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
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SHOW SLAVE STATUS \G;
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]
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]