Mysql Replication Resync

There are many articles out there that explain how to do mysql replication but not too many that explain how to fix it when things go south. When replication breaks or fails over and you need to recover, there are only a few simple steps that you need to do. Here we go..

1 The dump...

Do a mysql dump of the master database you wish to replicate to the slave (the one with the most updated information).

This can be done with this command.

mysqldump -u root -p --master-data=1 -a -c -Q databasename > somefile.sql

where databasename is the database in question and somefile.sql is the output file you will use on the slave. Just a word of caution here, mysqldump will lock the tables while it is doing this so if you have a big database or a slow server, you could take down your site for a bit.

2 Get it on to the slave..

Transfer the dump file to the slave server using scp, ftp, sftp, or floppy disks (have fun with that last one if you databases are anything like the ones I deal with :) ). You should know how to do this but if you don’t there are many howtos out there to help.

3 Stop the slave server...

Go into mysql and turn off the slave replication process by using the following command.

mysql -u root -p
mysql> slave stop;

Then exit mysql.

4 The import

Import the dump file with the following command line mysql import (just standard stuff really).

mysql -u root -p databasename < somefile.sql

This might take some time to complete. Don’t worry though the master mysql server is queuing up the changes while you get the dump imported.

5 Start the slave server...

Start the slave back up with the following command.

mysql -u root -p
mysql> slave start;

6 Check It!!

Check the status of the slave server with the

show slave status;

command.

QUELLE: http://www.cornerview.net/2007/12/12/mysql-replication-resync/