MySQL主从一致性校验工具pt-table-checksum简单介绍

1.介绍

pt-table-checksum是percona toolkit工具中的一个命令，用于在线比较Mysql主从数据一致性。

2.工作流程

1. 分块，将一张大表根据主键或唯一索引拆分为多个块，默认第一个块是1000条数据，后面的块大小会根据机器性能浮动。

2. 求校验和，所谓校验和，其实就是一个字符串，mysql为我们提供了crc32函数用于计算校验和。

   pt计算校验和的参考sql如下：

   #假设表拥有联合主键(id1,id2)
   #需要借助上一个块的最大主键值，不然查询会全表扫描，效率非常低
   #一次计算多少数据的校验和是可以自己决定的，看内存
   SELECT 
   COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', 表的所有字段...)) AS UNSIGNED)), 10, 16)), 0) AS crc 
   FROM (SELECT * FROM 要校验的表 WHERE (id1 > 上一个块的最大主键值id1) or (id1 = 上一个块的最大主键值id1 and id2 > 上一个块的最大主键值id2) ORDER BY `id1`,`id2` LIMIT 1000) AS temp_table_name;
   

   如果是校验第一块的话，此时还没有前一块的最大主键或最大唯一索引，sql如下：

   SELECT 
   COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', 表的所有字段...)) AS UNSIGNED)), 10, 16)), 0) AS crc 
   FROM 要校验的表 order by id1, id2 limit 1000;
   

   计算校验和最关键的两个函数就是CRC32和BIT_XOR

   crc32在计算机网络中有介绍，循环冗余校验，可以看作得到一个8位哈希值

   bit_xor求异或，是一个聚合操作，如果你的块大小为1000，在你得到1000个哈希值之后，对其异或最终得到一个字符串

3. 通过replica into操作，借助主从复制，在主库上执行插入操作，在从库回放。从库会生成一个名为percona的库，包含一个checksums表，该表就记录了每块的信息，通过对比校验和就能判断数据是否一致了。如果不一致的话，只能定位到这一块，具体是哪些行不一致，则需要一行一行扫描了。但此时效率就比全表扫描要高很多了。pt-table-sync修复的原理就是如此，先用pt-table-checksum扫描一遍，再逐行扫描不一致的块，将从修复到与主一致

使用replica into而不是insert into避免了主键冲突问题

3.简单使用例子

pt需要指定的是主库的连接信息，它能通过主从关联信息自动找到从库（show slave hosts）

pt-table-checksum --replicate=percona.checksums --no-check-binlog-format --nocheck-replication-filters --databases=db1 --tables=user h=127.0.0.1,u=root,p=123456,P=3306

–replicate 指定生成的校验和信息存储到哪个库表中

–no-check-binlog-format 不校验binlog格式

–nocheck-replication-filters 不校验复制过滤器

4.拓展

根据pt的思路，我们可以自己实现一个数据库一致性校验工具，因为pt是针对主从架构的，在非主从架构下无法生效。