I have 1 master and 2 slaves. I am using MySQL 5.1.42 on all servers. I am attempting to use mk-table-checksum to verify that their data is in sync, but I am getting unexpected results on one of the slaves.
First, I generate the checksums on the master like this:
mk-table-checksum h=localhost --databases MYDB --tables {$table_list} --replicate=MYDB.mk_checksum --chunk-size=10M
My understanding is that this runs the checksum queries on the master which then propagate via normal replication to the slaves. So, no locking is needed because the slaves will be at the same logical point in time when they run the checksum queries on themselves. Is this correct?
Next, to verify that the checksums match, I run this on the master:
mk-table-checksum --databases MYDB --replicate=IRC.mk_checksum --replicate-check 1 h=localhost,u=maatkit,p=xxxx
If there are any differences, I repair the slaves like this:
mk-table-sync --execute --verbose --replicate IRC.mk_checksum h=localhost,u=maatkit,p=xxxx
After doing all of this, I repaired both slaves with mk-table-sync. However, everytime I run this sequence (after everything has already been repaired), one slave is perfectly in sync but one slave always has a few tables out of sync. I am 99.999% sure that the data on the slaves matches, since I repaired everything and the tables were not even updated on the master between runs of the checksum script. What would cause a few tables to always show out of sync on only one of the slaves? I am stuck. Here is the output:
Differences on h=x.x.x.x,p=...,u=maatkit
DB TBL CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
IRC product 10 0 1product_id
>= 147377 ANDproduct_id
< 162085
IRC post_order_survey 0 0 1 1=1
IRC mk_heartbeat 0 0 1 1=1
IRC mailing_list 0 0 1 1=1
IRC honey_pot_log 0 0 1 1=1
IRC product 12 0 1product_id
>= 176793 ANDproduct_id
< 191501
IRC product 18 0 1product_id
>= 265041
IRC orders 26 0 1order_id
>= 694472
IRC orders_product 6 0 1op_id
>= 935375
--replicate=MYDB.mk_checksum
--replicate=IRC.mk_checksum
Why the difference?
Did you, perchance, post this question to the Maatkit mailing list? It sounds like a recent thread there. If it wasn't you, maybe you can hijack that thread. (I prefer to answer there, I don't track/follow/monitor here).