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

pt工具主从一致性检查并修复以及版本3.0.4的版本缺点

程序员文章站 2022-04-19 20:38:15
pt-table-checksum和pt-table-sync分别检验master-slave的数据不一致并修复。 1、本次测试环境 2、 3、开始检测差异 1)创建一个用户,可以访问master和slave,master上执行如下的创建用户命令 构造master-slave的差异环境,slave同 ......

   pt-table-checksum和pt-table-sync分别检验master-slave的数据不一致并修复。

1、本次测试环境

 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、开始检测差异
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
pt-table-checksum 3.0.4的bug,功能缺乏,binlog_format格式非statement格式检测不出来差异
  DIFFS=0表示没有差异数据。实际上主从数据不一致,我们已经加入了参数--nocheck-binlog-format,这里却没有检测出来。为什么没有检测出来呢?
 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',建议不要使用。
  有一种很挫的方法,仅仅是为了看差异结果(生产环境勿用),执行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数据的不一致了

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、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