zomo technology

MySQL replication health

Tuesday, 09. 30. 2008  –  Category: sw

It’s possible for MySQL’s statement-based replication to get out of sync. I’ve yet to see a real example myself, perhaps because the DB abstraction layers I encounter not creating the “dangerous” queries that can make this happen. Still, other than monitoring replication health with SHOW SLAVE STATUS and friends I’ve sometimes been concerned about whether my MySQL slaves are faithful copies of the master. Finding that out during a failover moment is the wrong time.

The author of the piece linked above has scratched this itch with Maatkit, a suite of replication-focused MySQL tools which include some for checksumming tables.

This can run with standalone instances but most appealing is the way it runs across a replication chain: the statements to perform the table checksums are themselves introduced into the replication and store their results in a table alongside the master’s checksums. You can then compare and contrast on each slave: neat. All straightforward to roll into Nagios or other monitoring.