How to Resynchronize a Mismatched MySQL Replication Setup: Master-Slave?

Master-Slave Database Resynchronization for Mismatched MySQL Replication

In a master-slave MySQL replication setup, if the slave database becomes out of sync with the master, steps must be taken to re-establish the synchronization. This procedure is essential to ensure the availability and consistency of data across the slave database.

To re-sync the database:

On the Master:

1. Reset the master and flush tables:

   • RESET MASTER;
   • FLUSH TABLES WITH READ LOCK;

2. Retrieve master status:

   • SHOW MASTER STATUS;

On the Slave:

1. Stop slave replication:

   • STOP SLAVE;

2. Load master's data dump:

   • mysql -uroot -p < mysqldump.sql

3. Reset slave and sync logs:

   • RESET SLAVE;
   • CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

On the Master:

1. Unlock tables:

   • UNLOCK TABLES;

On the Slave:

1. Start slave replication:

   • START SLAVE;

Verifying Synchronization:

After completing these steps, verify that slave replication is running properly by executing SHOW SLAVE STATUS;. A successful output will display:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes

The above is the detailed content of How to Resynchronize a Mismatched MySQL Replication Setup: Master-Slave?. For more information, please follow other related articles on the PHP Chinese website!