Building or Rebuilding a MySQL Slave Instance

MySQL master-slave setups using log positioning are fairly common practice. Occasionally, the slave instance becomes corrupt or out-of-sync with the master. These steps will help reset the master instance and establish a new starting point for bin logging.

On the master, reset the master and lock the tables.

RESET MASTER;
FLUSH TABLES WITH READ LOCK;

Next, show the master status to obtain bin log position information.

SHOW MASTER STATUS;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000001 |    19113 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Note the file name and the position. In this example, the file name is bin.000001 and the position is 19113. This information will be needed after the slave instance is reset.

The master’s databases can now be dumped. The dump file will then be transferred to the inoperative slave server and then imported.

mysqldump -u root -p --all-databases > /tmp/master_dump.sql

Once the dump begins, the table lock can be released.

UNLOCK TABLES;

Transfer the master_dump.sql file to the inoperative slave server and import it.

# Slave server
STOP SLAVE;

Import the dump using the mysql client.

mysql -u root -p < /tmp/master_dump.sql

After the dump is complete, the final step is to sync the slave with the master’s logs. The values previously taken from the master status are used.

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='bin.000001', MASTER_LOG_POS=19113;
START SLAVE;

To verify the operation of the slave, the SHOW SLAVE STATUS; command can be used. The Slave_IO_Running and Slave_SQL_Running values should be yes.

SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-master.local
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000001
          Read_Master_Log_Pos: 3302820
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 3284021
        Relay_Master_Log_File: bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3302820
              Relay_Log_Space: 3284222
              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: 0
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: 1500759608
                  Master_UUID: c85acd75-5441-11fa-9d06-005156a1fa2d
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

References