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

In the master-slave architecture based on the principle of MySQL logical replication, the master-slave data is often inconsistent for some reason, which leads to the interrupt of the master-slave replication process. On the other hand, based on periodically checking the status of the IO thread and SQL thread of the slave show slave status\ G, we can only confirm that the current replication is normal, but not whether the current master-slave data is consistent. Fortunately, percona provides pt toolkit, in which pt-table-checksum and pt-table-sync cooperate with each other, based on certain conditions, can better complete the master-slave data consistency checksum repair, without greatly affecting the performance of the online database.

The official documentation of pt-table-checksum is 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.

Actually, as a verification tool, pt-table-checksum is only responsible for detecting data inconsistencies. As for the repair of discrepancy data, it is up to pt-table-sync to deal with it.

Prerequisites for using pt-table-checksum and pt-table-sync tools:

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

2. Binlog format is required to be statement. If the online database uses binlog log format is row, you can add-- no-check-binlog-format to avoid.

3. No stored procedures, triggers, or event

4. It is not recommended to repair tables with foreign key constraints

The principle of pt-table-checksum can refer to the official documentation or open general_log in a test environment, and then check the generated log after performing a pt-table-checksum. The basic principle is to create a checksums table in the main library that holds the check values for each chunk. By sorting the table according to the unique index of the primary key or, several chunk are generated by the adaptive number of row records, each row record is strung together into a string, the CRC32 value is calculated, and then the check value of the chunk is recorded in the checksums table. These SQL operations will be transferred to the slave database in the way of statement to perform the same operation, and if the data of the table is inconsistent, the corresponding chunk check values will also be inconsistent.

The script for verification and repair is as follows:

#! / bin/sh## one-way master-slave architecture, master_ip is the ip address of the master database, and slave_ip is the ip address of the slave database; in the two-way master-slave architecture, master_ip is the master database ip address based on the data of this database, and slave_ip is the alternative master database ip address where 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 "# if [- e $table_file] then rm-fr $table_filefiif [- e $diff_table] then rm-fr $diff_tablefiif [- e $sync_sql] then rm-fr $sync_sqlfi### initialization checksums Table # # $mysql_master / dev/null 2 > & 1CREATE DATABASE IF NOT EXISTS PERCONA USE PERCONA;CREATE TABLE IF NOT EXISTS checksums (db char (64) NOT NULL,tbl char (64) NOT NULL,chunk int NOT NULL,chunk_time float NULL,chunk_index varchar (200) NULL,lower_boundary text NULL,upper_boundary text NULL,this_crc char (40) NOT NULL,this_cnt int NOT NULL,master_crc char (40) 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, db, tbl) ENGINE=InnoDB EOF### filters out Innodb tables that do not contain foreign key constraints and have a unique index of the primary key or. Triggers, stored procedures and event need to manually filter out the tables involved # $mysql_master / 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 innovative InnoDB' and s.NON_UNIQUE=0 and k.POSITION_IN_UNIQUE_CONSTRAINT is null and concat (k. TABLEX SCHEMA.' K.TABLE_NAME) not in (select concat (k. TABLENAME) 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.TABLENAME into outfile "$table_file" FIELDS TERMINATED BY' | 'LINES TERMINATED BY'\ n' EOF### calls pt-table-checksum Write 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### splices at the Slave end to generate a repair command set Then execute to generate the corresponding SQL statement $mysql_slave $diff_table 2 > / dev/nullSELECT concat (db,' |', tbl) FROM percona.checksums where (master_cnt this_cnt or master_crc 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 hobbyist slavehippomagrical playbook user journal p = "$password"-- databases= "$db"-- tables= "$tb" > > $sync_sqldone### executes the difference SQL on the masterside Repair the data difference on the slave side through replication # $mysql_master / dev/null 2 > & 1set tx_isolation= "REPEATABLE-READ" Set binlog_format=statement;source $sync_sql;EOF## cleans up temporary files # rm-fr $sync_sql $table_file $diff_table

Before executing the script, several prerequisites need to be met:

1. Create a dedicated account for checksum repair.

Account creation statements: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, PROCESS, FILE, SUPER, REPLICATION SLAVE ON *. * TO 'checksums'@'%'

PS: if the login IP of checksums users is limited, you can configure only the IP of the master and slave libraries.

2. Currently, the script can only automatically filter out innodb tables with unique index or primary key without foreign key constraints, and tables involved in triggers, stored procedures and event, which need to be manually eliminated.

3. The script only needs to be deployed on the side of the main library. It does not need to be deployed on the slave side.

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.