pt-table-checksum and pt-table-sync, the former is mainly used to verify the consistency of master and slave, and the latter is mainly used to repair data. The combination of the two can repair the problem of data inconsistency.
1. pt-table-checksum installation
The latest address for downloading the toolkit is as follows:
https://www.percona.com/downloads/percona-toolkit/LATEST/
Install the pt-table-checksum and pt-table-sync commands. You need to install the percona-toolkit toolset first
1.1 Percona-toolkit toolkit toolkit installation method introduction:
There are three ways to install the percona-toolkit tool:
The first is to download binary packages and decompress them directly, so they can be used.
The second is to download source packages for compilation and installation.
The third is rpm package installation
Whether it's a binary installation, a source package compilation installation, or an rpm package installation, download the dependent packages before installation
yum ×××tall perl perl-devel perl-CPAN perl-DBD-MySQL perl-Time-HiRes perl-DBI perl-Digest-MD5 -y
1.2 Download source package compilation and installation:
yum ×××tall perl perl-devel perl-CPAN perl-DBD-MySQL perl-Time-HiRes perl-DBI perl-Digest-MD5 -y tar xf percona-toolkit-3.0.13.tar.gz [root@localhost ~]# cd percona-toolkit-3.0.13 [root@localhost percona-toolkit-3.0.13]# perl Makefile.PL Checking if your kit is complete... Looks good Writing Makefile for percona-toolkit make && make ×××tall
1.3 Download the binary package and install it directly by decompressing:
yum ×××tall perl perl-devel perl-CPAN perl-DBD-MySQL perl-Time-HiRes perl-DBI perl-Digest-MD5 -y tar xf percona-toolkit-3.0.11_x86_64.tar.gz -C /usr/local/ mv percona-toolkit-3.0.11_x86_64 /usr/local/percona-toolkit [root@ks-es11 local]# cat /etc/profile.d/percona-toolkit.sh export PATH=$PATH:/usr/local/percona-toolkit/bin [root@ks-es11 local]# cat /etc/profile.d/mysql.sh export PATH=$PATH:/usr/local/mysql7/bin
2. Fast construction of master-slave replication based on Gtid
master 192.168.0.39
slave 192.168.0.11
2.1. Operation on master:
mysql -uroot -p'jiawu256789' -e "ggrant replication slave on *.* to rept@'192.168.0.11' identified by 'JuwoSdk21TbUser'; flush privileges;" mysqldump -uroot -p'jiawu256789' -B -A -F --master-data=2 --single-transaction --set-gtid-purged=OFF --events|gzip >/opt/juwo_$(date +%F).sql.gz scp -rp -P10239 /opt/juwo_$(date +%F).sql.gz root@192.168.0.11:/root
2.2. Operation on slave:
gzip -d /root/juwo_$(date +%F).sql.gz mysql -uroot -p'jiawu256789' -e "source /root/juwo_$(date +%F).sql;" mysql -uroot -p'jiawu256789' -e "CHANGE MASTER TO MASTER_HOST='192.168.0.39',MASTER_PORT=3306,MASTER_USER='rept',MASTER_PASSWORD='JuwoSdk21TbUser',MASTER_AUTO_POSITION = 1;start slave;show slave status\G"
3. Permission to create the required link libraries
The privileges needed to create pt-table-checksum and pt-table-sync accounts to manipulate mysql and to repair data
Log in to slave and master (both sides execute the following SQL)
master(192.168.0.18)
slave(192.168.0.22)
grant update,×××ert,select,create,drop,delete,index,execute,super,process,replication slave on *.* to ptsum@'192.168.0.%' identified by 'ptchecksums'; flush privileges;
IV. Simulated Demonstration
4.1 Creating Demonstration Data Environment
New test libraries and test tables are built on master libraries. The SQL instructions are as follows:
create database test01; use test01; CREATE TABLE `frame01` ( `id` int(11) NOT NULL AUTO_INCREMENT,`parent_id` int(11) DEFAULT NULL,`dsn` varchar(255) NOT NULL,PRIMARY KEY (`id`)); ×××ert into `frame01` values(1,1,'192.168.0.22,u=pt22,p=ptchecksums,P=3307'); ×××ert into `frame01` values(2,2,'192.168.0.33,u=pt33,p=ptchecksums,P=3308');
Slve view data has been synchronized
mysql> select * from frame01; +----+-----------+------------------------------------------+ | id | parent_id | dsn | +----+-----------+------------------------------------------+ | 1 | 1 | 192.168.0.22,u=pt22,p=ptchecksums,P=3307 | | 2 | 2 | 192.168.0.33,u=pt33,p=ptchecksums,P=3308 | +----+-----------+------------------------------------------+ 2 rows in set (0.00 sec)
Slve library modifies frame 01 table number, simulation data is inconsistent
update frame01 set dsn='192.168.0.55,u=umaaa,p=ptchsyeudew,P=3310' where id=1; update frame01 set dsn='192.168.0.66,u=umbbb,p=ptchsyeudew,P=3311' where id=2; mysql> select * from frame01; +----+-----------+-------------------------------------------+ | id | parent_id | dsn | +----+-----------+-------------------------------------------+ | 1 | 1 | 192.168.0.55,u=umaaa,p=ptchsyeudew,P=3310 | | 2 | 2 | 192.168.0.66,u=umbbb,p=ptchsyeudew,P=3311 | +----+-----------+-------------------------------------------+
4.2 pt-table-checksum demonstration
The new version of pt-table-ckecksum does not need to add checksums tables by itself. When pt-table-checksum tables are used, percona libraries and checksums tables are generated by default on master libraries.
[root@kusou-es11 ~]# pt-table-checksum h=192.168.0.39,u=ptsum,p='ptchecksums',P=3306 --tables=test01.frame01 --no-check-binlog-format --nocheck-replication-filters --recursion-method="processlist"
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
06-15T10:37:41 0 1 2 0 1 0 0.314 test01.frame01By default, libraries percona and table checksums are generated on the master library
mysql> select * from percona.checksums; +--------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ | db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts | +--------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ | test01 | frame01 | 1 | 0.001818 | NULL | NULL | NULL | 2eedbb7c | 2 | 2eedbb7c | 2 | 2019-06-15 11:08:00 | +--------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ 1 row in set (0.00 sec)
[root@kusou-es11 ~]# pt-table-checksum h=192.168.0.39,u=ptsum,p='ptchecksums',P=3306 --databases=test01 --nocheck-replication-filters --replicate=test01.checksums --no-check-binlog-format --recursion-method="processlist"
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
06-15T10:40:18 0 1 2 0 1 0 0.317 test01.frame014.3. Instruction parameter description of pt-table-checksum
- nocheck-replication-filters means that replication filters are not checked; - replicate=test01.checksums writes the results to the checksums table in the TEST01 Library of mysql; The database checked by databases=test01 is test01, where multiple libraries can be written and partitioned by commas. No-check-binlog-format does not check the binlog log format. pt-table-checksum runs in the state replication format. If the binlog log format is row, it will report errors. The binlog log check is removed by the above-no-check-binlog-format to prevent errors. --recursion-method="proccesslist" then the tool will automatically find the slave library from the main library in the show process list, without this parameter, which is also adopted by default, and another way is dns, which will be demonstrated later.
The checksums table is written to the test01 Library of master 192.168.0.39. Log on to the main library to view
mysql> select * from test01.checksums; +--------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ | db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts | +--------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ | test01 | frame01 | 1 | 0.001907 | NULL | NULL | NULL | 2eedbb7c | 2 | 2eedbb7c | 2 | 2019-06-15 11:28:02 | +--------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ 1 row in set (0.00 sec)
4.4. Explanation of output parameters
TS: Time to complete the inspection.
ERRORS: Number of errors and warnings that occur when checking.
DIFFS:0 represents consistency and 1 represents inconsistency. When you specify -- no-replicate-check, it will always be 0, and when you specify -- replicate-check-only, it will display different information.
The demonstration is as follows:
[root@kusou-es11 ~]# pt-table-checksum h=192.168.0.39,u=ptsum,p='ptchecksums',P=3306 --databases=test01 --no-check-binlog-format --nocheck-replication-filters --no-replicate-check --recursion-method="processlist"
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
06-15T11:08:00 0 0 2 0 1 0 0.012 test01.frame01The data of test01 library on master and test01 library on slave are inconsistent, but when pt-table-checksum is detected, with the parameter-no-replicate-check, DIFFS will always be 0.
When specified -- replicate-check-only, different information is displayed:
[root@kusou-es11 ~]# pt-table-checksum h=192.168.0.39,u=ptsum,p='ptchecksums',P=3306 --databases=test01 --no-check-binlog-format --nocheck-replication-filters --replicate-check-only --recursion-method="processlist" Checking if all tables can be checksummed ... Starting checksum ... Differences on kusou-es11 TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY test01.frame01 1 0 1
ROWS: Number of rows in a table.
CHUNKS: Number of blocks divided into tables.
SKIPPED: The number of blocks skipped due to errors or warnings or excessive size.
TIME: Execution time.
TABLE: The name of the table being checked
4.5. Using dsn to verify data:
The instruction parameters of pt-table-checksum+dsn mode are described as follows:
- nocheck-replication-filters: No replication filters are checked, and it is recommended that they be enabled. Later, you can use -- databases to specify the database to be checked. no-check-binlog-format: Do not check the duplicated binlog mode, if the binlog mode is ROW, it will report an error. --replicate-check-only: Displays only asynchronous information. - replicate=: Write the checksums information into the specified table, recommending that it be written directly to the database being checked. -- databases=: Specify the database to be checked, and separate multiple databases by commas. --tables=: Specify tables that need to be checked, separated by commas h=192.168.0.39: Master's intranet address u=ptsum: User name p=ptchecksums: password P=3306: Port dsn=D: Represents checking in dsn mode, D is database t=dsns: Represents using this table
Configure dsn mode to verify data:
Login 192.168.0.39 master Library to Create Route Table Linking Slve Library
mysql> use percona; mysql>CREATE TABLE `dsns` ( `id` int(11) NOT NULL AUTO_INCREMENT,`parent_id` int(11) DEFAULT NULL,`dsn` varchar(255) NOT NULL,PRIMARY KEY (`id`)); mysql>######Xxert into DSNs values (1,1,'h = slave IP,u=username,p=password,P=3306'); mysql>×××ert into dsns values(1,1,'h=192.168.0.11,u=ptsum,p=ptchecksums,P=3306');
[root@kusou-es11 ~]# pt-table-checksum --nocheck-replication-filters --replicate=percona.checksums --databases=test01 --tables=frame01 --no-check-binlog-format h=192.168.0.39,u=ptsum,p=ptchecksums,P=3306 --recursion-method dsn=D=percona,t=dsns,h=192.168.0.11,u=ptsum,p=ptchecksums,P=3306
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
06-15T11:45:39 0 1 2 0 1 0 0.317 test01.frame01DIFFS =1 is also visible; the frame 01 table data in the test01 libraries of master and slave are inconsistent
Instruction parameters are briefly described:
The first section of the ip, account, password and port refers to the account information connected to the main library.
h=192.168.0.39,u=ptsum,p=ptchecksums,P=3306
--recursion-method dsn = Check data by linking to dsn
D=percona,t=dsns, read from the percona library the account information of the link from the library in the DSNs table
The second section, ip, account, password and port, refers to the account information connected to the slave library.
h=192.168.0.11,u=ptsum,p=ptchecksums,P=3306
Tip: This pt-table-checksum instruction reads account information from the percona.dsns table on the slave library 192.168.0.11 and compares it with the data from the test01.frame 01 table in the main library 192.168.0.39.
If you log in 192.168.0.11 from the library, delete the data from the percona.dsns table of the slave library, and then make pt-table-checksum comparison, you will be prompted that the connection can not be made from the library, resulting in the failure of the check.
mysql> select * from dsns;
+----+-----------+---------------------------------------------+
| id | parent_id | dsn |
+----+-----------+---------------------------------------------+
| 1 | 1 | h=192.168.0.11,u=ptsum,p=ptchecksums,P=3306 |
+----+-----------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> delete from dsns where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from dsns;
[root@kusou-es11 ~]# pt-table-checksum --nocheck-replication-filters --replicate=percona.checksums --databases=test01 --tables=frame01 --no-check-binlog-format h=192.168.0.39,u=ptsum,p=ptchecksums,P=3306 --recursion-method dsn=D=percona,t=dsns,h=192.168.0.11,u=ptsum,p=ptchecksums,P=3306
Checking if all tables can be checksummed ...
Starting checksum ...
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
06-15T12:23:40 0 0 2 0 1 0 0.009 test01.frame01Error Tip: Diffs cannot be detected because no slaves were found. Please read the -- recursion-method documentation for information.
DIFFS = 0, but in fact, the data of test01.frame01 tables of master and slave libraries are inconsistent, and the check-and-match fails.
[root@kusou-es11 ~]# pt-table-checksum --nocheck-replication-filters --replicate=percona.checksums --databases=test01 --tables=frame01 --no-check-binlog-format h=192.168.0.39,u=ptsum,p=ptchecksums,P=3306 --recursion-method dsn=D=percona,t=dsns
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
06-15T12:53:54 0 1 2 0 1 0 0.316 test01.frame01 DIFFS =1 indicates that the master and slave data are inconsistent and the detection is successful.
It is possible to detect the success of executing the above commands because they read the account information of the linked slave library directly from the percona.dsns table of the main library 192.168.0.39.
5. Errors in the Demonstration
The following instructions are operated on the slave library:
Error Reporting 1: [root@localhost local]# /usr/local/percona-toolkit/bin/pt-table-checksum h=192.168.0.39,u=ptsum,p='ptchecksums',P=3306 --databases=mysql --no-check-binlog-format --nocheck-replication-filters Can't locate Digest/MD5.pm in @INC (@INC conta×××: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/percona-toolkit/bin/pt-table-checksum line 789. BEGIN failed--compilation aborted at /usr/local/percona-toolkit/bin/pt-table-checksum line 789.
Solution:
The reason is: the lack of perl-Digest-MD5 package can be solved by installing perl-Digest-MD5. [root@localhost ~]# yum -y ×××tall perl-Digest-MD5
The data validation tool of pt-table-checksum is briefly introduced here. If you have any questions, please leave a message to exchange and study together.