How to Resynchronize MySQL Databases After a Master-Slave Mismatch?

Database Resync in MySQL Replication with Master-Slave Database Mismatch

In scenarios where data inconsistency arises between the master and slave databases due to network interruptions or system outages, it becomes necessary to re-synchronize the databases to restore data integrity.

Resynchronization Procedure

To re-sync the MySQL databases, follow these steps:

Master Server:

1. Reset the master's position: RESET MASTER;
2. Lock the tables for reading: FLUSH TABLES WITH READ LOCK;
3. Note the master status: SHOW MASTER STATUS;

Slave Server:

1. Stop slave replication: STOP SLAVE;
2. Import the master's dump: mysql -uroot -p < mysqldump.sql
3. Reset the slave: RESET SLAVE;
4. Synchronize logs: CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
5. Start replication: START SLAVE;

Verification:

After completing these steps, verify the synchronization status:

1. On the master: SHOW MASTER STATUS;
2. On the slave: SHOW SLAVE STATUS;

The output should indicate that both Slave_IO_Running and Slave_SQL_Running are set to Yes, confirming the successful resynchronization of the databases.

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