pt工具主从一致性检查并修复以及版本3.0.4的版本缺点
当我们在进行数据库的运维工作时，很多时候会出现主从数据不一致的故障，尤其是当我们的binlog格式没有选择row模式，当主库执行一些类似于replace select或者时间函数等不确定的随机函数时，会出现从库数据和主库数据不一样。复制线程同步的时候就会报错，运营人员抽取数据就不会准确，尤其是对数据的一致性和安全性较高的金融公司。这个时候我们就要借助percona公司的pt工具来进行处理，pt-table-checksum和pt-table-sync分别检验master-slave的数据不一致并修复，避免了人工分析并筛选binlog日志进行修复的繁琐。但是对于pt工具，版本之间的差异还是比较大，尤其是pt工具的3.0.4版本并不能很好的检测出来，故而分享这个坑给诸位一线人员。
首先我们要熟悉pt工具的运行机制?
pt-table-checksum针对的binlog_format=statement的格式，根据pt-table-checksum的原理，它在执行的时候，没有将会话级别的binlog_format=statement设置成功，那我们只能手动将动态参数binlog_format设置为statement模式。只有在statement格式下才能进行，因为两边要计算CRC32，计算完后再把主上的master_crc、master_cnt更新到从库，最后在从库对比master和this相关列，也就是说从库不会去计算所谓的CRC32，它直接完整copy主库的checksums的所有内容。pt-table-checksum 3.0.4在执行时缺少SET@@binlog_format=\’STATEMENT\’，建议不要使用。
1、本次分享的Linux版本、pt工具版本、数据库实例、binlog_format的值如下
1 [root@172-16-3-190 we_ops_admin]# cat /etc/redhat-release
2 CentOS release 6.8 (Final)
3 [root@172-16-3-190 we_ops_admin]# /opt/app/mysql_3309/bin/mysqladmin --version
4 /opt/app/mysql_3309/bin/mysqladmin Ver 8.42 Distrib 5.6.20-68.0, for Linux on x86_64
5 [root@172-16-3-190 we_ops_admin]# pt-table-checksum --version
6 pt-table-checksum 3.0.4
7
8 master1:172.16.3.190 basedir：/opt/app/mysql_3309/ datadir:/opt/app/mysql_3309/data port：3309
9 slave1:172.16.3.189 basedir：/opt/app/mysql_3309/ datadir:/opt/app/mysql_3309/data port：3309
10 master&slave：binlog_format=mixed
2、构造主从的数据差异，人为造成主从数据 不一致。这个过程就不概述了，数据模拟的过程大家都会操作。
1 ----测试表aa结构
2 CREATE TABLE `aa` (
3 `aa` varchar(1) DEFAULT \'\',
4 `bb` varchar(1) DEFAULT NULL,
5 `id` int(11) NOT NULL,
6 PRIMARY KEY (`id`)
7 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
8
9 ----master上表数据
10 mysql> select * from aa;
11 +------+------+----+
12 | aa | bb | id |
13 +------+------+----+
14 | 1 | 1 | 1 |
15 | 2 | 2 | 2 |
16 | 5 | 2 | 5 |
17 +------+------+----+
18 3 rows in set (0.00 sec)
19
20 ----slave上表数据
21 mysql> select * from aa;
22 +------+------+----+
23 | aa | bb | id |
24 +------+------+----+
25 | 2 | 2 | 2 |
26 | 4 | 4 | 4 |
27 | 5 | 5 | 5 |
28 +------+------+----+
29 3 rows in set (0.00 sec)
3、利用pt工具检测差异
1）创建一个用户，可以访问master和slave，master上执行如下的创建用户命令。构造master-slave的差异环境，slave同步master数据后，人为修改slave数据使得不一致。
1 grant all privileges on *.* to \'checksums\'@\'172.16.%.%\' identified by \'checksums\' 2 Query OK, 0 rows affected (0.00 sec)
2）pt-table-checksum检测差异，并写入差异到checksums表中，master上执行如下命令。
1 [root@172-16-3-190 we_ops_admin]# /usr/bin/pt-table-checksum --create-replicate-table --replicate=ceshi.checksums --nocheck-replication-filters --nocheck-binlog-format --recursion-method=processlist --databases=ceshi --user=checksums --password=checksums -h172.16.3.190 --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 2 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 3 01-30T10:26:44 0 0 3 1 0 0.042 ceshi.aa
DIFFS=0表示没有差异数据。实际上主从数据不一致，我们已经加入了参数–nocheck-binlog-format，这里却没有检测出来。为什么没有检测出来呢？根据pt的执行机制，那到底是那一步出现问题了呢，有一种很挫的方法，仅仅是为了看差异结果(生产环境勿用)，执行pt-table-checksum前，在主上 set global binlog_format=\’STATEMENT\’。
1 master上执行
2 mysql> set @@global.binlog_format=statement;
3 Query OK, 0 rows affected (0.00 sec)
4
5 slave上执行
6 mysql> set @@global.binlog_format=statement;
7 Query OK, 0 rows affected (0.00 sec)
master上再次执行，发现DIFFS的值终于为1，表示已经检测到master-slave数据的不一致了，这个时候可以表明pt 3.0.4版本在自动设置binlog_format格式为statement模式时没有设置成功。
1 [root@172-16-3-190 we_ops_admin]# /usr/bin/pt-table-checksum --create-replicate-table --replicate=ceshi.checksums --nocheck-replication-filters --nocheck-binlog-format --recursion-method=processlist --databases=ceshi --user=checksums --password=checksums -h172.16.3.190 --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 2 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 3 01-30T11:02:15 0 1 3 1 0 0.026 ceshi.aa
4、当我们检测出数据不一致了，并将该信息存储到checksums表中了，我们就可以利用pt-table-sync修复master-slave数据不一致，这个修复的第二步骤经过验证可以master和slave进行修复命令的执行，均能达到修复的需求。
1）master上执行，–print打印出修复的sql语句。参数–sync-to-master参数在master上执行必须有，否则打印不出差异sql。
[root@172-16-3-190 we_ops_admin]# pt-table-sync --sync-to-master --replicate=ceshi.checksums -h172.16.3.190 --user=checksums --password=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 h=172.16.3.189,u=checksums,p=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 --print DELETE FROM `ceshi`.`aa` WHERE `id`=\'4\' LIMIT 1 /*percona-toolkit src_db:ceshi src_tbl:aa src_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.190,p=...,u=checksums dst_db:ceshi dst_tbl:aa dst_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.189,p=...,u=checksums lock:1 transaction:1 changing_src:ceshi.checksums replicate:ceshi.checksums bidirectional:0 pid:13528 user:root host:172-16-3-190*/; REPLACE INTO `ceshi`.`aa`(`aa`, `bb`, `id`) VALUES (\'1\', \'1\', \'1\') /*percona-toolkit src_db:ceshi src_tbl:aa src_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.190,p=...,u=checksums dst_db:ceshi dst_tbl:aa dst_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.189,p=...,u=checksums lock:1 transaction:1 changing_src:ceshi.checksums replicate:ceshi.checksums bidirectional:0 pid:13528 user:root host:172-16-3-190*/; REPLACE INTO `ceshi`.`aa`(`aa`, `bb`, `id`) VALUES (\'5\', \'2\', \'5\') /*percona-toolkit src_db:ceshi src_tbl:aa src_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.190,p=...,u=checksums dst_db:ceshi dst_tbl:aa dst_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.189,p=...,u=checksums lock:1 transaction:1 changing_src:ceshi.checksums replicate:ceshi.checksums bidirectional:0 pid:13528 user:root host:172-16-3-190*/;
我们再次再slave上构造差异并执行修复命令
1 ---slave上执行
2 mysql> update aa set id = 4 where aa = 5;
3 Query OK, 1 row affected (0.00 sec)
4 Rows matched: 1 Changed: 1 Warnings: 0
5
6 ----master上执行检测
7 [root@172-16-3-190 we_ops_admin]# /usr/bin/pt-table-checksum --create-replicate-table --replicate=ceshi.checksums --nocheck-replication-filters --nocheck-binlog-format --recursion-method=processlist --databases=ceshi --user=checksums --password=checksums -h172.16.3.190 --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309
8
9 # A software update is available:
10 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
11 01-30T15:12:27 0 1 3 1 0 0.025 ceshi.aa
12
13 ----slave上执行数据修复
14 [root@172-16-3-189 we_ops_admin]# pt-table-sync --sync-to-master --replicate=ceshi.checksums -h172.16.3.190 --user=checksums --password=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 h=172.16.3.189,u=checksums,p=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 --print
15 DELETE FROM `ceshi`.`aa` WHERE `id`=\'4\' LIMIT 1 /*percona-toolkit src_db:ceshi src_tbl:aa src_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.190,p=...,u=checksums dst_db:ceshi dst_tbl:aa dst_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.189,p=...,u=checksums lock:1 transaction:1 changing_src:ceshi.checksums replicate:ceshi.checksums bidirectional:0 pid:23321 user:root host:172-16-3-189*/;
16 REPLACE INTO `ceshi`.`aa`(`aa`, `bb`, `id`) VALUES (\'5\', \'2\', \'5\') /*percona-toolkit src_db:ceshi src_tbl:aa src_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.190,p=...,u=checksums dst_db:ceshi dst_tbl:aa dst_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.189,p=...,u=checksums lock:1 transaction:1 changing_src:ceshi.checksums replicate:ceshi.checksums bidirectional:0 pid:23321 user:root host:172-16-3-189*/;
17
18 [root@172-16-3-189 we_ops_admin]# pt-table-sync --sync-to-master --replicate=ceshi.checksums -h172.16.3.190 --user=checksums --password=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 h=172.16.3.189,u=checksums,p=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 --execute
19
20 [root@172-16-3-189 we_ops_admin]# 3309.sh
21 Warning: Using a password on the command line interface can be insecure.
22 Welcome to the MySQL monitor. Commands end with ; or \g.
23 Your MySQL connection id is 223
24 Server version: 5.6.20-68.0-log Percona Server (GPL), Release 68.0, Revision 656
25
26 Copyright (c) 2009-2014 Percona LLC and/or its affiliates
27 Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
28
29 Oracle is a registered trademark of Oracle Corporation and/or its
30 affiliates. Other names may be trademarks of their respective
31 owners.
32
33 Type \'help;\' or \'\h\' for help. Type \'\c\' to clear the current input statement.
34
35 mysql> select * from aa;
36 ERROR 1046 (3D000): No database selected
37 mysql> use ceshi;
38 Reading table information for completion of table and column names
39 You can turn off this feature to get a quicker startup with -A
40
41 Database changed
42 mysql> select * from aa;
43 +------+------+----+
44 | aa | bb | id |
45 +------+------+----+
46 | 1 | 1 | 1 |
47 | 2 | 2 | 2 |
48 | 5 | 2 | 5 |
49 +------+------+----+
50 3 rows in set (0.00 sec)
2）–execute执行修复语句
1 [root@172-16-3-190 we_ops_admin]# pt-table-sync --sync-to-master --replicate=ceshi.checksums -h172.16.3.190 --user=checksums --password=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 h=172.16.3.189,u=checksums,p=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 --execute
3）验证master和slave数据不一致性是否修复，经检验数据一致
1 ----master上表aa数据
2 mysql> select * from ceshi.aa;
3 +------+------+----+
4 | aa | bb | id |
5 +------+------+----+
6 | 1 | 1 | 1 |
7 | 2 | 2 | 2 |
8 | 5 | 2 | 5 |
9 +------+------+----+
10 3 rows in set (0.00 sec)
11
12 ----slave上表aa数据
13 mysql> select * from ceshi.aa;
14 +------+------+----+
15 | aa | bb | id |
16 +------+------+----+
17 | 1 | 1 | 1 |
18 | 2 | 2 | 2 |
19 | 5 | 2 | 5 |
20 +------+------+----+
21 3 rows in set (0.00 sec)
再次利用工具运行，检测master-slave数据一致性
1 [root@172-16-3-190 we_ops_admin]# /usr/bin/pt-table-checksum --create-replicate-table --replicate=ceshi.checksums --nocheck-replication-filters --nocheck-binlog-format --recursion-method=processlist --databases=ceshi --user=checksums --password=checksums -h172.16.3.190 --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 2 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 3 01-30T14:50:43 0 0 3 1 0 0.038 ceshi.aa
5、总结
1、由上面的模拟结果和pt工具差异的检测过程，我们可以得出结论pt-table-checksum 3.0.4存在bug，binlog_format格式非statement格式检测不出来差异。
2、对于数据一致性要求较高的业务，尽量从源头上避免随机函数的应用。
3、设置binlog_format的模式为row模式，能有效避免随机函数带来主从数据不一致的故障，但是这样会产生大量的binlog日志，占用磁盘空间。
版权声明：本文为liyingxiao原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。