Some times we face issue in MySQL replication and slave could not sync. The cause of this issue may have lots of reason. Following Steps help you to start Re-Sync Mysql.
Note:-When you Re-Sync MySQL Replication 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.
Step 1: Slave Server
First we need to stop slave from slave server using following command.
# mysql> STOP SLAVE;
Step 2: Master Server
After stopping slave go to master server and reset the master using following command.
# mysql> RESET MASTER;
# mysql> FLUSH TABLES WITH READ LOCK;
Take dump of Master Server Database.
# mysqldump -u username –p techoism > techoism.sql
After taking backup unlock the tables.
# mysql> UNLOCK TABLES;
Step 3: Slave Server
Restore database from taken backup.
# mysql -u root –p techoism < techoism.sql
Login mysql and Reset Slave using following command.
# mysql> RESET SLAVE;
# mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1;
After resetting slave start slave replication.
# mysql> START SLAVE;
Now your replication has been re sync and check the slave status.
# mysql> show slave status\G