Checking the replication for errors

Calculating checksums is the best way to be sure that a server and a slave contain exactly the same data as the server. This check can be used in two situations:

• After loading data into a slave, to be sure that everything worked properly

• On running servers, on a regular basis, or when we suspect that a replication error happened

The second case is more complicated, because during the normal execution slaves can lag behind their masters. However, a tool explained next is able to perform this check automatically, by waiting until slaves reach a certain binary log event.

There are at least three methods to do this:

• Using the CHECKSUM TABLE statement

• Using the Percona pt-table-checksum tool

• Calculating a checksum of the physical files (only for physical backups)

Sometimes we only want to check a relatively small subset of data. If so, instead of checking the whole tables, we can write a query that returns that data and calculate the MD5 checksum of the result set...