Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

5 Answers 5

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
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 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.

share|improve this answer

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

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.