Re: Replication with two servers

[Jeremy Smyth]

What you describe is known as "circular replication", and while it is possible, it requires a lot of care; MySQL replication is asynchronous, and therefore is subject to conflicts. It does not perform conflict resolution, so you have to make sure your application caters for that possibility.

The simplest way to succeed with circular replication is to avoid updating the same data (rows and related rows) on different servers and therefore avoid conflicts.

If you do not take care to avoid conflicts, you will eventually encounter a situation where two servers accept conflicting changes at nearly the same time, and replicate them to the other side. This results in the worst-case scenario of two servers containing different data, and no warning of that situation and no idea which version is the true one. I cannot stress too much how bad this situation is, and how important it is to avoid it.

So, in short, what you want to do is possible, but it is not easy to do in the way that you want to while making sure your data is protected.