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:
- Reset the master by executing RESET MASTER;.
- Lock the tables for read operations using FLUSH TABLES WITH READ LOCK;.
- Obtain the master's current replication status using SHOW MASTER STATUS;. Copy the values returned for Master_Log_File and Master_Log_Pos.
- 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:
- Stop the slave's replication process using STOP SLAVE;.
- Import the master's database dump into the slave using mysql -uroot -p < mysqldump.sql.
- Reset the slave's replication configuration by executing RESET SLAVE;.
- 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.
- 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!
Statement:The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn