How to Resync Mismatched Databases in MySQL Replication?

Resolving Mismatched Databases in MySQL Replication

In the realm of MySQL replication, maintaining data integrity between the master and slave servers is paramount. However, circumstances like network outages can disrupt this delicate balance, leading to database discrepancies. This guide provides a detailed procedure to resync your master and slave databases from scratch, ensuring seamless replication once again.

Procedure:

At the Master Server:

• Reset master status: Use RESET MASTER; to clear the master's replication information.
• Lock read operation: Execute FLUSH TABLES WITH READ LOCK; to prevent data changes while dumping.
• Display master status: Run SHOW MASTER STATUS; and record the values of the result.

Generate a Master Dump:

• Create a full database dump using mysqldump -u root -p --all-databases > /a/path/mysqldump.sql.
  Unlock Read Operation:
• Execute UNLOCK TABLES; to release the read lock.

Copy Dump File to Slave:

• Transfer the dumped file to the slave server using scp or any suitable file transfer method.

At the Slave Server:

• Stop slave replication: Run STOP SLAVE; to halt the replication process.
• Load master data: Use mysql -uroot -p < mysqldump.sql to populate the slave with the master's data.

• Reset and Sync Logs:

  • Execute RESET SLAVE; to clear the slave's replication settings.
  • Replace MASTER_LOG_FILE and MASTER_LOG_POS with the values you recorded from the master's SHOW MASTER STATUS; output. Use CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;.
• Start slave replication: Run START SLAVE; to resume data flow from the master.

Verification:

• Check replication status with SHOW SLAVE STATUS;.
• Expected output: Slave_IO_Running: Yes and Slave_SQL_Running: Yes, indicating successful synchronization.

The above is the detailed content of How to Resync Mismatched Databases in MySQL Replication?. For more information, please follow other related articles on the PHP Chinese website!