MySQL pt-table-checksum and Pt-table-sync checksum repair master-Slave consistency

"Pt-table-checksum"
Pt-table-checksum is one of the Percona-toolkit series tools that can be used to detect the consistency of the data in the master and from the database. The principle is to run on the main library, checksum the synchronized tables and record them. It then compares the checksum of the main table to determine whether the data is consistent. The detection process in blocks, for large tables can be differentiated into multiple blocks, so as to avoid the lock table (according to a unique index to split the table into blocks) will automatically determine the replication delay, the load of master, after the threshold will automatically pause detection.

"Pt-table-sync"
Pt-table-sync, as its name implies, is used to repair inconsistencies between multiple instances of data. It allows the master-slave data to be repaired to the final consistency, or it can be fixed by applying multiple unrelated database instances that are double-written or multi-writable. At the same time, it also integrates the Pt-table-checksum verification function, which can be repaired on one side or based on the pt-table-checksum calculation results.

• Checksum (master server run)

Pt-table-checksum--nocheck-binlog-format--nocheck-plan--nocheck-replication-filters--replicate=pt.checksums-- Set-vars innodb_lock_wait_timeout=120--databases newtable-u ' checksums '-P ' checksums '-h10.1.1.6#-h-u-p-p-s-d connection Information #- -nocheck-replication-filters detection ignores mysql configuration parameter binlog_ignore_db and so on. #--nocheck-binlog-format does not detect the log format #--replicate specify checksum stored db and table, such as test.checksum#--chunk-size,--chunk-size-limit Used to specify the size of the detection block. More controllability #--ignore-databases/tables/column jump out of the specified element's filter #--lock-wait-timeout InnoDB lock timeout setting, default is #--max-load set maximum number of concurrent connections #-- The replicate-check-only only outputs inconsistent information for the data. #--help has this on the line, as well as other details in the documentation. Note:--no-check-binlog-format ignore check binlog format, otherwise will error, default will go to check statement mode, generally we all use row mode

　　

TS : Time to complete the check.
errors: The number of errors and warnings that occurred while checking.
diffs:0 is consistent, and 1 means inconsistent. When--no-replicate-check is specified, it will always be 0 when the specified--replicate-check-only displays different information.
rows: The number of rows in the table.
chunks: The number of blocks that are divided into tables.
skipped: The number of blocks skipped due to errors or warnings or too large.
time: The time of execution.
table: The name of the table being checked.

Note: The main observation diffs column, identifies the difference number.

• Viewing differences (Slave library runs)

Select db, TBL, sum (this_cnt) as Total_rows, COUNT (*) as chunks   

　　

• replication, eliminating differences (master server run)

Suppose there is a difference in the Newtables database tb1 table

I. Automatic elimination of differences (not recommended) Pt-table-sync--print--execute--sync-to-master h=10.1.1.7,p=3306,u=checksums,p= ' checksums '-- Databases=newtables--tables=tb1ii. Print out SQL statements, human intervention to Slave library Execution (recommended) Pt-table-sync--print--sync-to-master h=10.1.1.7,p= 3306,u=checksums,p= ' checksums '--databases=newtables--tables=tb1pt-table-sync--print--sync-to-master h=10.1.1.7, p=3306,u=checksums,p= ' checksums '--replicate pt.checksums#--sync-to-master: Specifies a DSN, which is the IP from which he will pass show Processlist or show slave status goes to Automatic Brahma. #--replicate: Specifies the table obtained through Pt-table-checksum, which will almost always be used for all 2 tools. #--print: print, but do not execute the command. #--execute: Executes the command.

　　Note: Slave need to authorize the main library drop and create temporary tables permissions

• Inspection

Re-execute the pt-table-checksum once to see if there are any differences.

MySQL pt-table-checksum and Pt-table-sync checksum repair master-Slave consistency