利用pt-table-checksum做主从一致性校验

主从数据的一致性校验是个头疼的问题，偶尔被业务投诉主从数据不一致，或者几个从库之间的数据不一致，这会令人沮丧。通常我们仅有一种办法，热备主库，然后替换掉所有的从库。这不仅代价非常大，而且类似治标不治本的方案，让人十分不安。因此我们需要合适的工具，至少帮我们回答下面三个问题：

• 是从库延迟导致了用户看到的数据不一致，还是真的主从数据就不一致？
• 如果不一致，这个比例究竟多大？
• 下次还会出现吗？

回答清楚这几个问题，有助于我们决定是否修复，以及修复的方式，还可以帮我们找出不一致的数据，进而定位问题根源。而percona的pt-table-checksum正是我们想要的。

pt-table-checksum简介

pt-table-checksum是著名的 percona-toolkit 工具集的工具之一。它通过在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。这种校验是分表进行的，在每个表内部又是分块进行的，而且pt工具本身提供了非常多的限流选项，因此对线上服务的冲击较小。

checksum计算原理

1. 单行数据checksum值的计算

pt工具先检查表的结构，并获取每一列的数据类型，把所有数据类型都转化为字符串，然后用 concat_ws() 函数进行连接，由此计算出该行的checksum值。checksum默认采用crc32，你可以自己定义效率更高的udf。

2. 数据块checksum值的计算

如果一行一行的计算checksum再去和从库比较，那么效率会非常低下。pt工具选择智能分析表上的索引，然后把表的数据split成一个个chunk，计算的时候也是以chunk为单位。因此引入了聚合函数 BIT_XOR() 。它的功能可以理解为把这个chunk内的所有行的数据拼接起来，再计算crc32的值，就得到这个chunk的checksum值。sql语句如下：
这其中还有count(*)，用来计算chunk包含的行数。每一次对chunk进行checksum后，pt工具都会对耗时进行统计分析，并智能调整下一个chunk的大小，避免chunk太大对线上造成影响，同时也要避免chunk太小而效率低下。

3. 一致性如何保证

当pt工具在计算主库上某chunk的checksum时，主库可能还在更新，同时从库可能延迟使得relay-log中还有与这个chunk数据相关的更新，那该怎么保证主库与从库计算的是”同一份”数据？答案是加for update当前读锁，这保证了主库的某个chunk内部数据的一致性。否则，1000个人chekcusm同样的1000行数据，可能得到1000个不同的结果，你无法避开mvcc的干扰！获得for update锁后，pt工具开始计算chunk的checksum值，并把计算结果保存到pt工具自建的结果表中(采用replace into select的方式)，然后释放锁。该语句最终会传递到从库并执行相同的计算逻辑。

内部工作过程

有了上面关键的几点说明，我们再来看看pt工具的内部工作过程，如下图:简单解释下工作过程：

• 1. 连接到主库：pt工具连接到主库，然后自动发现主库的所有从库。默认采用show full processlist来查找从库，但是这只有在主从实例端口相同的情况下才有效。
• 3. 查找主库或者从库是否有复制过滤规则：这是为了安全而默认检查的选项。你可以关闭这个检查，但是这可能导致checksum的sql语句要么不会同步到从库，要么到了从库发现从库没有要被checksum的表，这都会导致从库同步卡库。
• 5. 开始获取表，一个个的计算。
• 6. 如果是表的第一个chunk，那么chunk-size一般为1000；如果不是表的第一个chunk，那么采用19步中分析出的结果。
• 7. 检查表结构，进行数据类型转换等，生成checksum的sql语句。
• 8. 根据表上的索引和数据的分布，选择最合适的split表的方法。
• 9. 开始checksum表。
• 10. 默认在chunk一个表之前，先删除上次这个表相关的计算结果。除非–resume。
• 14. 根据explain的结果，判断chunk的size是否超过了你定义的chunk-size的上限。如果超过了，为了不影响线上性能，这个chunk将被忽略。
• 15. 把要checksum的行加上for update锁，并计算。
• 17-18. 把计算结果存储到master_crc master_count列中。
• 19. 调整下一个chunk的大小。
• 20. 等待从库追上主库。如果没有延迟备份的从库在运行，最好检查所有的从库，如果发现延迟最大的从库延迟超过max-lag秒，pt工具在这里将暂停。
• 21. 如果发现主库的max-load超过某个阈值，pt工具在这里将暂停。
• 22. 继续下一个chunk，直到这个table被chunk完毕。
• 23-24. 等待从库执行完checksum，便于生成汇总的统计结果。每个表汇总并统计一次。
• 25-26. 循环每个表，直到结束。 
  校验结束后，在每个从库上，执行如下的sql语句即可看到是否有主从不一致发生：
  select * from percona.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc) \G

重要选项

安全选项：

–check-replication-filters 是否检查复制过滤规则 
–check-slave-tables 检查是否所有从库都有被检查的表和列 
–chunk-size-limit 每个chunk最大不能超过这个大小，超过就忽略它

限速选项：

–check-interval 多久检查一次主从延迟、主库负载是否达到上限 
–check-slave-lag 是否只检查这个从库的延迟 
–max-lag 最大延迟，超过这个就等待 
–max-load 最大负载，超过这个就等待

过滤选项：

–databases 只检查某些库 
–tables 只检查某些表 
这些过滤选项在修复不一致数据后，检查修复效果很有用。

其他选项

–resume 因某种原因中断，下次接着执行，不用从头开始 
–chunk-time 每个chunk被计算的时间，一般默认为0.5秒

实战举例

PTDEBUG=1 ./pt-table-checksum --user=user --password=pass --host=10.10.10.10 --port=3306 --databases=nettedfish --tables=just_do_it --recursion-method=processlist

缺陷和注意事项

• 如果表没有主键或唯一索引，或者干脆没有任何索引，那么pt工具在chunk表的时候，将无所适从。不过我们已强制在建表的时候，每个表都必须有主键。
• –check-binlog-format是默认选项，建议不要关闭它。pt-table-checksum工具自身产生的所有sql语句要基于语句格式同步到从库，这是由它的实现原理决定的。但是在A-B-C的级联复制结构中，如果B是行格式的复制，那么B与C的数据一致性校验就没法做了。在A上设置该sql语句为语句级并不会把set这个动作记录到binlog中，这个属性无法级联传递。
• 主从异构的情况下，checksum语句可能在从库上执行失败，即使是索引的不一致。例如sql语句中有force index某个索引，但是从库的表上没有这个索引，就会导致卡库。

总结

pt-table-checksum是校验主从数据不一致的最好工具。由于MySQL复制自身的缺陷，或主从切换不严谨，或备份软件bug等原因，都可能导致主从数据的不一致。不管你管不管，不一致都在那里，就看数据对你重不重要，重要的话，就定期做下检查并修复吧。 
且看下回分解：用pt-table-sync修复不一致的数据。

摘要：

工作上需要把一个从库提升为主库，但对从库和主库的数据一致性不能保证一样，所以就利用 pt-table-checksum 工作来检查主从的一致性（之前写过用1.0.1的版本可以进行操作的文章，但是在新版本操作就不行了，只能重新来过）以及通过 pt-table-sync 如何修复这些不一致的数据。

二. 前言
pt-table-checksum是一个在线验证主从数据一致性的工具,主要用于以下场景:
1. 数据迁移前后,进行数据一致性检查
2. 当主从复制出现问题,待修复完成后,对主从数据进行一致性检查
3. 把从库当成主库,进行数据更新,产生了"脏数据"
4. 定期校验

前提：

下载地址：wget  http://www.percona.com/downloads/percona-toolkit/2.2.14/tarball/percona-toolkit-2.2.14.tar.gz

yuminstallperl-ExtUtils-CBuilderperl-ExtUtils-MakeMaker

yum install perl-Time-HiRes

yum install perl-DBI

yum install perl-DBD-MySQL

安装方法：perl Makefile.PL

make

make install

Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1.

三. 环境
master
|----slave1
|----slave2

四. 临时校验(端口3306)

• 准备工作:

1. 创建用户checksums,192.168.250.249为当前写库 
   
   1. GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'192.168.250.249' IDENTIFIED BY 'xxxx';
   
   

   select	查看所有库的表,原理可加 explain选项查看
   process	show processlist
   super	set binlog_format='statement'
   replication slave	show slave hosts

   连接范围: 允许master连接

2. 参数解释

   DSN	h='192.168.250.249',u='checksums',p='xxxx',P=3306
   -d	指定校验的库,多个库用逗号(,)分隔
   --nocheck-replication-filters	忽略replication-do-db规则
   --replicate=test.checksums	在test库下创建checksums表,并将数据写入
   --recursion-method=none	指定复制检查的方式,默认情况下使用SHOW PROCESSLIST,如果非标准的3306端口,就使用SHOW SLAVE HOSTS的方式,推荐使用dsn方式,手动指定

   METHOD USES =========== ================== processlist SHOW PROCESSLIST hosts SHOW SLAVE HOSTS dsn=DSN DSNs from a table none Do not find slaves DSN格式:dsn=h=host,D=pt,t=dsns

• 使用举例:

1. 登录主库,增加授权
   
   1. GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'192.168.250.249' IDENTIFIED BY 'xxxx';
   
   
2. 登录主库,执行pt-table-checksums脚本
3. 只检查mysql库 
   
   1. pt-table-checksum h='192.168.250.249',u='checksums',p='xxxx',P=3306 -d mysql --nocheck-replication-filters --replicate=test.checksums
   
   
4. 只检查mysql库,但是过滤mysql.user表 
   
   1. pt-table-checksum h='192.168.250.249',u='checksums',p='xxxx',P=3306 -d mysql --ignore-tables=mysql.user --nocheck-replication-filters --replicate=test.checksums
   
   
5. 手动在从库上查询的sql 
   --chunk-size 指定块的大小,默认是1000条. 
   
   1. SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM test.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl;

五. 利用脚本做定期任务. 

• 准备工作

1. 建库
   
   1. CREATE DATABASE pt CHARACTER SET utf8;
   
   
2. 授权 
   
   1. GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'192.168.250.249' IDENTIFIED BY 'xxxx'; 
   2. GRANT ALLON pt.* TO 'checksums'@'192.168.250.249' IDENTIFIED BY 'xxxx';
   
   
3. 在pt库中建表,并插入从库的信息. 
   
   1. CREATE TABLE `dsns` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) );
      
   2. 
      
   3. -- 写入从库信息
      
   4.  INSERT INTO dsns (parent_id,dsn) values(1, "h=replica_host,u=checksums,p=password,P=3306"); 
      
   5. -- 如果有多个从库,就插入多条记录. 
      
   6. -- 也可以按如下简写
      
   7. INSERT INTO dsns (parent_id,dsn) values(1, "h=replica_host");
   
   
4. 全库校验的命令
   
   1. ./pt-table-checksum h='192.168.250.249',u='checksums',p='xxxx',P=3306 --nocheck-replication-filters --replicate=pt.checksums --recursion-method=dsn=h=host,D=pt,t=dsns

• 脚本
  
  1. #!/bin/bash
  2. # ########################################################################
  3. # This program is percona-toolkit shell
  4. # Version: 1.0 (20120926)
  5. # Authors: lianjie.ning@qunar.com
  6. # History:
  7. # ########################################################################
  8. set -e
  9. set -u
  10. if [ $# -eq 0 ]; then
  11. echo "Usage:
  12. $0 -h, help
  13. $0 -P port -c [pt-table-checksum]"
  14. exit
  15. fi
  16. trap "send_mail" EXIT
  17. # send mail
  18. function send_mail()
  19. {
  20. ESUBJECT="$HOSTNAME $0"
  21. EMAIL='xxx@xxx.com'
  22. if [ -r $LOG ]; then
  23. cat $LOG |mail -s "$ESUBJECT" $EMAIL
  24. else
  25. echo "The log($LOG) is not exist !" |mail -s "$ESUBJECT" $EMAIL
  26. fi
  27. rm "$LOG"
  28. }
  29. # default variables
  30. TIME=`date +%Y%m%d%H%M%S`
  31. LOG="/tmp/$HOSTNAME.checksum.log"
  32. #CHECKSUM="/usr/local/bin/pt-table-checksum"
  33. CHECKSUM="/home/lianjie.ning/pt-table-checksum"
  34. exec 1>>$LOG 2>$LOG
  35. function checksum()
  36. {
  37. master_host="192.168.250.249"
  38. user="checksums"
  39. password="xxxx"
  40. : ${port:="3306"}
  41. db_table="pt.checksums"
  42. echo "MySQL Port: $port"
  43. echo "Verify MySQL replication integrity"
  44. $CHECKSUM h="$master_host",u="$user",p="$password",P="$port" --nocheck-replication-filters --replicate=$db_table --recursion-method=dsn=D=pt,t=dsns
  45. }
  46. # main
  47. while getopts :P:c arg
  48. do
  49. case $arg in
  50. P)
  51. port="$OPTARG"
  52. ;;
  53. c)
  54. checksum
  55. ;;
  56. :)
  57. echo "$arg: 错误参数"
  58. ;;
  59. \?)
  60. echo "$arg: 非法选项"
  61. ;;
  62. esac
  63. done
  六. 问题处理:
  
  1. 在主库上执行 
     pt-table-checksum h='192.168.250.249',u='checksums',p='xxxx',P=3306 -d mysql --nocheck-replication-filters --replicate=test.checksums 
     需要注意test.checksums是否存在,如果存在,则手工删除,否则会报如下错误 
     06-07T16:54:23 User does not have all privileges on --replicate table `test`.`checksums`.
  2. binlog_format问题(脚本bug) 
     06-07T19:06:07 Error checksumming table mysql.columns_priv: Error executing checksum query: Checksum query for table mysql.columns_priv caused MySQL error 1592: 
         Level: Note 
          Code: 1592 
       Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave. 
         Query: REPLACE INTO `test`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT() AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `host`, `db`, `user`, `table_name`, `column_name`, `timestamp` + 0, `column_priv`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `mysql`.`columns_priv` /*checksum table/ 
     该问题与复制过滤器replicate-do-db 和 pt-table-checksums,innodb_strict_mode = 1均没有关系 
     脚本的bug,在mysql 5.5.18版本之后,都会出现这个问题 
     https://bugs.launchpad.net/percona-toolkit/+bug/919499 
     下个新版本,已经修复 
     http://bazaar.launchpad.net/~percona-toolkit-dev/percona-toolkit/2.1/view/head:/bin/pt-table-checksum
  3. 索引问题 
     06-07T21:19:31 Cannot checksum table db.table: There is no good index and the table is oversized. at ./pt-table-checksum line 3899.
     因为该工具是根据主键索引或者唯一索引来分块进行校验的,默认是1000行为一块.