Shulou(Shulou.com)06/01 Report--

Pt-table-sync repair master is not consistent

Introduction to pt-table-sync

As the name implies, it is used to fix data inconsistencies between multiple instances. It can make the master-slave data repair to the final consistency, and it can also make multiple unrelated database instances through the application of double-write or multi-write to be consistent. At the same time, it also integrates the verification function of pt-table-checksum, which can be repaired at the same time, or based on the calculation results of pt-table-checksum.

working principle

1. Calculation of checksum value of single row data

The computing logic, like pt-table-checksum, first checks the table structure, gets the data types of each column, converts all data types into strings, and then concatenates them with the concat_ws () function, thus calculating the checksum value of the row. Checksum is calculated using crc32 by default.

two。 Calculation of checksum value of data block

Like the pt-table-checksum tool, pt-table-sync intelligently analyzes the indexes on the table and then split the table's data into several chunk, which is calculated in units of chunk. It can be understood as splicing the data of all the rows in the chunk, and then calculating the value of the crc32 to get the checksum value of the chunk.

3. Bad block detection and repair

In the first two steps, the algorithm and principle of pt-table-sync and pt-table-checksum are the same. Further down, it starts to be different:

Pt-table-checksum is just a check, so it stores the checksum results in a statistical table, and then records the executed sql statements in binlog, and the task is complete. Statement-level replication passes the calculation logic to the slave library and performs the same calculation in the slave library. Pt-table-checksum 's algorithm itself does not care about the delay from the library, the delay is calculated the same (some colleagues do not understand this, please refer to my previous article), it will not affect the correctness of the calculation results (but we will still detect the delay, because too much delay will affect the business, so we always add-max-lag to limit the current).

Pt-table-sync is different. First of all, it needs to complete the calculation of the checksum value of chunk. Once it is found that the checksum value of the same chunk on the master and slave is different, it goes deep into the chunk, compares and fixes the defective rows line by line. The calculation logic is described as follows (take fixing the data inconsistency of the master-slave structure as an example, the situation of business double writing is more complex to repair-because the issues related to conflict resolution and benchmark selection are limited to space, which is not covered here):

1. For each slave library, each table, loop the following checksum repair process.

two。 For each chunk, add a for update lock when checking. Once the lock is acquired, record the show master status value of the current main library.

3. Execute the select master_pos_wait () function on the slave library, waiting for the sql thread from the library to execute to the location where show master status gets it. In order to ensure that the content of the master and subordinate about this chunk will not be changed.

4. Checksum this chunk and compare it with the checksum of the main library.

5. If the checksum is the same, the master and slave data are the same, then move on to the next chunk.

6. If the checksum is different, the chunk is inconsistent. Dig inside the chunk, calculate the checksum line by line, and compare it (the comparison process of a single line of checksum is the same as that of chunk, which is actually a special case of chunk's size of 1).

7. If a line is found to be inconsistent, mark it. Continue to detect the remaining lines until the chunk ends.

8. For the found rows that are inconsistent with the master, use the replace into statement to execute once in the master database to generate the full binlog of the row, and synchronize it to the slave database, which will repair the slave library based on the data of the master database. For rows that have rows in the master library but not in the slave library, use replace to insert them on the master library (must not be insert) Rows that exist in the slave library but not in the master library are deleted by performing delete in the master library (pt-table-sync strongly recommends that all data repairs are done only in the master library, rather than directly modifying slave data; however, there are exceptions, which will be discussed later).

9. Until all inconsistent lines of the chunk are fixed. Continue to check and repair the next chunk.

10. Until all the tables on the library are repaired. Start repairing the next slave library.

Important option

Security option

-[no] check-triggers checks to see if there are triggers, and warns if so

-[no] foreign-key-checks checks primary foreign key constraints by default, and warns if any

-[no] unique-checks checks whether there is a unique index. If not, a warning is given.

Filtering option

-ignore-databases

-ignore-engines

-ignore-tables

Other options

-replicate=s is combined with pt-table-checksum to fix but not verify. Results of verification before using pt-table-checksum

-bidirectional bidirectional synchronization. Usually, it is based on the data of the main database. If two-way synchronization is enabled, it is necessary to define conflict resolution rules, which will be more complicated.

Pt-table-checksum command

Cd / data/work/percona-toolkit-2.1.1/bin

. / pt-table-sync-- print hobbies 192.168.0.90-- sync-to-master hobbies 192.168.0.5-- sync-to-master hobbies 192.168.0.5 Prun3650

Use superdba because you need too much permission on the table

In front is slave ip,port,user,password.

Then there's master ip,port,user,password.

D-database

T-table

-- print: prints, but does not execute commands.

-- execute: execute the command.

This indicates that the repair is complete. You can check it again to make sure it's repaired.

The replace table must have a primary key or unique index, and the table cannot have triggers, otherwise this scheme does not apply

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.