Reset (Re-Sync) Mysql Master-Slave Replication

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.

download_opt

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
    

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

    The reCAPTCHA verification period has expired. Please reload the page.