I have setup a Master-Master configuration in MySQL with one additional slave, like this:

Master1 <-> Master2 <- Slave

Master1 is the write master used by my application, Master2 is used for reads and backup, and the slave is only used for reads. I have setup log-slave-updates on both masters, and everything seems to work correctly.

However I'm having trouble wrapping my head around what happens if Master2 fails and I have to switch the slave to follow Master1. Basically what I understand is you need to do a CHANGE MASTER TO MASTER_HOST = 'Master1' on the slave, but what I don't get is how you determine the parameters for MASTER_LOG_POS and MASTER_LOG_FILE. How do you make sure the point at which you start replication from Master1 does not make you lose data, or tries to apply events twice?

  • I wonder if this can be resolved using GTID replication? Otherwise I don't think the slave has any info on master1's log position. – Manny Calavera Nov 15 '14 at 2:00
  • Come to think of it, if you can make sure master2 and slave is in sync. You can issue show slave master on master2 to retrieve current execution pos. – Manny Calavera Nov 15 '14 at 2:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.