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