Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers 2

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.

share|improve this answer
    
Thanks, I would never have clients writing to two different masters. The second "master" is really a slave and would not have any direct client access - unless I need to take the primary master down for maintenance. I think actually, I would set the the secondary master up with log-slave-updates and make the slave slave from that, as then I would not need to wait for the slave to catch up again when I bring the primary master back on line - if you see what I mean? Thanks for the tips! –  Mayb2Moro Oct 29 '12 at 20:37
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.