Mysql master-slave verification tools pt-table-checksum and pt-table-sync

pt-table-checksum and pt-table-sync

1. Download and install percona-toolkit and the required dependencies

wget /downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.

yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes -y

rpm -ivh  percona-toolkit-2.2.

• 1
• 2
• 3
• 4
• 5

AI writing code

2. pt-table-checksum

   Purpose: It can be used to detect the consistency of data in the master and slave databases.

   Principle: Run on the main library, checksum the synchronized table, and record it. Then compare whether the checksums of each table in the master and slave are consistent, so as to determine whether the data is consistent.

3. pt-table-sync

   Purpose: Used to fix data inconsistencies between multiple instances. It can repair the master-slave data to the final consistency, and can also repair multiple unrelated database instances by applying double-write or multiple writes to the consistency. At the same time, it also integrates the verification function of pt-table-checksum, which can be repaired while verifying, or it can be repaired based on the calculation results of pt-table-checksum.

   principle:
   First check the table, then get the data type of each column, then convert the type into a string, and use concat_wa()functionMake a connection, and then calculate the checksum value with crc32 verification;

4. Using pt-table-checksum

   #Check whether the table is synchronized. DIFFS of 1 means data is inconsistent.

pt-table-checksum --nocheck-replication-filters  --no-check-binlog-format --replicate= --databases=acsp  h=127.0.0.1,u=root,p=password,P=3306

• 1

AI writing code

Detailed explanation of parameters:
–nocheck-replication-filters: Do not check replication filtering rules, such as replicate-ignore-db, replicate-wild-do-table.
–no-check-binlog-format: Do not check the copied binlog mode. If the binlog mode is row mode, this parameter needs to be enabled.
--create-replicate-table: This parameter is required to enable the checksum for the first time, and the checksum table will be created to store the results.
–replicate=: The table that stores checksum results.
–databases: Indicates the library to be checked.
–tables(-t): indicates the table to be checked.
–replicate-check-only: Indicates that only out-of-sync tables are displayed.
–recursion-method: Under normal circumstances, the tool will automatically recognize the slave library. If the recognition fails, you can use this parameter to specify the method to find slave. There are four parameters, namely processlist,hosts, dsn=DSN, and no, which are used to decide whether to find slaves through show processlist, show slave hosts or dsn=DSN.

The above parameters can be selected according to your own verification situation.

5. Synchronous statements

pt-table-sync --replicate=acsp.checksums h=127.0.0.1,u=root,p=password h=192.168.1.21,u=root,p=passowrd --print --tables=acsp.tb_so_synthesis

• 1

AI writing code

Detailed explanation of parameters:
Add the IP and user password from the library afterward

–print: It means to print out the statement that needs to be synchronized
–tables: represents the specified checking of a table
–execute: If you add –execute directly, it means that you will execute directly without viewing the statement as follows:

pt-table-sync --replicate=acsp.checksums h=127.0.0.1,u=root,p=password  h=192.168.1.21,u=root,p=password--execute  

• 1

AI writing code