How to ensure MySQL replication SLAVE is fully synchronized with the replication MASTER?

Question

Using simple replication settings with one MASTER and one SLAVE, how can one ensure that the SLAVE and MASTER are fully synchronized?

Now yes, they both started from the exact same image and replication is working and reporting that everything is okay BUT: * It has happened that there were errors stopping the replication and then the replication had to be stopped and later resumed. * Perhaps a change accidentally occurred on the SLAVE and then it's not the same as the MASTER anymore. * Other whichever scenarios that might break sync.

While it's possible to do a big mysqldump of both database and compare the files I would be interested in a method that can be implemented more easily and also can be checked automatically to ensure all is in sync.

Thanks

Answer 1

Have you tried Percona Toolkit (formerly known as Maatkit)? You can use one of their tools which is pt-table-checksum for your case. You can check other tools too at their website.

pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master. The optional DSN specifies the master host. The tool’s exit status is nonzero if any differences are found, or if any warnings or errors occur.

The following command will connect to the replication master on localhost, checksum every table, and report the results on every detected replica:

Answer 2

If you have MySQL Server versions 5.6.14 or higher, you can use the MySQL Replication Synchronization Checker. It's included in the MySql server package. Is designed to work exclusively for servers that support global transaction identifiers (GTIDs) and have gtid_mode=ON.

This utility permits you to check replication servers for synchronization. It checks data consistency between a master and slaves or between two slaves. The utility reports missing objects as well as missing data. The utility can operate on an active replication topology, applying a synchronization process to check the data. Those servers where replication is not active can still be checked but the synchronization process will be skipped. In that case, it is up to the user to manually synchronize the servers.

See MySQL Documentation for more information