利用percona-toolkit检查并修复mysql集群的数据一致性

千家信息网最后更新 2024年11月12日利用percona-toolkit检查并修复mysql集群的数据一致性percona-toolkit是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的mysql任务和系统任务，这些任务包括：
· 检查master和slave数据的一致性
· 有效地对记录进行归档
· 查找重复的索引
· 对服务器信息进行汇总
· 分析来自日志和tcpdump的查询
· 当系统出问题的时候收集重要的系统信息

在这里只介绍一下mysql主从复制+MHA架构下最常用到数据一致性检测及修复，当master宕机后，其中一个slaver被推举为主节点，宕机节点重新加入集群后，需根据bin log同步数据，为了保险起见还是要检查一遍集群的数据一致性，percona-toolkit就派上用场了

一、pt-table-checksum检查主从库数据的一致性
pt-table-checksum在MASTER上校验指定库、表，将结果存在一个库表里，复制进程将检验sql传递到slave上再执行一次。通过比较M/S的检验值确定数据是否一致。利用主从复制做检验，不需要在检验期间对主从数据库同时锁表，可以控制校验的数据和速度，不影响到正常服务。
安装：
#依赖包
yum -y perl-DBI perl-DBD-MySQL perl-TermReadKey
#percona-toolkit包
wget http://www.percona.com/downloads/percona-toolkit/LATEST/percona-toolkit-2.2.4.tar.gz
tar xzvf percona-toolkit-2.2.4.tar.gz ; cd percona-toolkit-2.2.4 ; perl Makefile.pl && make && make install
使用方法：
pt-table-checksum [OPTIONS] [DSN]
pt-table-checksum：在主上通过执行校验的查询对复制的一致性进行检查，对比主从的校验值，从而产生结果。DSN指向的是主的地址，该工具的退出状态不为零，如果发现有任何差别，或者如果出现任何警告或错误，更多信息请见官网。
不指定任何参数，会直接对本地的所有数据库的表进行检查。
pt-table-checksum -S /tmp/mysqld.sock u=root,p=123456
环境：
#主库：
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
+----+------+
5 rows in set (0.00 sec)


#从库：
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+----+------+
4 rows in set (0.00 sec)
注意：
1、 根据测试，需要一个技能登录主库，也能登录从库，而且还能同步数据库的账号；
2、 只能指定一个host，必须为主库的IP；
3、 在检查时会向表加S锁；
4、 运行之前需要从库的同步IO和SQL进程是YES状态。
可以用该语句授权用户，这里为了方便直接用root用户了
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'x.x.x.x' IDENTIFIED BY 'xxxx';
执行检测（MASTER上）：
pt-table-checksum --nocheck-replication-filters --replicate=test.checksum --databases=test h=192.168.68.235,u=root,p=123 --empty-replicate-table --create-replicate-table
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
09-18T12:03:16 0 1 5 1 0 0.018 test.t1
参数说明：
TS ：完成检查的时间。
ERRORS ：检查时候发生错误和警告的数量。
DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS ：表的行数。
CHUNKS ：被划分到表中的块的数目。
SKIPPED ：由于错误或警告或过大，则跳过块的数目。
TIME ：执行的时间。
TABLE ：被检查的表名。
参数意义：
--nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。
--no-check-binlog-format : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。
--replicate-check-only :只显示不同步的信息。
--replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。
--databases= ：指定需要被检查的数据库，多个则用逗号隔开。
--tables= ：指定需要被检查的表，多个用逗号隔开
h=127.0.0.1 ：Master的地址
u=root ：用户名
p=123456 ：密码
P=3306 ：端口
更多的参数请见官网，上面指出来的是常用的，对该场景够用的参数。
通过DIFFS是1可以看出主从的表数据不一致。通过查看从库上的test.checksum表可以看到主从库的检验信息。
mysql> select * from checksum\G;
*************************** 1. row ***************************
db: test
tbl: t1
chunk: 1
chunk_time: 0.001604
chunk_index: NULL
lower_boundary: NULL
upper_boundary: NULL
this_crc: 13fa7d9d #从的校验值
this_cnt: 4 #从的行数
master_crc: aa7a56c3 #主的校验值
master_cnt: 5 #主的行数
ts: 2013-09-18 12:03:16
1 row in set (0.00 sec)
通过上面的 this_crc <> master_crc 更能清楚的看出他们的不一致了，通过chunk知道是这个张表的哪个块上的记录出现不一致。要是主的binlog模式是Row 则会报错：
Replica db2 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.
从错误信息得出，要是不改binlog模式的话，则在执行上面的命令时候要指定：--no-check-binlog-format，即：
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate-check-only --replicate=test.checksum --databases=test --tables=t1 h=127.0.0.1,u=root,p=123,P=3306
指定--replicate-check-only参数会在前一次pt-table-checksum检验的数据之上比较（不会再执行计算），显示出数据不一致的SLAVE主机名：
[root@host125 ~]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate-check-only --replicate=test.checksum --databases=test --tables=t1 h=127.0.0.1,u=root,p=123456,P=3306
[root@host125 ~]# pt-table-checksum --nocheck-replication-filters --replicate=test.checksum --databases=test h=192.168.68.235,u=root,p=123 --empty-replicate-table --create-replicate-table --replicate-check-only
Differences on host122
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
test.t1 1 -1 1
数据不一致的SLAVE和表都找出来了，下面就用pt-table-sync来修补数据。
二、pt-table-sync修复从库不一致的数据
使用方法：
pt-table-sync [OPTIONS] DSN [DSN]
pt-table-sync: 高效的同步MySQL表之间的数据，他可以做单向和双向同步的表数据。他可以同步单个表，也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。
继续上面的复制环境，主和从的t1表数据不一致，需要修复，
执行：
[root@host125 ~]# pt-table-sync --print --replicate=test.checksum h=192.168.68.235,u=root,p=123,P=3306 h=192.168.68.232,u=root,p=123,P=3306
#先MASTER的IP，再SLAVE的IP
REPLACE INTO `test`.`t1`(`id`, `name`) VALUES ('5', 'ee')
/*percona-toolkit src_db:test src_tbl:t1 src_dsn:P=3306,h=192.168.68.235,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:P=3306,h=192.168.68.232,p=...,u=root lock:1 transaction:1 changing_src:test.checksum replicate:test.checksum bidirectional:0 pid:24763 user:root host:host125*/;
参数的意义：
--replicate= ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。
--databases= : 指定执行同步的数据库，多个用逗号隔开。
--tables= ：指定执行同步的表，多个用逗号隔开。
--sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。
h=127.0.0.1 ：服务器地址，命令里有2个ip，第一次出现的是M的地址，第2次是Slave的地址。
u=root ：帐号。
p=123456 ：密码。
--print ：打印，但不执行命令。
--execute ：执行命令。
更多的参数请见官网，上面指出来的是常用的，对该场景够用的参数。
和上面的命令一样效果的命令：
[root@host125 ~]# pt-table-sync --print --sync-to-master h=192.168.68.232,u=root,p=123,P=3306 --databases test --tables t1
#用一个IP （SLAVE）就可以了。
REPLACE INTO `test`.`t1`(`id`, `name`) VALUES ('5', 'ee') /*percona-toolkit src_db:test src_tbl:t1 src_dsn:P=3306,h=192.168.68.235,p=...,u=root dst_db:test dst_tbl:t1 dst_dsn:P=3306,h=192.168.68.232,p=...,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:24798 user:root host:host125*/;
还可以让它自己执行修复数据的SQL语句，但是这样就没有输出了：
[root@host125 ~]# pt-table-sync --execute --sync-to-master h=192.168.68.232,u=root,p=123,P=3306 --databases test --tables t1

数据已经修复完成了:
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
+----+------+
5 rows in set (0.00 sec)
建议还是用--print 打印出来的好，这样就可以知道那些数据有问题，可以人为的干预下。不然直接执行了，出现问题之后更不好处理。总之还是在处理之前做好数据的备份工作。
注意：要是表中没有唯一索引或则主键则会报错：
Can't make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.
补充：
要是从库有的数据，而主库没有，那这个数据怎么处理？会给出删除SLAVE多余数据，和修复SLAVE缺失数据的SQL语句。
如果在shell窗口不想显示输入密码则可以添加：--ask-pass 参数，如：
[root@host125 ~]# pt-table-sync --print --ask-pass --sync-to-master h=192.168.68.232,u=root,P=3306 --databases test --tables t1
Enter password for 192.168.68.232:
如果使用--ask-pass，报错：
Cannot read response; is Term::ReadKey installed? Can't locate Term/ReadKey.pm in @INC
安装Term/ReadKey.pm模块：
[root@host125 ~]# perl -MCPAN -e "shell"
cpan[1]> install Term::ReadKey
总结：
该工具执行检查表动作，检查连接的帐号需要有很高的权限，在一般权限上需要加SELECT, PROCESS, SUPER, REPLICATION SLAVE等权限。pt-table-checksm 配合pt-table-sync使用，在执行pt-table-sync数据同步之前，一定要执行pt-table-checksm命令检查。