1

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 127.0.0.1
# Syncing h=127.0.0.1
# DELETE REPLACE INSERT UPDATE ALGORITHM EXIT DATABASE.TABLE
#      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:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.0.40.12
                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
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1452

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

5 Answers 5

2

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 127.0.0.1

3) On the slave run the following command:

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

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.

1

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

mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
3
  • Thank you. No idea how to use maatkit but my slave is busy catching
    – johnwards
    Nov 13, 2009 at 16:28
  • This usually fixes most MySQL replication problems when you have a key collision or something. Nov 17, 2009 at 12:51
  • It doesn't usually fix them, it usually skips and leaves the slave's data still inconsistent with the master.
    – user48802
    Sep 1, 2011 at 1:25
1

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.

1
  • 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, 2009 at 19:30
1

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!

1

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);

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .