WO Blog

Slave out of sync with your master?

Hello, I am not growing a Mo. So I have to blog about something technical…

One of our major clients has a Master-Replication setup which we use for failover and backups. However it managed to get itself out of sync. A small example is that it the user table is missing a single user. The database is so large it is impossible to be sure that this is the only thing missing.

Thankfully there are tools to help in this situation provided by Maatkit, sadly the documentation is a bit vague and lacks direct examples.

Here are my simple steps to get a slave in sync with its master. I would make sure that try this is a demo environment first as during my experiments with these tools it managed to remove data from the master…which is bad!

Make sure that replication is working before you start this.

1) In the database (mydb) on the master I want to create the following table, this stores the checksum data:

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, which stores the checksum data in the table we just created:

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

3) On the slave run the following command, what this does…I am a touch vague about but it works…read the docs see if you can explain it:

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

I hope that this is of some use to someone!

Post a Comment

Your email address is never published nor shared. Required fields are marked *

*
*