pt-table-checksum verification and pt-table-sync repair data

1: Download tool package

Log in to the website to download the corresponding tool package

2: Installation

(1) yum installation:

sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum list | grep percona-toolkit

The result set shows the following to prove that the installation is successful:

percona-toolkit.noarch 3.0.0-rc percona-release-noarch

Then install

sudo yum install percona-toolkit

(2) Unzip the installation package:

[root@localhost home]# cd toolkit/[root@localhost toolkit]# ls
percona-toolkit-2.2.19.tar.gz[root@localhost toolkit]# tar -xvf percona-toolkit.tar.gz

So far, percona's toolKit toolkit has been installed. Let's mainly test pt-table-checksum verification and pt-table-sync. The former is mainly used for data verification to verify whether the master and slave are consistent. The latter is mainly used To repair data, the two can generally be used together to repair data inconsistencies.

pt-table-checksum is a tool provided by percona to verify the consistency of master-slave data. We point to the master library and judge the result set of the slave library and feedback it to us. When inconsistencies are captured When asked, a non-zero result will be returned. However, it can only find inconsistencies in the data. If you want to repair data problems, you must use pt-table-sync.

pt-table-checksum is based on the statement copy format when running. If we are in ROW format, we need to add the --no-check-binlog-format parameter when executing.

3: The practice of pt-table-checksum, we can use it directly after installation. I am using the second method

[root@localhost bin]# /home/toolkit/percona-toolkit-2.2.19/bin/pt-table-checksum --nocheck-replication-filters --replicate=rep_test.checksums --no-check-binlog-format h=172.16.16.35,u=root,p=123456,P=3306TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE06-07T10:55:49 0 0 1 1 0 1.331 MAXIANGQIAN.MAXIANGQIAN06-07T10:55:54 0 0 1 1 0 5.524 maxiangqian.test06-07T10:55:56 0 0 4 1 0 1.208 maxiangqian.test0106-07T10:55:57 0 0 0 1 0 1.164 mysql.columns_priv06-07T10:55:58 0 0 5 1 0 1.216 mysql.db06-07T10:55:59 0 0 2 1 0 1.176 mysql.engine_cost06-07T10:56:00 0 0 0 1 0 1.178 mysql.event06-07T10:56:01 0 0 0 1 0 1.160 mysql.func06-07T10:56:03 0 0 1 1 0 1.154 mysql.gtid_executed06-07T10:56:08 0 0 40 4 0 5.015 mysql.help_category06-07T10:56:13 0 0 679 5 0 5.806 mysql.help_keyword06-07T10:56:18 0 0 1334 4 0 4.572 mysql.help_relation06-07T10:56:19 0 0 635 1 0 1.169 mysql.help_topic06-07T10:56:20 0 0 0 1 0 1.163 mysql.ndb_binlog_index06-07T10:56:22 0 0 0 1 0 1.157 mysql.plugin06-07T10:56:23 0 1 48 1 0 1.236 mysql.proc06-07T10:56:24 0 0 0 1 0 1.197 mysql.procs_priv06-07T10:56:25 0 0 1 1 0 1.235 mysql.proxies_priv06-07T10:56:26 0 0 6 1 0 1.188 mysql.server_cost06-07T10:56:28 0 0 0 1 0 1.154 mysql.servers06-07T10:56:34 0 0 1 1 0 6.184 mysql.tables_priv06-07T10:56:35 0 0 0 1 0 1.198 mysql.time_zone06-07T10:56:36 0 0 0 1 0 1.121 mysql.time_zone_leap_second06-07T10:56:37 0 0 0 1 0 1.148 mysql.time_zone_name06-07T10:56:38 0 0 0 1 0 1.147 mysql.time_zone_transition06-07T10:56:40 0 0 0 1 0 1.138 mysql.time_zone_transition_type06-07T10:56:41 0 0 8 1 0 1.336 mysql.user06-07T10:56:42 0 0 0 1 0 1.164 open_api.ma06-07T10:57:01 0 0 1000000 13 0 18.707 sbtest.sbtest06-07T10:57:02 0 0 6 1 0 1.263 sys.sys_config

We can also check a certain library separately to narrow the scope of the check:

[root@localhost bin]# /home/toolkit/percona-toolkit-2.2.19/bin/pt-table-checksum --nocheck-replication-filters --replicate=rep_test.checksums --no-check-binlog-format --databases=maxiangqian h=172.16.16.35,u=root,p=123456,P=3306

The results obtained are as above, take a look at the description :

TS: Time to complete the check. ERRORS: The number of errors and warnings that occurred during the check. DIFFS: 0 means consistent, 1 means inconsistent. When --no-replicate-check is specified, it will always be 0. When --replicate-check-only is specified, different information will be displayed. ROWS: The number of rows in the table. CHUNKS: The number of blocks divided into the table. SKIPPED: The number of blocks to skip due to errors or warnings or because they are too large. TIME: execution time. TABLE: The name of the table being checked.

Then take a look at this main parameter:

--nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。--no-check-binlog-format : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。--replicate-check-only :只显示不同步的信息。 --replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。 --databases= ：指定需要被检查的数据库，多个则用逗号隔开。--tables= ：指定需要被检查的表，多个用逗号隔开 h=127.0.0.1 ：Master的地址
u=root ：用户名
p=123456 ：密码
P=3306 ：端口

By specifying the --replicate=rep_test.checksums parameter, a database will be automatically generated. The check information All are written to the checksums table.

Enter the corresponding library of SLAVE to view the information of the checksums table. You can view inconsistent information through the following statement:

mysql> select * from checksums where this_cnt<>master_cnt;

Through the above this_crc <> master_crc can more clearly see their inconsistencies. Through chunk, we can know which block of this table has inconsistent records.

4: Detect data inconsistencies and use pt-table-sync to repair

Let’s take a look at the previous section. We detected that there is an inconsistency through the following statement The data is inconsistent:

[root@localhost bin]# /home/toolkit/percona-toolkit-2.2.19/bin/pt-table-checksum --nocheck-replication-filters --replicate=rep_test.checksums --no-check-binlog-format --databases=maxiangqian h=172.16.16.35,u=root,p=123456,P=3306TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE06-07T11:13:18 0 0 1 1 0 1.220 MAXIANGQIAN.MAXIANGQIAN06-07T11:13:19 0 1 1 1 0 1.135 maxiangqian.test06-07T11:13:21 0 0 4 1 0 1.197 maxiangqian.test01

Restore inconsistent data:

[root@localhost bin]# clear[root@localhost bin]# ./pt-table-sync --execute h=172.16.16.35,P=3306,D=maxiangqian,t=test,u=root,p=123456 h=172.16.16.34,P=3306,u=root,p=123456 --no-check-slave --print[root@localhost bin]# /home/toolkit/percona-toolkit-2.2.19/bin/pt-table-checksum --nocheck-replication-filters --replicate=rep_test.checksums --no-check-binlog-format --databases=maxiangqian h=172.16.16.35,u=root,p=123456,P=3306TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE06-07T11:18:09 0 0 1 1 0 1.172 MAXIANGQIAN.MAXIANGQIAN06-07T11:18:10 0 0 1 1 0 1.144 maxiangqian.test06-07T11:18:11 0 0 4 1 0 1.282 maxiangqian.test01

itself, the test table is missing a row of data, After execution, the data of the subject and the copy will be consistent:

Next, let’s take a look at some use cases of pt-table-sync:

(1) Put host1 on Synchronize the data to host2, only synchronize the db.tbl table

pt-table-sync --execute h=host1,D=db,t=tbl h=host2[root@localhost bin]# ./pt-table-sync --execute h=172.16.16.35,P=3306,D=maxiangqian,t=test,u=root,p=123456 h=172.16.16.34,P=3306,u=root,p=123456 --no-check-slave --print

(2) Synchronize all data on host1 to host2 and host3

pt-table-sync --execute host1 host2 host3[root@localhost bin]# ./pt-table-sync --execute h=172.16.16.35,P=3306,D=maxiangqian,t=test,u=root,p=123456 h=172.16.16.34,P=3306,u=root,p=123456 h=172.16.16.35,P=3307,u=root,p=123456 --no-check-slave --print

(3) Ensure that all data on slave1 is synchronized with the main database

pt-table-sync --execute --sync-to-master slave1[root@localhost bin]# ./pt-table-sync --execute --sync-to-master h=172.16.16.34,P=3306,u=root,p=123456 --no-check-slave --print

This requirement checks The table must have a primary key or unique index. We can also specify the single table of a single database as follows

[root@localhost bin]# ./pt-table-sync --execute --sync-to-master h=172.16.16.34,P=3306,D=maxiangqian,t=test01,u=root,p=123456 --no-check-slave --print

(4) Restore all slave libraries according to the rep_test.checksums generated by the check

pt-table-sync --execute --replicate test.checksum master1

That is the following example:

[root@localhost bin]# ./pt-table-sync --execute --replicate rep_test.checksums h=172.16.16.35,P=3306,u=root,p=123456

(5) Only repair the data of slave1

/pt-table-sync --execute --replicate rep_test.checksums --sync-to-master slave1

(6) Dual master mode master2 repairs its own data:

pt-table-sync --execute --sync-to-master h=master2,D=db,t=tbl

Due to dual master mode There are many problems. A modification may be transmitted to another database, so we must not perform the following operations in dual-master mode:

pt-table-sync --execute h=master1,D=db,t=tbl master2

The above is the detailed content of pt-table-checksum verification and pt-table-sync repair data. For more information, please follow other related articles on the PHP Chinese website!