How to provide MySQL replication. Does SLAVE fully synchronize with MASTER replication?

Question

Using simple replication options with one MASTER and one SLAVE, how can you ensure full synchronization of SLAVE and MASTER?

Now yes, they both started with the same image, and the replication works and reports that everything is in order, BUT: * It so happened that there were errors stopping the replication, and then the replication had to be stopped and then resumed. * It is possible that changes to SLAVE accidentally occurred, and then this is not the same as MASTER. * Other scenarios that may disrupt synchronization.

Although it is possible to make a big mysqldump for both databases and compare the files, I will be interested in a method that can be implemented more easily, and can also be checked automatically so that everything is in sync.

thanks

Answer 1

Have you tried the Percona Toolkit (formerly known as Maatkit)? You can use one of your tools, which pt-table-checksum, for your business. You can also check other tools on your website.

pt-table-checksum performs an online replication integrity check for performing checksum queries on a server that produces different results for replicas that are incompatible with the wizard. secondary DSN indicates the main host. The tool exit status is non-zero if any differences are detected or any warnings or errors occur.

The following command will connect to the replication wizard on localhost, the checksum of each table and report the results on each replica detected:

Answer 2

If you have MySQL Server version 5.6.14 or higher, you can use the MySQL replication synchronization checker . It is included in the MySql server package. Designed to work exclusively with servers that support global transaction identifiers (GTID) and have gtid_mode = ON.

This utility allows you to check replication servers for synchronization. It checks for data consistency between the master and slaves, or between two slaves. The utility reports missing objects, as well as missing data. The utility can work in the topology of active replication, using the synchronization process to verify data. Those servers where replication is not active can still be checked, but the synchronization process will be skipped. In this case, the user can manually synchronize the servers.

See MySQL Documentation for more information.

Answer 3

You are right to be suspicious of the seemingly healthy configuration of master / subordinate replication! We did a great job when suddenly we got warnings from check_mk regarding the database that existed on our host, which was not on our subordinate ... but the output about the main and subordinate state was good! How does this make you nervous? A way to prove process integrity is to use checksums to verify data.

I have seen a lot of internet conversations recommending pt-table-checksum . However, its limitations turned out to be too burdensome for us to be convenient. Most importantly, it requires and even sets up replication based on instructions (see pt-table-checksum link). As stated 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 of statement-based replication that make our developers nervous because some features cannot be played back properly; see document for list.

We already have problems with the master and subordinate using replication based on instructions, so we try to avoid this.

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