Take the 2-minute tour ×

Our slave, which is just used for backups, is out of sync. It has crashed out on a key restraint.

I would like to find a way to resync up the slave without taking the master offline, I know I can do it that way but I believe it is possible not to.

I have "High Performance MySQL" infront of me and it points me in the direction of maatkit, specifically mk-table-sync.

For the life of me I can't get mk-table-sync to work.

I run it like so on the slave:

root@machine:~# mk-table-sync --sync-to-master --dry-run
# Syncing h=
#      0       0      0      0 Chunk     0    database.case_study_product
#      0       0      0      0 Chunk     0    database.case_study_region
#      0       0      0      0 Chunk     0    database.case_study_sector
#      0       0      0      0 Chunk     0    database.contact
#      0       0      0      0 Chunk     0    database.contact_issue
#      0       0      0      0 Chunk     0    database.feedback
#      0       0      0      0 Chunk     0    database.feedback_rating
#      0       0      0      0 Chunk     0    database.feedback_usefulness
#      0       0      0      0 Chunk     0    database.help
#      0       0      0      0 Chunk     0    database.help_issue
#      0       0      0      0 Chunk     0    database.search_weight
#      0       0      0      0 Chunk     0    database.contented_content
#      0       0      0      0 Nibble    0    database.contented_content_index
#      0       0      0      0 Chunk     0    database.contented_content_log

I know for a fact that contented_content and contented_content_index are out of sync. But from what I can tell form that output maatkit thinks everything is fine.

Here is the output of slave status:

             Slave_IO_State: Waiting for master to send event
                Master_User: rep1
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000023
        Read_Master_Log_Pos: 25832973
             Relay_Log_File: mysql-relay-bin.000002
              Relay_Log_Pos: 19098703
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
                 Last_Errno: 1452

Any help, pointers...ask for more info..etc

share|improve this question

5 Answers 5

up vote 2 down vote accepted

Right after a lot of head scratching and playing around on my virtual environment this is how I managed to sync my slave up to the master.

1) In the database (mydb) on the master I want to create the following table:

CREATE TABLE checksum (
     db         char(64)     NOT NULL,
     tbl        char(64)     NOT NULL,
     chunk      int          NOT NULL,
     boundaries char(100)    NOT NULL,
     this_crc   char(40)     NOT NULL,
     this_cnt   int          NOT NULL,
     master_crc char(40)         NULL,
     master_cnt int              NULL,
     ts         timestamp    NOT NULL,
     PRIMARY KEY (db, tbl, chunk)

2) On the master run the following command:

mk-table-checksum -d mydb --replicate mydb.checksum

3) On the slave run the following command:

mk-table-sync -d mydb --replicate mydb.checksum  --sync-to-master --no-foreign-key-checks --execute

When I tried running the checksum replicate command on the slave before running the sync command that didn't do anything at all.

The slave connected and working in my example, also I disabled foreign keys checks because we are using INNODB and was getting foreign key constraint issues when running the sync.

share|improve this answer

Maybe it will help if you put on the slave server :

mysql> STOP SLAVE;
share|improve this answer
Thank you. No idea how to use maatkit but my slave is busy catching –  johnwards Nov 13 '09 at 16:28
This usually fixes most MySQL replication problems when you have a key collision or something. –  Tom O'Connor Nov 17 '09 at 12:51
It doesn't usually fix them, it usually skips and leaves the slave's data still inconsistent with the master. –  Baron Schwartz Sep 1 '11 at 1:25

The slave's data will be different after it finishes catching up. Run mk-table-checksum --replicate on the master first, then on the slave, run mk-table-sync --replicate (there is an example in the docs).

I'm not clear what you understood about the state of your slave from the output of --dry-run, but --dry-run DOES NOT compare any data. It merely tells you what tables it would examine and with what sync algorithm.

share|improve this answer
Oh I presumed that a dry run would be like rsync. So I could see what it was going to do, just like rsync. I would be quite scared about running things that make data changes without knowing this. I'll have a look at the docs about mk-table-checksum on Monday, cheers. –  johnwards Nov 13 '09 at 19:30

I think there is a need for clearer information on what --dry-run does, so I created a feature request: http://code.google.com/p/maatkit/issues/detail?id=691

I welcome your comments there and on the Maatkit mailing list!

share|improve this answer

I have a similar situation where I need to regular verify data consistency between my master and slave.

I wrote a script to handle this which I threw into crontab and have it run every Sunday, during a time when I know there won't be much data being written to the replicated dbs.

I should note this is written in PHP, and the slave/master are on the same network with a NFS hosted on the master's drive @ /home/sharefiles/

I'm sure some people might grumble about how this is done, but it serves my needs quite well, and takes only a couple seconds to run.

/* This script runs weekly to stop replication, drop the databases, copy them from master to slave */

/* And begins the replication again. Do not touch this script!! */

//Stop slave on slave

$slave = mysql_connect("slave", "user", "pw");

mysql_query("STOP SLAVE", $slave);

//Reset position

mysql_query("RESET SLAVE", $slave);

//Get master info, position etc

$master = mysql_connect("localhost", "user", "pw");

$masterinfo = mysql_fetch_assoc(mysql_query("SHOW MASTER STATUS", $master)); // $masterinfo[File], $masterinfo[Position]

//Dbs to be replicated

$dbArray = array("db1", "db2", "db3", "db4");

//Dump each db and copy to slave

foreach($dbArray as $db) {

    system("mysqldump $db > /home/sharefiles/$db.sql");

    system("mysql -h slaveaddress -u root --password=pw --database=$db < /home/sharefiles/$db.sql");


mysql_query("CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='replication', MASTER_PASSWORD='replicationuserpassword', MASTER_LOG_FILE='$masterinfo[File]', MASTER_LOG_POS=$masterinfo[Position]", $slave);

mysql_query("START SLAVE", $slave);

share|improve this answer

Your Answer


