前言

当MySQL数据库架构使用主从时，由于事务创建和提交的顺序并不一致。例如我们先创建A事务，在创建B事务，但我们在提交的时候可能先提交B事务，在提交A事务。此时我们从服务在重放二进制日志时，会先执行B，再执行A。这样就会导致最终主从数据不一致。在一些对数据一致性要求高的场景下，我们就需要检测主从数据的一致性。接下来我们将会介绍percona工具包中的pt-table-checksum做一致性检测，以及pt-table-sync同步数据。

一. 部署percona tookit

  1. 下载安装包 ~]# wget https://www.percona.com/downloads/percona-toolkit/3.0.5/binary/redhat/7/x86_64/percona-toolkit-3.0.5-1.el7.x86_64.rpm

  2. 安装 ~]# yum install percona-toolkit-3.0.5-1.el7.x86_64.rpm

二. 一致性检测工具pt-table-checksum

1. 一致性检测原理

pt-table-checksum是percona-toolkit系列工具中的一个，用于检测主从数据库中的一致性。一次只工作在一张表上，会将主库上的表切割成一个一个的chunk，这种切割要依赖于表上的index。所以在检测时不需要大量的内存和前期工作，而且还可以在数据尖峰是通过指数衰减算法，快速选择适合的chunk大小，减轻服务器压力。将表切割成一个一个的chunk接下来会对chunk进行checksum，并记录下来。并对比从库上的checksum是否一致，从而判断数据是否一致。并且在检测过程中会自动判断master负载，以及slave延迟，一旦超过阈值就会停止下来。对于线上的环境影响不大。而且他还可以随时停止，只需在重启时加入--resume就会从上次的检测重新开始。接下来介绍其详细过程。

1) 表结构的检查

表结构的检查也称之为单行数据checksum值的计算，并获取每一列的数据类型，把所有数据类型都转化为字符串，然后用concat_ws()函数进行连接，由此计算出该行的checksum值。checksum默认采用crc32计算。

2) 数据块checksum的计算

pt-table-sync会智能分析表上的索引，然后把表的数据split成若干个chunk，计算的时候以chunk为单位。可以理解为把chunk内所有行的数据拼接起来，再计算crc32的值，即得到该chunk的checksum值。所以它把checksum结果存储到统计表，然后把执行过的sql语句记录到binlog中，任务就算完成。然后从服务器会读取到binlog的SQL语句依次执行，并将checksum保存在表中。

2. 校验

1）授权

Create database pt CHARACTER SET utf8; GRANT UPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON . TO 'checksums'@'192.168.239.135' identified by 'check_pass'; GRANT ALL ON pt.* TO 'checksums'@'192.168.%'; 在这里我们创建了一个数据库，用于存储一致性检测生成的数据。主从同步工具pt-table-sync根据此数据库中的数据查找有不一致的数据，并同步。其中135为主，136为从。

2）校验（Master服务器运行）

pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases newtable -u'checksums' -p'checksums' -h192.168.239.135 #-h -u -p -P -S -d 连接信息 #--nocheck-replication-filters 检测中忽略mysql 配置参数binlog_ignore_db等。 #--nocheck-binlog-format 不检测日志格式，默认是使用statement 格式，如果binlog的日志与默认不同将会检测失败。所以我们会关闭binlog格式的检测 #--replicate 指定checksum 存储的db和表， 如test.checksum #--chunk-size， --chunk-size-limit 用于指定检测块的大小。 可控性更强，Number of rows to select for each checksum query。默认是1000。对于--chunk-size-limit来说，他可以避免当主服务器为空，而从服务数据很大时造成的从延时过大。 #--lock-wait-timeout innodb 锁的超时设定， 默认为1 #--max-load ： Examine SHOW GLOBAL STATUS after every chunk, and pause if any status variables are higher than their thresholds #--replicate-check-only 只输出数据不一致的信息。 #--resume: pt-table-checksum停止后，使用此参数可以接着停止的地方开始。

注意： pt-table-checksum前提假设主从的表和表结构是一致的，如果不一致pt-table-checksum会失败

三. 数据同步工具pt-table-sync

pt-table-sync是MySQL数据同步工具，并不仅仅是同步主从数据，任意主机上的表都可以同步。 pt-table-checksum只是校验，所以它把checksum结果存储到统计表，然后把执行过的sql语句记录到binlog中，任务就算完成。 pt-table-sync则不同，工作流程如下：

  • a) 连接到主库：pt工具连接到主库，然后自动发现主库的所有从库。默认采用show full processlist来查找从库，但是这只有在主从实例端口相同的情况下才有效
  • b) 在主库上对每一个chunk，在校验时加上for update锁。一旦获得锁，就记录下当前主库的show master status值。在从库上执行select master_pos_wait()函数，等待从库sql线程执行到show master status得到的位置。以此保证，主从上关于这个chunk的内容均不再改变。
  • c) 对这个chunk执行checksum，然后与主库的checksum进行比较
  • d) 如果checksum相同，说明主从数据一致，就继续下一个chunk
  • e) 如果checksum不同，说明该chunk有不一致。深入chunk内部，逐行计算checksum并比较
  • f) 如果发现某行不一致，则标记下来。继续检测剩余行，直到这个chunk结束
  • g) 对找到的主从不一致的行，采用replace into(如果数据不存在则插入,存在则更新,避免了主键约束)语句，在主库执行一遍以生成该行全量的binlog,并同步到从库，这会以主库数据为基准来修复从库;对于主库有的行而从库没有的行，采用replace在主库上插入(必须不能是insert);于从库有而主库没有的行,通过在主库执行delete来删除(pt-table-sync强烈建议所有的数据修复都只在主库进行,而不建议直接修改从库数据;但是也有特例，后面会讲到)。
  • h) 直到修复该chunk所有不一致的行。继续检查和修复下一个chunk
  • i) 直到这个从库上所有的表修复结束。开始修复下一个从库

四. 实验示例

1. 实验环境

  • 主机IP : 192.168.239.135 192.168.239.136
  • 主机系统 : centos7.2
  • MySQL版本 : 5.5.56 两台主机已经配置好主从，其中135为主，136为从

2. 主从数据一致性检查

  • 1）授权（master主机上） mysql> CREATE DATABASE pt; #创建数据库pt用于存放checksum的值 mysql> GRANT UPDATE,INSERT,SELECT,PROCESS,SUPER,REPLICATION SLAVE ON . 'checksum'@'192.168.%' IDENTIFIED BY "check_pass"; #创建checksum用户用于执行检测，以及分配检测时要用的权限。 mysql> GRANT ALL ON pt.* TO checksum@'192.168.%'; #checksum用户要将checksum的值写入pt数据库中所以需要分配权限给checksum用户。 权限解释：
select     //查看所有库的表,原理可加 explain选项查看
process    //show processlist
super      //set binlog_format='statement'
replication slave   //show slave hosts

__注意 : __在master上执行一致性检测时，master会通过show processlist查看slave主机，并通过连接master的账号和密码连接slave，所以master上一致性检测的账号在slave上一定要有。

  • 2）在master上执行一致性检测 首先我们需要人为创造不一致，在slave中删除一条记录 执行一致性检测（主从上都可执行） ~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --databases=hellodb -u 'checksum' -p 'check_pass' -h 192.168.239.135
                       TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
03-14T16:25:21               0      1                  8              1       0           0.011 hellodb.classes
03-14T16:25:21               0      0                 14             1       0           0.017 hellodb.coc
03-14T16:25:21               0      0                 7               1       0           0.032 hellodb.courses
03-14T16:25:21               0      0                15              1       0          0.015 hellodb.scores
03-14T16:25:21               0      0                25              1       0          0.016 hellodb.students
03-14T16:25:21               0      0                4                1       0          0.018 hellodb.teachers
03-14T16:25:21               0      0                0                1       0          0.016 hellodb.toc

显示数据解释 __TS : __完成检测表时的时间，不显示年份。 __ERRORS : __在checksum时发生的错误和警告的次数 __DIFFS : __主从之间chunk不同的个数，如果不为0，表明主从数据有不一致的。 __ROWS : __检测表时一个chunk有多少行。如果使用了-where选项，一个表中的chunk可能不同 __CHUNKS : __表被切割成了多少个chunk __SKIPPED : __由于某种原因跳过检测chunk的数量 __TIME : __checksum此表所花的时间。 __TABLE : __被checksum的表明 由上可知classes表中有数据不一致。

3. 主从同步

主从实现同步，往往都是借助pt-table-checksum产生的checksum表来说实现数据同步。

1）手动同步

~]# pt-table-sync --print --sync-to-master h=192.168.239.136,u=checksum,p=check_pass --databases=hellodb --replicate=pt.checksums h=192.168.239.136,u=checksum,p=check_pass 指明需要同步的slave主机，以及登录的用户名和密码 --databases=hellodb：指明同步的数据 --replicate=pt.checksums：同步时使用的checksum数据库。 --sync-to-master：会通过show slave status去自动找主服务器同步数据，如果没有此参数，我们需要通过h p u同时指明master和slave，即两组h p u。 --print：主从不同的数据仅打印出来，并不在从上执行。 此命令在主从上都可执行。输出信息如下： 我们只需在从服务器上执行REPLACE INTO hellodb.classes(classid, class, numofstu) VALUES ('1', 'Shaolin Pai', '10')这条sql语句即可

2）自动同步

~]# pt-table-sync --execute --sync-to-master h=192.168.239.136,u=checksum,p=check_pass --databases=hellodb --replicate=pt.checksums --execute：自动修复主从不同的数据 自动同步出现如下错误： pt-table-sync在实现同时时并不会直接在slave上进行操作，都是在master上执行命令，进而影响slave，这种修改数据的方式更加安全。所以master需要在slave上有对应的权限。 上图显示master在slave上没有delete权限，查看slave分配的权限，如下图可知确实没有delete权限，只需在master上将checksum用户添加delete权限即可

在master上修改checksum的权限，由于主从同步，slave也会修改对应用户权限

mysql GRANT UPDATE,INSERT,SELECT,DELETE,PROCESS,SUPER,REPLICATION SLAVE ON . TO 'checksum'@'192.168.%';

执行数据同步，再次执行checksum检测，可以看到没有不同，而且查看slave中classes数据发现删除的数据又出现了

参考

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