浅析pt-table-checksum和pt-table-sync原理

介绍：

pt-table-checksum 是 Percona-Toolkit 的组件之一，用于检测MySQL主、从库的数据是否一致。其原理是在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库执行，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。检测过程根据唯一索引将表按row切分为块（chunk），以为单位计算，可以避免锁表。检测时会自动判断复制延迟、 master的负载， 超过阀值后会自动将检测暂停，减小对线上服务的影响。pt-table-checksum 默认情况下可以应对绝大部分场景，官方说，即使上千个库、上万亿的行，它依然可以很好的工作，这源自于设计很简单，一次检查一个表，不需要太多的内存和多余的操作；必要时，pt-table-checksum 会根据服务器负载动态改变 chunk 大小，减少从库的延迟。为了减少对数据库的干预，pt-table-checksum还会自动侦测并连接到从库，当然如果失败，可以指定--recursion-method选项来告诉从库在哪里。它的易用性还体现在，复制若有延迟，在从库 checksum 会暂停直到赶上主库的计算时间点（也通过选项--设定一个可容忍的延迟最大值，超过这个值也认为不一致）。为了保证主数据库服务的安全，该工具实现了许多保护措施：1)自动设置 innodb_lock_wait_timeout 为1s2)默认当数据库有25个以上的并发查询时，pt-table-checksum会暂停。可以设置 --max-load 选项来设置这个阀值3)当用 Ctrl+C 停止任务后，工具会正常的完成当前 chunk 检测，下次使用 --resume 选项启动可以恢复继续下一个 chunk

计算原理：

1. 单行数据checksum值的计算 pt工具先检查表的结构，并获取每一列的数据类型，把所有数据类型都转化为字符串，然后用concat_ws()函数进行连接，由此计算出该行的checksum值。checksum默认采用crc32，你可以自己定义效率更高的udf。 2. 数据块checksum值的计算 如果一行一行的计算checksum再去和从库比较，那么效率会非常低下。pt工具选择智能分析表上的索引，然后把表的数据split成一个个chunk，计算的时候也是以chunk为单位。因此引入了聚合函数BIT_XOR()。它的功能可以理解为把这个chunk内的所有行的数据拼接起来，再计算crc32的值，就得到这个chunk的checksum值。这其中还有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的方式)，然后释放锁。该语句最终会传递到从库并执行相同的计算逻辑。

工作过程：

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）循环每个表，直到结束。

使用场景：

1. 数据迁移前后,进行数据一致性检查；2. 当主从复制出现问题,待修复完成后,对主从数据进行一致性检查；3. 把从库当成主库,进行数据更新,产生了"脏数据"；4. 定期校验；

注意事项：

1.表中没有索引，pt-table-checksum将没办法处理2.表中只有普通索引。当数据列有重复并且正好在分块的交界中，将会报错。所以表中最好有一个唯一索引列。01-07T12:05:29 Error checksumming tablefreedom.a6: Possible infinite loop detected! The lower boundary for chunk 2 is <cc, cc> and the lower boundaryfor chunk 3 is also <cc, cc>.  Thisusually happens when using a non-unique single column index.  The current chunk index for table freedom.a6is name which is not unique and covers 1 colum3.块的定义过大。当块大于表的行数时，将会产生全表锁。4.如果表没有主键或唯一索引，或者干脆没有任何索引，那么pt工具在chunk表的时候，将无所适从。不过我们已强制在建表的时候，每个表都必须有主键。5.—check-binlog-format是默认选项，建议不要关闭它。pt-table-checksum工具自身产生的所有sql语句要基于语句格式同步到从库，这是由它的实现原理决定的。但是在A-B-C的级联复制结构中，如果B是行格式的复制，那么B与C的数据一致性校验就没法做了。在A上设置该sql语句为语句级并不会把set这个动作记录到binlog中，这个属性无法级联传递。6.主从异构的情况下，checksum语句可能在从库上执行失败，即使是索引的不一致。例如sql语句中有force index某个索引，但是从库的表上没有这个索引，就会导致卡库。

参考博文：

https://segmentfault.com/a/1190000004309169

http://www.cnblogs.com/huminxxl/p/3978559.html