Building or Rebuilding a MySQL Replica Instance

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

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

RESET MASTER;
FLUSH TABLES WITH READ LOCK;

Next, show the 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 replica instance is reset.

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

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

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

UNLOCK TABLES;

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

# Replica server
STOP SLAVE;

Import the dump using the mysql client.

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

After the dump is complete, the final step is to sync the replica with the primary’s logs. The values previously taken from the primary 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 replica, 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

16.1.7.1 Checking Replication Status – MySQL Documentation
16.1.2.5 Setting Up Replication Slaves – MySQL Documentation
Re-Sync MySQL DB if Master and Slave have different databases – Stack Overflow

References

Jimmy McNatt