How to Resynchronize MySQL Replication After a Slave Disconnection?

Resolving Database Mismatches in MySQL Replication

In a MySQL replication setup, a mismatch between the database content of the master and slave servers can occur if the slave is disconnected for a prolonged period and then reconnected. To re-synchronize the databases in such situations, the following steps can be taken:

At the Master Server:

1. Reset the master by executing RESET MASTER;.
2. Lock the tables for read operations using FLUSH TABLES WITH READ LOCK;.
3. Obtain the master's current replication status using SHOW MASTER STATUS;. Copy the values returned for Master_Log_File and Master_Log_Pos.
4. Export the entire database from the master to a dump file using mysqldump -u root -p --all-databases > /path/mysqldump.sql.

At the Slave Server:

1. Stop the slave's replication process using STOP SLAVE;.
2. Import the master's database dump into the slave using mysql -uroot -p
3. Reset the slave's replication configuration by executing RESET SLAVE;.
4. Update the slave's replication configuration to match the master's current position using CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;. Replace 'mysql-bin.000001' and '98' with the values obtained from the SHOW MASTER STATUS command executed on the master server.
5. Start the slave's replication process again using START SLAVE;.

Verification:

To confirm successful synchronization, execute SHOW SLAVE STATUS; on the slave server. The output should display both Slave_IO_Running and Slave_SQL_Running as Yes.

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