How to reset (resynchronize) MySQL master-slave replication

Sometimes MySQL replication will cause the slave system to be unable to synchronize correctly with the master system. This may have many reasons. But what we need to consider is how to fix it? This article will introduce how to reset MySQL replication.

Note: After using the method in this article, all bin-log files will be deleted, so if necessary, you can back up the bin-log file first, and then follow the instructions.

(Related recommendations: MySQL Tutorial)

On the slave server:

First, we need to stop the slave server. Log in to the mysql server and execute the following commands.

mysql> STOP SLAVE;

On the master server:

After stopping the slave server, go to the master server and reset the master server status using the following command.

mysql> RESET MASTER;
mysql> FLUSH TABLES WITH READ LOCK;

Use the following command to copy the database dump.

# mysqldump -u root -p mydb > mydb-dump.sql

Unlock the table on the master server after backup.

mysql> UNLOCK TABLES;

On the slave server:

Use the following command to restore the database backup on the slave server.

# mysql -u root -p mydb < mydb-dump.sql

Log in to mysql and execute the following command to reset the slave server status.

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

Start slave replication after resetting the slave

mysql> START SLAVE;

Replication has been resynchronized with the new configuration, which can be verified using the following command.

mysql> show slave status G

The above is the detailed content of How to reset (resynchronize) MySQL master-slave replication. For more information, please follow other related articles on the PHP Chinese website!