How to Reset ( Re-Sync ) MySQL Master-Slave Replication

Some times MySQL replication creates problems and slave could not sync properly from the master database server. It may cause due to lots of reason’s. Only the question is how to fix it?

This article will guide you to how to reset MySQL replication and it will start again from scratch.

Warning: After using this tutorial, All of your bin-log files will be deleted, So if you want, you may take a backup of bin-log files first and then follow the instructions.

At Slave Server:

At first we need to stop slave on slave server. Login to the MySQL server and execute the following command.

mysql> STOP SLAVE;

At Master Server:

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

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

[ Note: Adding a read lock with production sites. Read more about table locking ]

Take a dump of database is being replicated using following command.

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

root@mysql-0:/# mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > emas-all-databases.sql

　　

[root@test2-master ~]# kubectl cp -n test2 mysql-0:emas-all-databases.sql ./emas-all-databases.sql

　　

After taking backup unlock the tables at master server.

mysql> UNLOCK TABLES;

At Slave Server:

Restore database backup taken on slave server using following command.

[root@test2-master ~]# kubectl cp emas-all-databases.sql  test2/mysql-1:/

　　

[root@test2-master ~]# kubectl exec -it mysql-1 bash -n test2

　　

 

　　

mysql> select @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL read_only = OFF;
Query OK, 0 rows affected (0.00 sec)

　　

root@mysql-1:/# mysql -uroot -p < emas-all-databases.sql

　　

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

Login to mysql and execute following commands to reset slave state also.

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

CHANGE MASTER TO MASTER_LOG_FILE='mysql-0-bin.000001', MASTER_LOG_POS=1;

　　

After resetting slave start slave replication

mysql> START SLAVE;

Now your replication has been re sync same as newly configured. you can verify it using the following commands.

mysql> show slave status \G