MySql Two Way Replication / Circular Replication

[inya]

Hi,

how y' all doing?

I need some help. I currently have a server running MySql, but i want to put up another server in a different town. I want both servers to replicate each others (they are both Radius servers) databases on MySql.

I'd be really grateful if someone can guide me through the steps of MySql two way replication.

Thanks in advance.

Inya.

[Anigel]

Taken from http://dev.mysql.com/doc/mysql/en/Replication_FAQ.html

Q: What issues should I be aware of when setting up two-way replication?

A: MySQL replication currently does not support any locking protocol between master and slave to guarantee the atomicity of a distributed (cross-server) update. In other words, it is possible for client A to make an update to co-master 1, and in the meantime, before it propagates to co-master 2, client B could make an update to co-master 2 that will make the update of client A work differently than it did on co-master 1. Thus, when the update of client A makes it to co-master 2, it will produce tables that are different than what you have on co-master 1, even after all the updates from co-master 2 have also propagated. This means that you should not co-chain two servers in a two-way replication relationship unless you are sure that your updates can safely happen in any order, or unless you take care of mis-ordered updates somehow in the client code.

You must also realize that two-way replication actually does not improve performance very much (if at all), as far as updates are concerned. Both servers need to do the same number of updates each, as you would have one server do. The only difference is that there will be a little less lock contention, because the updates originating on another server will be serialized in one slave thread. Even this benefit might be offset by network delays.

You may also want to read http://lists.mysql.com/mysql/173264 which explains how to do this and also what complications can occur.

Please take great care in your database design to ensure these complications do not affect you if you do decide to use two-way replication

Normally you are much better off just using a master for updates and reading from either server. Then you use a program or script that stores updates in the case of the master being temporarily unavailable at the time the update is attempted.

[mosab]

you might find this link usefull
http://www.onlamp.com/pub/a/onlamp/2...plication.html