MySQL has a nice feature called replication that usually keeps a slave database in sync with a master. Except when it doesn't.

Usually, you can use the following on the slave to bring them back into sync: SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N (Where N is some number.)

However, sometimes this won't work (no matter how much you skip) and you need to resync the entire database. I've found the following is the easiest way to bring them back into sync. Keep in mind, though, that it does require a small amount of downtime on the master.

  1. Shut down the MySQL server on the slave.
  2. rsync the master to the slave while the master is still live: $ rsync -Sa --progress --exclude=mastername* /db/* rootsync:/db

    Of course, for this to work, you have to have be able to use ssh as root between the two hosts. I have a key in root's .ssh directory to allow this and run an ssh server on a high port that only allows connections from a single IP. rootsync is defined in my .ssh/config as Host rootsync HostName slaveserver User root Port 2222

    If you want more information on setting up ssh like this, see my ssh howto.

  3. Stop MySQL on the master briefly and and run rsync again. I rsync twice so that it, hopefully, takes less time the second time when MySQL is down.
  4. Start MySQL on the master and immediatly make a note of the location of the database log position. mysql> show master status; +--------------------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------------------+----------+--------------+------------------+ | mastername-bin.000804 | 98 | | | +--------------------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
  5. Make sure that the files and do not exist on the slave.
  6. Start MySQL on the slave.
  7. Set the slave log position to the information you got from the master above: mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> change master to MASTER_LOG_FILE='mastername-bin.000804',MASTER_LOG_POS=98; Query OK, 0 rows affected (0.05 sec) mysql> start slave; Query OK, 0 rows affected (0.05 sec)