Reset ( Re-Sync ) MySQL Master-Slave Replication

Some times MySQL replication creates problems and slave could not sync properly from master. It may cause with lots of reason. Here is a way to fix it.

 

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

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.

mysql> show slave status \G

 

3 thoughts on “Reset ( Re-Sync ) MySQL Master-Slave Replication

  • August 13, 2014 at 4:51 am
    Permalink

    STOP SLAVE;
    SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
    START SLAVE;
    SHOW SLAVE STATUS \G;

  • September 12, 2014 at 6:48 am
    Permalink

    gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

  • September 12, 2014 at 6:50 am
    Permalink

    Restoring your MySQL Database

    Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

    Create an appropriately named database on the target machine
    Load the file using the mysql command:
    $ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql] Have a look how you can restore your tut_backup.sql file to the Tutorials database. $ mysql -u root -p Tutorials < tut_backup.sql To restore compressed backup files you can do the following: gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname] If you need to restore a database that already exists, you'll need to use mysqlimport command. The syntax for mysqlimport is as follows: mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

Leave a Reply