Exploring mk-table-checksum

Submitted by jay on September 9, 2010 - 9:02am

I recently started exploring the wonders of mk-table-checksum after spending too long dismissing the magic-like maatkit toolkit. What follows is not an exhaustive treatment of mk-table-checksum, but just an overview and some things I had to learn by trying the tool out.


The basic principle is based on the CHECKSUM TABLE table maintenance statement supported in the MySQL Server. The mk-table-checksum runs a CHECKSUM TABLE command on all (or some) tables. The results of that are stored in another table on the server.


This, by itself, isn't that interesting. What is interesting is the --replicate option, which replicates the statements that generate the checksums and inserts the data into a checksum table to all the slaves as well. The trick of this is that this is statement based replication; the statement replication tells each slave to calculate the CHECKSUM, independently. When the statements are fully replicated, each mysql server (master or slave) will have it's own version of the checksum table, based off the contents of the table on that mysql instance.


Hence, simply comparing that checksum table on all of the servers will quickly show you if there are differences in table contents. Here are some command line examples that I assembled.

mk-table-checksum -u failover -ppass --create-replicate --replicate mysql_health.checksums --ignore-databases mysql,mysql_health --empty-replicate-table --chunk-size=10M

This command creates the checksum table in 'mysql_health.checksums'. The chunk-size option breaks the tables down into roughly 10M chunks and checksums those chunks. When this command finishes, the checksums are generated on the master. You'll need to wait around time_to_run_on_master * (hops_to_furthest_slave + 1) for it to be fully propagated. Then you can run this:

mk-table-checksum -u failover -ppass --create-replicate --replicate mysql_health.checksums --ignore-databases mysql,mysql_health --empty-replicate-table --chunk-size=10M --replicate-check 2

This command (with probably too many options) checks up to 2 levels deep on the replication tree; that is that all slaves up to 2 levels away from the given master are checked for their checksum table and compared with the master. Only differences are reported.


Now if this were a live master actively taking writes, I may be concerned about other writes being interspersed in between the checksums, potentially affecting the checksum results differently on the slave compared with the master. There are ways to deal with this, but the --lock option is at least one option, which locks the tables on the master as they are checksummed. The obvious downfall of this is that your master can't take writes on a locked table. The perldoc for mk-table-checksum has some good discussion on various ways to handle this.


Once you have some idea of what is wrong, you can fix it with some more maatkit magic:

# Print output only
mk-table-sync -u failover -ppass --print --sync-to-master --replicate mysql_health.checksums
# execute fixes
mk-table-sync -u failover -ppass --execute --sync-to-master --replicate mysql_health.checksums

Will this work perfectly in all circumstances? I have no idea. I do know that I did a random delete of about 100 rows on a test table of mine on a slave, and this seemed to fix it. It's magic!




Trackback URL for this post:

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.

More information about formatting options

Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.


Comment abuse is not tolerated on this site, besides all the comments are moderated, so don't bother posting comments that are not on topic, only for increasing the SEO of your site, or are outright spam.  If you've got something intelligent to contribute, by all means, post a link to your blog.  

About Me

Jay Janssen
Yahoo!, Inc.
jayj at yahoo dash inc dot com
High Availability
Global Load Balancing
View Jay Janssen on Twitter  View Jay Janssen's LinkedIn profile View Jay Janssen's Facebook profile