Re-parenting a MySQL slave¶

Archived (pre-2022)

Preserved for reference only -- likely outdated. View original | Last updated: September 2019

Use case¶

In a replication topology with multiple slaves it's sometimes necessary to change order of data flow through the various servers.

For example, when you clone the primary mysql in AWS (mysql1) as outlined in Copying database to a fresh MySQL slave you end up with this setup:

while the desired setup looks like this:

In order to know the correct parameters for the necessary CHANGE MASTER statement we need to find a corresponding pair of binlog coordinates in both dbs002 and mysql1.

Step-by-Step Instructions¶

Stop the slave we want to reparent¶

First, stop the slave on mysql2 and wait a few seconds (to make sure that mysql2 is actually behind mysql1):

mysql> stop slave;               
Query OK, 0 rows affected (0.01 sec)

Gather the necessary information¶

Next, issue the following four commands on mysql1. Issuing them in one line makes sure that the commands are run quickly after each other and the slave stops for the shortest time possible:

mysql> stop slave; show slave status\G show master status\G start slave;
Query OK, 0 rows affected (0.03 sec)

*************************** 1. row ***************************                 
               Slave_IO_State:     
                  Master_Host: dbs002.prd.fyber.com
                  Master_User: replication                                     
                  Master_Port: 3306                                            
                Connect_Retry: 60                                              
              Master_Log_File: mysql-bin.091945              <-----------------
          Read_Master_Log_Pos: 199610594       
               Relay_Log_File: mysql-relay-bin.000318
                Relay_Log_Pos: 199610753
        Relay_Master_Log_File: mysql-bin.091945
             Slave_IO_Running: No  
            Slave_SQL_Running: No
              Replicate_Do_DB:   
          Replicate_Ignore_DB:     
           Replicate_Do_Table:                                                 
       Replicate_Ignore_Table:   
      Replicate_Wild_Do_Table:  
  Replicate_Wild_Ignore_Table: cms_db_production.advertisement_completion_rates
                   Last_Errno: 0        
                   Last_Error:          
                 Skip_Counter: 0        
          Exec_Master_Log_Pos: 199610594                     <-----------------
              Relay_Log_Space: 199610864                           
              Until_Condition: None                   
               Until_Log_File:          
                Until_Log_Pos: 0                                   
           Master_SSL_Allowed: No                                                                         
           Master_SSL_CA_File:                                     
           Master_SSL_CA_Path:                        
              Master_SSL_Cert:     
            Master_SSL_Cipher:      
               Master_SSL_Key:      
        Seconds_Behind_Master: NULL 
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:  
               Last_SQL_Errno: 0        
               Last_SQL_Error:                                     
  Replicate_Ignore_Server_Ids:                                
             Master_Server_Id: 174269471
                  Master_UUID: b8382237-6b22-11e7-92cd-a0369f696050
             Master_Info_File: mysql.slave_master_info        
                    SQL_Delay: 0                              
          SQL_Remaining_Delay: NULL                           
      Slave_SQL_Running_State:     
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:  
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0                              
1 row in set (0.00 sec)            

*************************** 1. row ***************************
             File: mysql-bin.062667                          <-----------------
         Position: 84767685                                  <-----------------
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

We need the four values donated by arrows in the above code:

Binlog coordinates for the current master (dbs002) from SHOW SLAVE STATUS:

Master_Log_File
Exec_Master_Log_File

Binlog coordinates for the mysql we want to connect to (mysql1) from SHOW MASTER STATUS:

File
Position

We now have a given point in time where we know the exact position in the master's and the slave's binary logs.

Let the new slave catch up to the same position¶

We now want to position mysql2 on the exact same point in time. For this command use the values from SHOW SLAVE STATUS above (Master_log_file, Exec_Master_Log_File):

mysql> start slave until master_log_file='mysql-bin.091945', master_log_pos=199610594;

Use SHOW SLAVE STATUS\G repeatedly to watch the progress. Once the slave has reached the desired position, it will stop.

Re-parent mysql2 to mysql1¶

Stop the slave completely, then change replication configuration to point at the new master. For this command use the values from SHOW MASTER STATUS above (File, Position):

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_host='mysql1.prd-aws.fyber.com', master_log_file='mysql-bin.062667', master_log_pos=84767685;
Query OK, 0 rows affected (0.04 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

Use SHOW SLAVE STATUS\G to verify that the slave is running correctly. A lag of a few minutes (depending on how long the above procedure took) is expected, but should catch up soon.