MySQL slave out of sync with master

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

Solution 1:

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.

Solution 2:

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;

Solution 3:

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.