Dual Master and Dual Slaves

[alvinswim]

We are in the process of setting up a set of high volume MySQL servers. and the configuration that we have chosen is a Dual master replication model, each with its own slave. ie. a total of 4 computers with this configuration

C<-----A<----->B----->D

We have chosen this so that if one master fails we can easily bring the slave forward to take its place.

However, we do have a question with bringing a Slave up to replace a dead master (ie. if A dies we bring C up to the dual master configuration with B)

Is there any way to bring C up as a replacement master WITHOUT stopping mysql or locking the tables?

Let me know.. Thanks for your help upfront.

 

[ericbrunson]

I've been doing a lot of reading on replication in the last couple of months and I've recently handled moving my DB master to a promoted slave.

Generally speaking, this is not a very good design you've laid out.

In order to promote C to a master you would have to know the position that A stopped reading B's logs, then CHANGE MASTER on C so C reads B at the log position A stopped. Since the master doesn't keep track of the last log position of its slaves, that may be hard to do.

Also, the only way you really want to be doing dual master is if the masters are not updating the same tables locally. I.E. B is the master for one set of applications and mirroring another set from A, while a A is complementary. In this design it would make more sense to promote B to be the single master for all applications, then synchronize C with B.

If you're looking for a more HA architecture, you should look at clustering.