Check and repair data consistency of MySQL Master/Slave servers

The last time I encountered the problem of data consistency between the MySQL master and slave servers, I thought about it a few years ago. I vaguely remembered the panic situation. Fortunately, I finally solved the problem with Maatkit. A few years later, when I faced the same problem again, Maatkit no longer exists and turned into a part of PerconaToolkit. What remains unchanged is that I am still confused.

The last time I encountered the problem of data consistency between the MySQL master and slave servers, I thought about it a few years ago. I vaguely remembered the panic situation. Fortunately, I finally solved the problem with Maatkit. A few years later, when I faced the same problem again, Maatkit no longer exists and turned into a part of Percona Toolkit. What remains unchanged is that I am still confused.

The last time I encountered the problem of data consistency between the MySQL master and slave servers, I thought about it a few years ago. I vaguely remembered the panic situation. Fortunately, I finally solved the problem with Maatkit. A few years later, when I faced the same problem again, Maatkit no longer exists and turned into a part of Percona Toolkit. What remains unchanged is that I am still busy, so I should record it, it is not guaranteed that this problem will occur at any time.

If you encounter an error message similar to the following on the MySQL slave server, congratulations:

mysql> SHOW SLAVE STATUS\GLast_Errno: 1062Last_Error: Error 'Duplicate entry '...' for key ...' on query.

Why is there a unique duplicate index key value? The biggest possible cause is the wrong write operation on the slave server! When this type of error occurs, many people will skip the error using the SQL _slave_skip_counter operation, or even some people write the script. If there are multiple errors, execute SQL _slave_skip_counter multiple times in a loop:

mysql> SET GLOBAL sql_slave_skip_counter = 1;mysql> START SLAVE;

Unfortunately, even if the SQL _slave_skip_counter operation can temporarily restore the master and slave nodes, most of the data consistency has been damaged more seriously, and the problem that will be covered one day later will pop up again.

The pt-table-checksum and pt-table-sync in Percona Toolkit can solve such problems. They are easy to install. You can download the rpm or deb software package based on your operating system. Of course, you can also use the source code for installation, make sure that the system has installed the dependent Perl software package:

shell> perl -MCPAN -e 'install DBI'shell> perl -MCPAN -e 'install DBD::mysql'shell> perl -MCPAN -e 'install Term::ReadKey'

By the way, when I install some Perl modules, the following error message appears:

Can't locate object method "install" via package "…"

If you have encountered a similar problem, you can go to the Perl Command Line to install:

shell> perl -MCPAN -e shellcpan> install ...

The remaining steps for installing Percona Toolkit are the fixed strategy of the Perl software:

shell> perl Makefile.PLshell> makeshell> make install

Supplement: pt-slave-restart in Percona Toolkit can replace SQL _slave_skip_counter:

shell> pt-slave-restart    --host=
 
      --port=
  
       --user=
   
        --password=
    
         --error-numbers=1062
    
   
  
 

The foreplay should be done here. Let's take a look at how to solve the problem:

Check the Data Consistency of MySQL Master/Slave servers

By running pt-table-checksum on the master server, it calculates the hash value of each table through a series of MySQL functions, using the master-slave replication relationship, replay the same computing process on the slave server and obtain the hash values of the master and slave servers. It is okay to compare whether the hash values are the same.

There are two points to note:

• When calculating the hash value of a table, pt-table-checksum does not directly calculate the hash value of the entire table, but is calculated in blocks. This avoids long latency on the slave server.
• Because the process of calculating hashes through MySQL functions needs to be replayed on the slave server, the format of master-slave replication must be based on STATEMENT and cannot be based on ROW.

The actual commands are as follows:

shell> pt-table-checksum \    --replicate=percona.checksums \    --host=
 
   \    --user=
  
    \    --password=
   
  
 

Note: The replicate option specifies the database and table to which the result is saved. If you want to, you can manually query it:

SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunksFROM percona.checksumsWHERE (    master_cnt <> this_cnt    OR master_crc <> this_crc    OR ISNULL(master_crc) <> ISNULL(this_crc))GROUP BY db, tbl;

BTW: In most cases, you only need to compare "master_crc <> this_crc.

Restoration of MySQL master-slave server data consistency

By running pt-table-sync on the master server, it will reconstruct the data and synchronize the data from the master server to the slave server through replication, thus restoring the consistency. During the operation, you can use the pt-table-checksum result.

shell> pt-table-sync \    --execute \    --replicate=percona.checksums \    --charset=
 
   \    --host=
  
    \    --user=
   
     \    --password=
    
   
  
 

Note: Because pt-table-sync will reconstruct data, it is risky to back up data in advance. If you are still not at ease, you can use the "print" option provided by it to print the corresponding SQL statement. You can review the operations that have been performed, then, manually execute the task to complete synchronization.

...

When using Percona Toolkit, careful netizens will find that there are two ways to pass parameters:

• -Host = -User = -Password =
• H = , U = , P =

The former is the option form, and the latter is the DSN form. If the option format is used, the system will automatically convert it to The DSN form internally. Generally, different information is in the DSN form, and public information is in the option form. For example, I want to pass multiple DSN host information. Their host names are different, but the user names and passwords are the same. In this case, I will use the option to pass the user name and password, the host name is passed in the form of DSN.

...

In this example, we directly type sensitive information such as passwords when running the Percona Toolkit command for convenience, which is often insecure, for example, you can view the command history to get the password. Fortunately, we have the ask-pass option to solve this problem. In fact, we can go further and directly save sensitive information such as passwords to the configuration file, the most common configuration file is 「~ /. My. cnf. In addition, there are several more official configuration files to choose from. We can see them in the source code:

default_files => [    "/etc/percona-toolkit/percona-toolkit.conf",    "/etc/percona-toolkit/$program_name.conf",    "$home/.percona-toolkit.conf",    "$home/.$program_name.conf",]

...

As the saying goes: if you are not afraid of thieves, you are afraid of thieves thinking. The same is true when you look at a problem: if you are not afraid of a problem, you are afraid that the problem lurks in the dark, but you do not know anything about it. Check the consistency of the master and slave databases.

Original article address: Check and fix the Data Consistency of the MySQL Master/Slave server. Thank you for sharing it with me.