MySql replication re-sync for master-slave

If your master-slave DBs get out of sync, you can try to reset the slave with:

stop slave
reset slave
slave start

use show slave status to check for Slave_IO_Running and Slave_SQL_Running

If this doesn’t work, you may have to re-import the data from Master to Slave via:

Master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

And copy the values of the result of the last command somewhere.

Without closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

$ mysqldump -uroot -p --all-database > /a/path/mysqldump.sql

Now you can release the lock, even if the dump hasn’t end. To do it perform the following command in the mysql client:

UNLOCK TABLES;

Now copy the dump file to the slave using scp or your preferred tool.

Slave:

Open a connection to mysql and type:

STOP SLAVE;

Load master’s data dump with this console command:

$ mysql -uroot -p < mysqldump.sql

Sync slave and master logs:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

Where the values of the above fields are the ones you copied before.

Finally type

START SLAVE;

And to check that everything is working again, if you type

SHOW SLAVE STATUS;

you should see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

thanks to SO

Leave a Reply