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)
+<------------+ | ^ | | 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
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
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!