MySQL主从一致性的快速校验工具-pt

最近测试环境的MySQL出现了偶发主从同步失败的现象。主从同步失败的问题很快的得到了解决。但我对于测试环境的数据库主从数据是否完全一致产生了怀疑，有怀疑就得有验证，得找个法子验证一下主从数据是否一致。手工检查也可以做，太耗时间，由此便引入了我本次所要介绍的工具pt-table-checksum。

为什么要做主从一致性监测

• 1、主从复制是基于binlog的逻辑复制，难免出现复制数据不一致的风险
• 2、这个风险不但会引起用户数据访问前后不一致的风险
• 3、而且会导致后续复制出现1032、1062错误进而引起复制架构停滞的隐患
• 4、为了及时发现并解决这个问题
• 5、我们需要定期或不定期地开展主从复制数据一致性的校验和修复工作

pt工具监测

用到的命令：

1、pt-table-check       #监测主从一致

2、pt-table-sync         #修复主从一致

mysql主从一致性校验，基于pt工具进行。

#限制及问题

1、在检查阶段，超过1000行的数据，如果没有设定索引或者主键，则报错，该表会跳过检查。

2、在修复阶段，如果表没有设置主键或索引，则修复报错，可以手动进行修复。

3、监测是基于块进行的，如果mysql表的数据没有进行分块，那么当表过大时，会造成监测一段时间后发现没有问题会跳过改表。

4、当数据库两个数据不一致，但是checksum检测一致时，没有比对出不一致。

主库和从库账号一致，密码不一致发现了这种问题。

原因：对于修改密码的操作，chencksum的值并没有发生变化。

使用场景

然而在真正的生产环境上，这两个工具仍是有必定的局限性，准确的说应该是mysql这种异步复制的架构致使了工具在使用上的局限性，由于从库会慢于主库，因此在校验主库上的表与校验从库上的表时每每数据是不一致的，这个不致是因为从库的延迟而致使的，因此这两个工具最好运用在如下场景：

• a)、从服务器提高为主服务器时，在新的主服务器上线时须要与旧的主服务器进行数据一致性检查
• b)、数据迁移后，应该进行数据一致性检查
• c)、从库被误操做致使数据更新后，应该进行一致性检查
• d)、计划内的数据一致性检查

原理介绍

pt-table-checksum的工作原理很简单，通过在主库和从库对表进行一致性的checksum计算，如果主从库的某张表（结构或者数据）不一致，那么checksum结果值就不一样，通过对比主库和从库checksum的值就可以很容易的知道主从是否数据一致了（checksum结果见下文示例）。pt-table-checksum同一时间只会对一张表进行checksum计算，所以它不会消耗过多的数据库资源。无论需要检查的MySQL数据库有多大，我们都无需担心。知道了工作原理，其他的就好办了。接下来我们来实际测试一下，看一看这个工具是如何能够方便的帮我们检查主从的不一致。

实操部分

1、安装

pt-table-checksum工具存在于percona-toolkit工具包内。基于不同的操作系统下载对应的包进行安装即可，安装过程在此省略。需要注意的一点是percona-toolkit的安装并不限制于主库或是从库，只需要安装在某台能同时访问主库和从库的机器中即可。在笔者的测试环境中，该工具在主库所在的服务器上安装。工具包可以从以下链接找到：https://www.percona.com/downloads/percona-toolkit/LATEST/

2、基础准备

在使用pt-table-checksum工具之前所需要做的准备工作主要包含以下2步。

1）创建数据库账户

在master和slave上创建统一的账号，并进行必要权限的授权，可参考以下语句：

mysql> grant select,process,super,replication slave,create,delete,insert,update on *.* to 'ms_check_user'@'%' identified by '123456';
mysql> grant all privileges on percona.* to 'ms_check_user'@'%';
mysql> flush privileges;

2）创建用户和表

pt-table_checksum会在主库上自动创建percona数据库以及checksums表，但如果从库设置的同步方式是指定数据库才同步（基于replicate-do-db）的话，那么对应的percona库和checksums表就不会在从库自动创建。在笔者的测试环境中，从库同步的数据库是通过replicate-do-db指定的，因此为了避免出现问题，手动在从库上创建库和表。创建库和表可参考以下语句：

mysql> create database percona;
mysql> use percona;
mysql> CREATE TABLE `checksums` (
    ->   `db` char(64) NOT NULL,
    ->   `tbl` char(64) NOT NULL,
    ->   `chunk` int(11) NOT NULL,
    ->   `chunk_time` float DEFAULT NULL,
    ->   `chunk_index` varchar(200) DEFAULT NULL,
    ->   `lower_boundary` text,
    ->   `upper_boundary` text,
    ->   `this_crc` char(40) NOT NULL,
    ->   `this_cnt` int(11) NOT NULL,
    ->   `master_crc` char(40) DEFAULT NULL,
    ->   `master_cnt` int(11) DEFAULT NULL,
    ->   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`db`,`tbl`,`chunk`),
    ->   KEY `ts_db_tbl` (`ts`,`db`,`tbl`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、测试验证

我们可以通过以下命令在MySQL主库所在服务器上进行测试，以确认CMDB数据库在主从双边是否一致。

pt-table-checksum --no-check-binlog-format --nocheck-replication-filters --databases cmdb -h127.0.0.1 -u'ms_check_user' -p'123456'

该命令中包含的几个可选参数解释如下：
--no-check-binlog-format：检查复制的binlog模式，如果binlog模式是ROW，则会报错；
--nocheck-replication-filters：不检查复制过滤器；
--databases：要进行主从检查的数据库，多个库以逗号分隔；
-h：服务器地址；
-u：账户；
-p：密码；

pt-table-checksum还有许多可选的参数，在此就不做一一介绍了，可以查看以下链接：

https://www.percona.com/doc/percona-toolkit/1.0/pt-table-checksum.html

该命令执行完毕后，我们可以看到类似如下输出。

Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED   TIME  TABLE
12-18T11:06:37      0      0      107          0       1       0   0.040 cmdb.t_cmdb_tb_1
12-18T11:06:37      0      0       65          0       1       0   0.055 cmdb.t_cmdb_tb_2
12-18T11:06:37      0      0       67          0       1       0   0.052 cmdb.t_cmdb_tb_3
12-18T11:06:37      0      0       83          0       1       0   0.041 cmdb.t_cmdb_tb_4
12-18T11:06:38      0      0        4          0       1       0   0.048 cmdb.t_cmdb_tb_5
12-18T11:06:38      0      0        2          0       1       0   0.042 cmdb.t_cmdb_tb_6

对于该检查输出结果的各项字段解释如下：

TS：完成检查的时间；
ERRORS：检查时候发生错误和警告的数量；
DIFFS：0表示一致，1表示不一致。当指定–no-replicate-check时，会一直为0，当指定–replicate-check-only会显示不同的信息；
ROWS：表的行数；
DIFF_ROWS：不一致的行数；
CHUNKS：被划分到表中的块的数目；
SKIPPED：由于错误或警告或过大，则跳过块的数目；
TIME：执行的时间；
TABLE：被检查的表名；

我们可以看出，对于MySQL中CMDB库的各表检查结果确认主从并未出现任何不一致（所有表的ERRORS和DIFFS都为0）。按照这个方式，我们可以依次对主从涉及到的所有重要数据库进行主从一致性检查。

至此，我们介绍了如何通过pt-table-checksum来实现对MySQL主从的一致性检查。如果检查发现已经存在了数据或结构的不一致，又该如何处理呢？我们可以通过同一软件包内的pt-table-sync工具来实现主从不一致的修复

打印修复sql：指定库表

pt-table-sync --databases=dataname --tables=table1,table2 h=MasterIP,u=ms_check_user,p=123456 h=SlaveIP,u=zxfly,p=zxfly --charset=utf8 --print 

修复：

pt-table-sync --databases=dataname --tables=table1,table2 h=MasterIP,u=ms_check_user,p=123456 h=SlaveIP,u=zxfly,p=zxfly --charset=utf8 --exec

个人总结

这两个工具一般都是结合起来使用，弥补了mysql没有数据一致性校验的机制，让运维人员在主从复制架构中更能维护得更好。基于percona官方的说明在pt-table-checksum工具中最好让复制是基于语句的复制，而基于语句和基于行的复制各有各的优缺点，如果考虑到在后期的维护中会常用到pt-table-checksum工具，个人认为还是该把binlog_format设置为statement，或者mixed。最后要说的是，如果在生产环境上真的产生了主备数据不一致，而不是延迟导致的，那在利用这些工具对数据操作时切记记得对源数据要进行备份，不管源数据是完好的，还是有些数据已被损坏，你在做数据修复工作前一定要把源数据做一个备份，在数据恢复这样一个高压的环境，谁能保证你做的操作都是规范且正确的？如果操作失误，你起码还有回滚的机会