Verifying replication slave data

I read about this wonderful toolkit called Maatkit in an equally wonderful book High Performance MySQL. I highly recommend both to anyone having to deal with MySQL. I’ll definitely do more posts about Maatkit and its tools as I use them.

I’m going to be up-front now, this is not the best method to run this. In the mk-table-checksum documentation it details the use of the faster/more accurate FNV1A_64 hash UDF; I would recommend using it over MD5 or SHA1. Also, if all your slaves are accessible from the machine running mk-table-checksum then I also recommend the –replicate-check flag instead of manually running the SQL.


From a machine that has at least access to your Master server:

mk-table-checksum --algorithm BIT_XOR --function MD5 --replicate utility.checksum --user maatkit_user --databases production --tables tableA,tableB

If you omit “–tables tableA,tableB” it will run against all tables. Also be sure “maatkit_user” has the select permission on the database, or use a user that does.

Give it some time to run and then replicate to the slaves and also run on them.
Then on the slave run this to see if there is any difference

select * from utility.checksum where this_crc != master_crc or this_cnt != master_cnt;

If there is any issues, there’s another Maatkit tool to remedy this issue: mk-table-sync