Switching MySQL slaves in master-master configuration

Overview

This document generally discusses how to switch mysql slaves between two master mysql servers which are in master-master configuration. In this scenario we have two master-master servers and two slaves named Slave1 and Slave2. Slave1 is pointed to Master1 and Slave2 is pointed to Master2.

Objective: Point Slave1 to Master2

Challenge: No data loss. Minimum downtime

Step-by-step guide

1. Closes all open tables and locks all tables for all databases with a global read lock in master1
   1. FLUSH TABLES WITH READ LOCK;

   Get master status and slave status from master1.

   

   
   In this case, master1 has already applied following changes, which were taken from master2.

   Binlog file from master2 : mysql-bin.000008

   Binlog position               : 1734

   Furthermore, following observations can be made.

   No changes can be performed on master1, however all pending changes will be applied on slave1 and finally both servers will be in sync.

   There will be no replication from master1 to master2; and vice versa.

2. Let’s insert some data in to master2 and see what happens
   
   This will be visible only on master2 and slave2. This change will not be propagated to master1 and slave1.
3. Let’s check the status of the master1
   
   
   In this case, master  read_master_log_pos has been changed, but relay_master_log_file and exec_master_log_pos have not been changed. Relay_master_log_file and exec_mastser_log_pos are the cordinate which we should use to point slave1 to master2.
4. Check if slave1 is in sync with master1
   
   Slave1 is in sync with master1
5. Stop the slave in slave1
6. Point slave1 to master1 with the values taken from step 3 ( relay_master_log_file and exec_master_log_pos)
   
7. start the slave on slave1
8. Check if the changes done in master2 were replicated to slave1
   
9. Unlock master1 and check the changes done in master2 were replicated to master1.