10

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

Collector
  • 2,034
  • 4
  • 22
  • 39

3 Answers3

7

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:

Amree
  • 2,890
  • 3
  • 30
  • 51
  • They also have a tool called `pt-table-sync` that allows you to synchronize the tables if you find something wrong. It uses `pt-table-checksum` to find potential issues and issues statements to the master to force the slave to replicate, so use at your own risks! – ESG Dec 28 '12 at 19:27
4

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

RonJohn
  • 349
  • 8
  • 20
Franco
  • 41
  • 4
  • Edited the answer to please cpbumz. This tool is really usefull, – Franco Jun 10 '15 at 19:46
  • This tool works like a charm. I have created a one liner batch file to run this tool every night and then using another service I get the result file in email. By the way, GTID replication is also a charm. – Allen King Apr 18 '17 at 23:49
3

You are right to be suspicious of a seemingly healthy master/slave replication setup! We were running fine when suddenly we got alerts from check_mk concerning a database that existed on our master that did not exist on our slave... but the master and slave status outputs were good! How unnerving is that? The way to prove integrity of the process is to use checksums to verify the data.

I have seen a lot of chatter on the Internet recommending pt-table-checksum . However, its limitations proved to be too onerous for us to be comfortable with. Most importantly, it requires and even sets statement-based replication (see the pt-table-checksum link). As it says in the mysql 5.6 online documentation, (for row-based replication...) "all changes can be replicated. This is the safest form of replication." There are other disadvantages to statement-based replication that make our developers nervous because some functions cannot be replicated properly; see the doc for a list.

We have already experienced issues with a master and slave using statement-based replication so we're specifically trying to avoid it.

We are going to try mysqlrplsync which specifically mentions that it "works independently of the binary log format (row, statement, or mixed)". It also mentions, however, that gtid-mode must be on and it requires MySQL 5.6.14 and higher... which means, I believe, that the MySQL delivered with RHEL7/CentOS 7 at least is out. You'll need to get the MySQL Community Edition, which is left as an exercise for the reader but you can go here for the packages or here for the repos, including RHEL derivatives and Debian.

Mike S
  • 1,235
  • 12
  • 19