Mix of Cyclic and Chained MySQL replication - What do you do with "log-slave-updates"..?

Question

I have the following MySQL replication topology set up:

• Master1 (M1) replicates to Master2 (M2)
• Master2 (M2) replicates to Master1 (M1)
• Master1 (M1) replicates to Slave1 (S1)

Pictorial View

+<------------+
|             ^
|             |
V             |
M1----------> M2
|
+-----------> S1

All users connect to their masters for selects and updates.

The theory in the cyclic replication is that if I need to do any maintenance on the Master, I can point everyone temporarily at Slave 1 whilst I do the work, and then when Master is back online and the changes have been replicated from M2, I can seamlessly swap everyone back to working on the Master.

S1 is an offsite server in case of a local disaster, so does not need the cyclic replication set up.

The problem I currently have is that any changes made on M2 are not replicating to S1, so in the scenario above where I do need to take the master offline, any changes which get made on M2 in the meantime, will not make it to the offsite copy on S1.

I understand I can set the flag "log-slave-updates" on Master which would then cause changes made on M2 and pushed to Master as part of the replication, to be passed to S1 but I am nervous, that with the cyclic replication in place between M1 and M2, that this will also, in some way try and push the same changes back to M2 again and end up in an endless loop of replication.

I am guessing that MySQL is clever enough to handle this, but can someone help guide me towards anything I may need to consider in this situation before I end up in an infinite loop of replication!!

Many thanks in advance!

Answer 1

Well, you are absolutely right to be concerned about log-slave-updates causing an issue with your Master-Master setup, though it won't necessarily be an infinite loop changes. I suspect if you are writing to both masters, that you will constantly be having to set skip_slave_counter and restart the slave thread.... In a word, not ideal.

I would look over this post, specifically the short section on data integrity:

The safest solution is to simply never write data to both masters.

So, if both master's are up, but your clients only ever write to one master at a time (Master 1 by default), then you shouldn't have an issue.

---

*shouldn't doesn't mean you won't, though.

Answer 2

log_slave_updates = 1 -- all machines

sync_binlog = 1 -- if it is not too slow.

server_id -- different on every machine

Don't write to both masters, you are asking for trouble. Instead, leave one Master as a hot standby. (You get no performance boost anyway, since all writes have to be done on all machines.)

Simpler Ascii picture: S1 <- M1 <-> M2

Arrange for all clients to connect via hostname or some kind of "virtual" host name (eg, DNS). Don't use sockets.