欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-11-29 19:50:40
安装环境: os:linux 6.3 64bit mysql: 5.7.21 percona-toolkit:2.2.18 安装过程: #一些依赖包 yum install perl perl-db...

安装环境:

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来同步主从数据了。