MySQL master-slave data consistency check based on Pt-table-checksum and Pt-table-sync

The master-slave architecture based on the principle of MySQL logical replication often results in inconsistent master-slave data due to some reasons, which causes the master-slave copy process to interrupt the error. Instead of checking the status of the IO thread and the SQL thread from the library's show Slave status\g on a regular basis, you can only confirm that the current replication is normal, but cannot confirm that the current master-slave data is consistent. Fortunately, Percona Company provides PT toolkit, wherein the pt-table-checksum and Pt-table-sync cooperate with each other, under certain preconditions, can better complete the master-slave data consistency check and repair, and do not affect the performance of the online database to a large extent.

The official documents of Pt-table-checksum are as follows:

pt-table-checksum performs an online replication consistency check by  executing checksum queries on the master,which produces different  Results on replicas that are inconsistent with the master. the  optional dsn specifies themaster host. the tool ' s  "EXIT STATUS"  is non-zero if any differences are found, or if any  Warnings or errors occur. The following command will connect to the replication master on  localhost, checksum every table, and report theresults on every  detected replica:pt-table-checksumThis tool is focused on finding  data differences efficiently. if any data is different, You can resolve the problem withpt-table-sync. 

Pt-table-checksum in fact, as a verification tool, only responsible for detecting inconsistencies in data. As for the repair of the differential data, it is referred to Pt-table-sync.

Prerequisites for using the Pt-table-checksum and Pt-table-sync tools:

1. The table must have a primary key or unique index

2, Request Binlog format for statement. If the online database is using the Binlog log format is row, you can add--no-check-binlog-format to circumvent.

3. Cannot have stored procedures, triggers, event

4. It is not recommended to fix a table with foreign KEY constraints

Pt-table-checksum principle can check the official document or in the test environment to open the General_log, perform a pt-table-checksum and then check the generated log. The rationale is to create a checksums table in the main library that holds the checksum value for each chunk. By sorting the table according to the primary key or unique index, a number of chunk are generated on the basis of the number of row records that are adaptive, a string of each row record is converted into strings, the CRC32 value is computed, and the checksum value of the chunk is recorded in the checksums table. These SQL operations will be statement to the same operation from the library, and if the data of the table is inconsistent, the corresponding chunk checksum value will be inconsistent.

The script for the checksum & Repair is as follows:

#!/bin/sh# #单向主从架构的话, MASTER_IP is the IP address of the main library, SLAVE_IP is the IP address of the library, and the two-way master-slave architecture, MASTER_IP is the main library IP address based on this library data, slave_ IP is the alternate main library IP address for which the data is modified. master_ip= "192.168.124.131"      slave_ip= "192.168.124.132" port= "3306" user= "checksums" Password= "checksums" pt_sync= "/usr/bin/pt-table-sync" pt_check= "/usr/bin/pt-table-checksum" mysql= "/usr/local/ Mysql/bin/mysql "mysql_master=" $mysql  -u$user -p$password -h$master_ip -p$port "mysql_slave= "$mysql  -u$user -p$password -h$slave_ip -P$port -N " table_file= "/tmp/table.txt" Diff_table= "/tmp/diff.txt" sync_sql= "/tmp/sync.sql" ###  clean environment  ## #if  [ -e  $table _file  ]then        rm -fr  $table _FILEFIIF&NBSP;[&NBSP;-E   $diff _table ]then        rm -fr  $diff _tablefiif  [ -e  $sync _sql ]then        rm -fr $ sync_sqlfi###  Initialize CHEcksums table  ### $mysql _master << eof >/dev/null 2>&1create database  IF NOT EXISTS PERCONA; use percona; create table if not exists checksums  (Db char ()  NOT NULL,tbl  char (&NBSP;NOT&NBSP;NULL,CHUNK&NBSP;INT&NBSP;NOT&NBSP;NULL,CHUNK_TIME&NBSP;FLOAT&NBSP;NULL,CHUNK_) Index varchar ( null,lower_boundary text null,upper_boundary text null,this_) Crc char ( not null,this_cnt int not null,master_crc char)  NULL, Master_cnt int null,ts timestamp not null default current_timestamp on  UPDATE CURRENT_TIMESTAMP,PRIMARY KEY  (db, tbl, chunk), index ts_db_tbl   (TS,&NBSP;DB,&NBSP;TBL)  ENGINE=InnoDB; eof###  filters out InnoDB tables that do not contain foreign key constraints and have a primary key or unique index. Triggers, stored procedures, and event needs to manually filter out the tables involved  ### $mysql _master << eof >/dev/null 2>&1select t.table_schema,t.table_name from information_schema.tables tinner  join information_schema.statistics s on t.TABLE_SCHEMA=s.TABLE_SCHEMA and  T.table_name=s.table_nameinner join information_schema.key_column_usage kon t.table_schema =k.table_schema and t.table_name=k.table_namewhere t.table_type= ' BASE TABLE '  and  t.engine= ' InnoDB '  and s.non_unique=0 and k.position_in_unique_constraint is  null and concat (K.table_schema, '. ', K.table_name)  not in  (Select concat (k. Table_schema, '. ', K.table_name)  from information_schema.key_column_usage k where k. Position_in_unique_constraint is not null)  and t.TABLE_SCHEMA not in  ( ' MySQL ', ' percona ', ' sys ', ' information_schema ', ' Performance_schema ')  group by t.table_schema,t. table_name into outfile  "$table _file"  FIELDS TERMINATED BY  ' | '  LINES TERMINATED BY  ' \ n '; eof###  calls Pt-table-checksum, makes data difference, writes the result to Percona.checksums table  ## #for  i in $ (cat $ Table_file) do        db=$ (echo  $i |awk -f\|  ' {print  $1} ')         tb=$ (echo  $i |awk -f\|  ' {print $ 2} ')          $pt _check --set-vars innodb_lock_wait_timeout= 120,binlog_format= ' statement '  -u$user -p$password -h$master_ip -p$port --databases=$ db --tables= $TB  >/dev/null 2>&1done###  on the slave side stitching generates the repaired command set, and then executes the corresponding SQL statement generated $mysql_ slave << eof 1> $diff _table 2>/dev/nullselect concat (db, ' | ', TBL)  FROM  percona.checksums where  (&NBSP;MASTER_CNT&NBSP;&LT;&GT;&NBSP;THIS_CNT&NBSP;OR&NBSP;MASTER_CRC  <>&nBsp;this_crc or isnull (MASTER_CRC) <>isnull (THIS_CRC))  group by db, tbl  ; eoffor i in $ (cat  $diff _table) do        db=$ (Echo   $i |awk -f\|  ' {print $1} ')         tb=$ (echo $ i|awk -f\|  ' {print $2} ')          $pt _sync --print  --sync-to-master h= $slave _ip,p= $port, u= $user, p= "$password"  --databases= "$db"  --tables= "$ TB  >>  $sync _sqldone###  performs differential SQL on the master side and repairs slave side data differences  ### $mysql _master < < eof >/dev/null 2>&1set tx_isolation= "Repeatable-read"; set binlog_ format=statement;source  $sync _sql; eof##  Cleanup Temporary files  ## #rm  -fr  $sync _sql  $table _file  $diff _table

Before you execute the script, you need to meet several prerequisites:

1, create a dedicated account for the checksum repair.

Account creation statement: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, PROCESS, FILE, SUPER, REPLICATION SLAVE on * * to ' checksums ' @ '% '

PS: If the login IP of the checksums user is limited, only the main library and the IP from the library can be configured.

2, the current script can only automatically filter out a unique index or primary key, without foreign key constraints of the InnoDB table, there are triggers, stored procedures and the table involved in the event, the need for manual culling.

3. The script can only be deployed on the main library side. It does not need to be deployed from the library side.

MySQL master-slave data consistency check based on Pt-table-checksum and Pt-table-sync