10 essential MySQL tools for admins

The MySQL community is a hotbed of free, open source tools to enhance the performance and health of your MySQL systems

By Daniel Nichter, InfoWorld |  Software, MySQL

Essential MySQL tool No. 5: mk-table-checksum"Data drift" is a significant problem for dynamic MySQL environments. This problem, wherein slave data becomes out of sync with the master, is often caused by writing data to a slave or executing certain nondeterministic queries on the master. What's worse is that the data differences may go unnoticed until they become crippling. Enter mk-table-checksum, a tool that performs the complex, sensitive calculations necessary to verify the data in two or more tables is identical.

mk-table-checksum works with both stand-alone servers and servers in a replication hierarchy, where the tool's greatest value is easily seen. Verifying table data between a master and a slave must account for replication consistency. Because changes to the master are replicating to slaves with some amount of time delay ("lag"), simply reading data from the servers is an unreliable way to verify consistency, given that the data is constantly changing and incomplete until fully replicated. Locking tables and waiting for all data to replicate would allow consistent reads, but to do so would mean effectively halting the servers. mk-table-checksum allows you to perform nonblocking, consistent checksums of master and slave data. (For technical details on how this is accomplished, see the tool's documentation.)

Apart from replication consistency, there are other problems with verifying data. Table size is one of them. The MySQL command CHECKSUM TABLE is sufficient for small tables, but large tables require "chunking" to avoid long locks or overloading CPU or memory resources with checksum calculations.

Chunking solves a second problem: the need for regular data-consistency checks. While data drift can be a one-time occurrence, often it is recurring. mk-table-checksum is designed to continuously check tables, vetting certain chunks one run and other chunks the next run until eventually the whole table has been checked. The ongoing nature of this process helps ensure that recurring drift is corrected.

Download: http://maatkit.org/get/mk-table-checksumMaintainers: Daniel Nichter & Baron SchwartzMore info: http://maatkit.org/ | http://code.google.com/p/maatkit/


Originally published on InfoWorld |  Click here to read the original story.
Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Answers - Powered by ITworld

ITworld Answers helps you solve problems and share expertise. Ask a question or take a crack at answering the new questions below.

Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Ask a Question