阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

pt-table-checksum工具主从一致性检查修复

231次阅读
没有评论

共计 9285 个字符,预计需要花费 24 分钟才能阅读完成。

当我们在进行数据库的运维工作时,很多时候会出现主从数据不一致的故障,尤其是当我们的 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 的值如下

[root@172-16-3-190 we_ops_admin]# cat /etc/RedHat-release
CentOS release 6.8 (Final)
[root@172-16-3-190 we_ops_admin]# /opt/app/mysql_3309/bin/mysqladmin –version
/opt/app/mysql_3309/bin/mysqladmin  Ver 8.42 Distrib 5.6.20-68.0, for Linux on x86_64
[root@172-16-3-190 we_ops_admin]# pt-table-checksum –version
pt-table-checksum 3.0.4

master1:172.16.3.190 basedir:/opt/app/mysql_3309/  datadir:/opt/app/mysql_3309/data port:3309
slave1:172.16.3.189 basedir:/opt/app/mysql_3309/  datadir:/opt/app/mysql_3309/data port:3309
master&slave:binlog_format=mixed

2、构造主从的数据差异,人为造成主从数据 不一致。这个过程就不概述了,数据模拟的过程大家都会操作。

—- 测试表 aa 结构
CREATE TABLE `aa` (
  `aa` varchar(1) DEFAULT ”,
  `bb` varchar(1) DEFAULT NULL,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

—-master 上表数据
mysql> select * from aa;
+——+——+—-+
| aa  | bb  | id |
+——+——+—-+
| 1    | 1    |  1 |
| 2    | 2    |  2 |
| 5    | 2    |  5 |
+——+——+—-+
3 rows in set (0.00 sec)

—-slave 上表数据
mysql> select * from aa;
+——+——+—-+
| aa  | bb  | id |
+——+——+—-+
| 2    | 2    |  2 |
| 4    | 4    |  4 |
| 5    | 5    |  5 |
+——+——+—-+
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’。

master 上执行
mysql> set @@global.binlog_format=statement;
Query OK, 0 rows affected (0.00 sec)

slave 上执行
mysql> set @@global.binlog_format=statement;
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 上构造差异并执行修复命令

—slave 上执行
mysql> update aa set id = 4 where aa = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

—-master 上执行检测
[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

# A software update is available:
            TS ERRORS  DIFFS    ROWS  CHUNKS SKIPPED    TIME TABLE
01-30T15:12:27      0      1        3      1      0  0.025 ceshi.aa

—-slave 上执行数据修复
[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 
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*/;
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*/;

[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

[root@172-16-3-189 we_ops_admin]# 3309.sh
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 223
Server version: 5.6.20-68.0-log Percona Server (GPL), Release 68.0, Revision 656

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> select * from aa;
ERROR 1046 (3D000): No database selected
mysql> use ceshi;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from aa;
+——+——+—-+
| aa  | bb  | id |
+——+——+—-+
| 1    | 1    |  1 |
| 2    | 2    |  2 |
| 5    | 2    |  5 |
+——+——+—-+
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 数据不一致性是否修复,经检验数据一致

—-master 上表 aa 数据
mysql> select * from ceshi.aa;
+——+——+—-+
| aa  | bb  | id |
+——+——+—-+
| 1    | 1    |  1 |
| 2    | 2    |  2 |
| 5    | 2    |  5 |
+——+——+—-+
3 rows in set (0.00 sec)

—-slave 上表 aa 数据
mysql> select * from ceshi.aa;
+——+——+—-+
| aa  | bb  | id |
+——+——+—-+
| 1    | 1    |  1 |
| 2    | 2    |  2 |
| 5    | 2    |  5 |
+——+——+—-+
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 日志,占用磁盘空间。

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计9285字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中