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!