checksum mysql_MySQL5.7使用pt-table-checksum检查主从数据一致性的测试讲解

安装环境：

OS：linux 6.3 64bit

mysql: 5.7.21

Percona-toolkit：2.2.18

安装过程：

#一些依赖包

yum install perl perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Time-HiRes -y

#下载安装percona-toolkit

tar -xvf percona-toolkit_2.2.18.tar.gz

cd percona-toolkit-2.2.18/

perl Makefile.PL

make

make test

make install

cp /usr/local/bin/pt* /bin/

测试步骤：

mysql为一主一从架构，在主库上安装toolkit，测试主从库是否一致。

1.建立测试帐号以及percona库

master server:

mysql> grant SELECT,LOCK TABLES,PROCESS,SUPER on *.* to repl@'172.17.61.%';

Query OK, 0 rows affected (0.14 sec)

mysql> grant select,create,drop,insert,delete,update,alter on percona.* to repl@'172.17.61.%';

Query OK, 0 rows affected (0.07 sec)

mysql> insert into l5m.t2(c1,c2,c3) values(2,'eee',33);

Query OK, 1 row affected (0.12 sec)

mysql> select * from l5m.t2;

+------+------+------+----+

| c1 | c2 | c3 | c4 |

+------+------+------+----+

| 1 | abc | 22 | 33 |

| 2 | eee | 33 | 34 |

+------+------+------+----+

2 rows in set (0.00 sec)

mysql> CREATE DATABASE IF NOT EXISTS percona;

Query OK, 1 row affected (0.17 sec)

2.准备测试数据，手动在slave用root帐户输入异步数据

master server:

mysql> select * from l5m.t2;

+------+------+------+----+

| c1 | c2 | c3 | c4 |

+------+------+------+----+

| 1 | abc | 22 | 33 |

+------+------+------+----+

1 row in set (0.00 sec)

slave server:

mysql> insert into l5m.t2(c1,c2,c3) values(2,'eee',33);

Query OK, 1 row affected (0.12 sec)

mysql> select * from l5m.t2;

+------+------+------+----+

| c1 | c2 | c3 | c4 |

+------+------+------+----+

| 1 | abc | 22 | 33 |

| 2 | eee | 33 | 34 |

+------+------+------+----+

2 rows in set (0.00 sec)

现在主从不一致了，从库多了一条数据

3.运行pt-table-checksum进行检查

[root@qht131 home]# pt-table-checksum h=172.17.61.131,u=repl,p='repl',P=3306 --databases=l5m --tables=t2

Replica qht132 has binlog_format ROW which could cause pt-table-checksum to break replication. Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation. If you understand the risks, specify --no-check-binlog-format to disable this check.

提示需要指定no-check-binlog-format参数

[root@qht131 home]# pt-table-checksum h=172.17.61.131,u=repl,p='repl',P=3306 --databases=l5m --tables=t2 --no-check-binlog-format

# A software update is available:

# * The current version for Percona::Toolkit is 3.0.5

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

05-02T22:07:44 0 1 1 1 0 0.769 l5m.t2

pt-table-checksum第一次运行会在当前的库建立checksums表

来看一下这个表的数据：

master server:

mysql> select * from checksums\G

*************************** 1. row ***************************

db: l5m

tbl: t2

chunk: 1

chunk_time: 0.029503

chunk_index: NULL

lower_boundary: NULL

upper_boundary: NULL

this_crc: 5bbc5664

this_cnt: 1

master_crc: 5bbc5664

master_cnt: 1

ts: 2018-05-02 22:07:43

1 row in set (0.00 sec)

由于运行pt-table-checksum就是master server，所以在master的checksums表里显示的数据是一致的。

slave server:

mysql> select * from checksums\G

*************************** 1. row ***************************

db: l5m

tbl: t2

chunk: 1

chunk_time: 0.029503

chunk_index: NULL

lower_boundary: NULL

upper_boundary: NULL

this_crc: 2418ebee

this_cnt: 2

master_crc: 5bbc5664

master_cnt: 1

ts: 2018-05-02 22:07:43

1 row in set (0.00 sec)

这里显示了主从不一致的数据行数。

发现了不一致，需要借助另外一个工具pt-table-sync来同步主从数据了。