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

At Slave Server:

At first we need to stop slave on slave server. Login to mysql server and execute 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

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.

# 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;

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 following commands.