Pt-table-checksum check and pt-table-sync repair data, checksum Check Tool

Pt-table-checksum check and pt-table-sync repair data, checksum Check Tool
1. Download the ToolkitLog on to the website and download the corresponding toolkit https://www.percona.com/downloads/percona-toolkit/LATEST/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.rpmyum list | grep percona-toolkit

The result set displays the following proof 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]# lspercona-toolkit-2.2.19.tar.gz[root@localhost toolkit]# tar -xvf percona-toolkit.tar.gz

So far, percona toolKit has been installed. Next we will mainly test the pt-table-checksum check and pt-table-sync. The former is mainly used for data validation to verify whether the master and slave nodes are consistent, the latter is mainly used to repair data, which can be used in combination to fix data inconsistency.

Pt-table-checksum is a tool provided by percona to verify master-slave data consistency. We direct the tool to the master database and report the result from the database to us, when inconsistency is captured, a non-zero result is returned. However, he can only find data inconsistencies. To fix data problems, he must use pt-table-sync. Pt-table-checksum is based on the statement replication format during running. If we use the ROW format, we need to add the -- no-check-binlog-format parameter during execution. 3: pt-table-checksum. We can use it directly after installation. This is 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 separately check a database to narrow down 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 result is as follows:

TS: The time when the check is completed. ERRORS: number of ERRORS and warnings during the check. DIFFS: 0 indicates consistency, and 1 indicates inconsistency. When -- no-replicate-check is specified, it is always 0. When -- replicate-check-only is specified, different information is displayed. ROWS: number of ROWS in the table. CHUNKS: Number of CHUNKS divided into tables. SKIPPED: Number of SKIPPED blocks due to errors or warnings. TIME: the execution TIME. TABLE: the name of the TABLE to be checked. Then let's take a look at the main parameters:

-- Nocheck-replication-filters: do not check the replication filter. We recommend that you enable it. You can use -- databases to specify the database to be checked. -- No-check-binlog-format: the copied binlog mode is not checked. If the binlog mode is ROW, an error is returned. -- Replicate-check-only: only information that is not synchronized is displayed. -- Replicate =: Write the checksum information to the specified table. We recommend that you directly write the information to the database to be checked. -- Databases =: Specifies the database to be checked. Multiple databases are separated by commas. -- Tables =: Specifies the table to be checked. Multiple tables are separated by commas (,). h = 127.0.0.1: Master Address u = root: username p = 123456: Password P = 3306: Port

By specifying the -- replicate = rep_test.checksums parameter, a database is automatically generated and the check information is written to the checksums table.

Go to the corresponding SLAVE database to view the checksums table information. You can view the inconsistent information through the following statement:

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

The above this_crc <> master_crc can clearly show their inconsistency, and the chunk can be used to know which part of the table contains inconsistent records.

4: Use pt-table-sync to fix data inconsistency detectionLet's take a look at the previous section. We found data inconsistency through the following statements:

[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

In itself, the test table contains a row of data, and the data of the subject and the copy will be consistent after execution:

Next, let's take a look at some use cases of pt-table-sync: (1) synchronize data on host1 to host2 and only synchronize db. tbl tables

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 master 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 requires that the table to be checked must have a primary key or a unique index. We can also specify a single table for 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 databases 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 slave1 data is repaired.

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

(6) Master 2 in dual-master mode:

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

Because there are many problems with the dual-master mode, one modification may be passed to another database, so we must never perform the following operations in the dual-master mode:

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