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 TABLEstatementUsing the Percona
pt-table-checksumtoolCalculating 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...