mysql 主从数据不一致

曾经终败给现在 2021-12-23 06:04 331阅读 0赞

**用pt-table-checksum校验数据一致性**

Jun 4th, 2013

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

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

回答清楚这几个问题，有助于我们决定是否修复，以及修复的方式，还可以帮我们找出不一致的数据，进而定位问题根源。而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()][concat_ws]函数进行连接，由此计算出该行的checksum值。checksum默认采用crc32，你可以自己定义效率更高的udf。

**2.** **数据块checksum值的计算**

如果一行一行的计算checksum再去和从库比较，那么效率会非常低下。pt工具选择智能分析表上的索引，然后把表的数据split成一个个chunk，计算的时候也是以chunk为单位。因此引入了聚合函数[BIT\_XOR()][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-sync]

**用pt-table-sync修复不一致的数据**

Jun 5th, 2013

上一篇[用pt-table-checksum校验数据一致性][pt-table-checksum]介绍了校验主从数据是否一致的方法，当确定主从数据不一致后，就要考虑如何修复。这里推荐[percona-toolkit][]的另外一个重要工具：pt-table-sync。

**pt-table-sync****简介**

顾名思义，它用来修复多个实例之间数据的不一致。它可以让主从的数据修复到最终一致，也可以使通过应用双写或多写的多个不相关的数据库实例修复到一 致。同时它还内部集成了pt-table-checksum的校验功能，可以一边校验一边修复，也可以基于pt-table-checksum的计算结果 来进行修复。

**工作原理**

**1.** **单行数据checksum值的计算**

计算逻辑与pt-table-checksum一样，也是先检查表结构，并获取每一列的数据类型，把所有数据类型都转化为字符串，然后用concat\_ws()函数进行连接，由此计算出该行的checksum值。checksum默认采用crc32计算。

**2.** **数据块checksum值的计算**

同pt-table-checksum工具一样，pt-table-sync会智能分析表上的索引，然后把表的数据split成若干个chunk， 计算的时候以chunk为单位。可以理解为把chunk内所有行的数据拼接起来，再计算crc32的值，即得到该chunk的checksum值。

**3.** **坏块检测和修复**

前面两步，pt-table-sync与pt-table-checksum的算法和原理一样。再往下，就开始有所不同：

pt-table-checksum只是校验，所以它把checksum结果存储到统计表，然后把执行过的sql语句记录到binlog中，任务就 算完成。语句级的复制把计算逻辑传递到从库，并在从库执行相同的计算。pt-table-checksum的算法本身并不在意从库的延迟，延迟多少都一样 计算(有同事对此不理解，可以参考我的前一篇文章)，不会影响计算结果的正确性(但是我们还是会检测延迟，因为延迟太多会影响业务，所以总是要加上— max-lag来限流)。  
pt-table-sync则不同。它首先要完成chunk的checksum值的计算，一旦发现主从上同样的chunk的checksum值不同，就深 入到该chunk内部，逐行比较并修复有问题的行。其计算逻辑描述如下(以修复主从结构的数据不一致为例，业务双写的情况修复起来更复杂—因为涉及到冲突 解决和基准选择的问题，限于篇幅，这里不介绍)：

1.  对每一个从库，每一个表，循环进行如下校验和修复过程。
2.  对每一个chunk，在校验时加上for update锁。一旦获得锁，就记录下当前主库的show master status值。
3.  在从库上执行select [master\_pos\_wait()][master_pos_wait]函数，等待从库sql线程执行到show master status得到的位置。以此保证，主从上关于这个chunk的内容均不再改变。
4.  对这个chunk执行checksum，然后与主库的checksum进行比较。
5.  如果checksum相同，说明主从数据一致，就继续下一个chunk。
6.  如果checksum不同，说明该chunk有不一致。深入chunk内部，逐行计算checksum并比较(单行的checksum的比较过程与chunk的比较过程一样，单行实际是chunk的size为1的特例)。
7.  如果发现某行不一致，则标记下来。继续检测剩余行，直到这个chunk结束。
8.  对找到的主从不一致的行，采用replace into语句，在主库执行一遍以生成该行全量的binlog，并同步到从库，这会以主库数据为基准来修复从库；对于主库有的行而从库没有的行，采用 replace在主库上插入(必须不能是insert)；对于从库有而主库没有的行，通过在主库执行delete来删除(pt-table-sync强烈 建议所有的数据修复都只在主库进行，而不建议直接修改从库数据；但是也有特例，后面会讲到)。
9.  直到修复该chunk所有不一致的行。继续检查和修复下一个chunk。
10. 直到这个从库上所有的表修复结束。开始修复下一个从库。

**重要选项**

**安全选项**

—\[no\]check-triggers 检查是否有触发器，有则警告  
—\[no\]foreign-key-checks 默认检查主外键约束，有则警告  
—\[no\]unique-checks 检查是否有唯一索引，无则警告

**过滤选项**

—ignore-databases  
—ignore-engines  
—ignore-tables

**其他选项**

—replicate=s 与pt-table-checksum结合起来，只修复，而不校验。使用pt-table-checksum之前校验的结果  
—bidirectional 双向同步。通常都以主库的数据为准，如果开启双向同步，就要定义冲突解决规则，会比较复杂

**用法举例**

假设10.55.55.55是主库，10.73.73.73是它的从库，端口在3306。  
1. 先校验：  
PTDEBUG=1 ./pt-table-checksum --user=user --password=pass --host=10.55.55.55 --port=3306 --databases=elink --tables=my\_cms\_10 --recursion-method=processlist 2. 根据校验结果，只修复10.73.73.73从库与主库不一致的地方：  
PTDEBUG=1 ./pt-table-sync --execute --replicate percona.checksums --sync-to-master h=10.73.73.73,P=3306,u=user,p=pass 3. 修复后，再重新校验一次。执行第一步的语句即可。  
4. 检查修复结果： 登陆到10.73.73.73，执行如下sql语句返回若为空，则说明修复成功： select \* from percona.checksums where master\_cnt <> this\_cnt OR master\_crc <> this\_crc OR ISNULL(master\_crc) <> ISNULL(this\_crc)

**注意事项**

*  采用replace into来修复主从不一致，必须保证被replace的表上有主键或唯一键，否则replace into退化成insert into，起不到修复的效果。这种情况下pt-table-sync会采用其他校验和修复算法，但是效率非常低，例如对所有列的group by然后求count(\*)(表一定要有主键！)。
 *  主从数据不一致需要通过replace into来修复，该sql语句必须是语句级。pt-table-sync会把它发起的所有sql语句都设置为statement格式，而不管全局的 binlog\_format值。这在级联A-B-C结构中，也会遇到pt-table-checksum曾经遇到的问题，引起行格式的中继库的从库卡库是 必然。不过pt-table-sync默认会无限递归的对从库的binlog格式进行检查并警告：  
    
 *  由于pt-table-sync每次只能修复一个表，所以如果修复的是父表，则可能导致子表数据连带被修复，这可能会修复一个不一致而引入另一个 不一致；如果表上有触发器，也可能遇到同样问题。所以在有触发器和主外键约束的情况下要慎用。pt-table-sync工具同样也不欢迎主从异构的结 构。pt-table-sync工具默认会进行先决条件的检查。
 *  pt-table-sync在修复过程中不能容忍从库延迟，这正好与pt-table-checksum相反。如果从库延迟太多，pt- table-sync会长期持有对chunk的for update锁，然后等待从库的master\_pos\_wait执行完毕或超时。从库延迟越大，等待过程就越长，主库加锁的时间就越长，对线上影响就越 大。因此要严格设置max-lag。
 *  对从库数据的修复通常是在主库执行sql来同步到从库。因此，在有多个从库时，修复某个从库的数据实际会把修复语句同步到所有从库。数据修复的代 价取决于从库与主库不一致的程度，如果某从库数据与主库非常不一致，举例说，这个从库只有表结构，那么需要把主库的所有数据重新灌一遍，然后通过 binlog同步，同时会传递到所有从库。这会给线上带来很大压力，甚至拖垮集群。正确的做法是，先用pt-table-checksum校验一遍，确定 不一致的程度：如果不同步的很少，用pt-table-sync直接修复；否则，用备份先替换它，然后用pt-table-sync修复。 说明： 这实际提供了一种对myisam备份的思路：如果仅有一个myisam的主库，要为其增加从库，则可以：先mysqldump出表结构到从库上，然后启动 同步，然后用pt-table-sync来修复数据。

**总结**

pt-table-sync工具很复杂也很强大。它修改数据库的内容，所以可能造成安全事故。为了安全，建议：使用pt-table-checksum定期检测数据的一致性，并手动重建损坏较为严重的从库，最后才用pt-table-sync修复剩下的从库。

转载于:https://www.cnblogs.com/Qing-840/p/5501444.html

[percona-toolkit]: http://www.percona.com/software/percona-toolkit
[concat_ws]: http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_concat-ws
[BIT_XOR]: http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_bit-xor
[pt-table-sync]: http://nettedfish.sinaapp.com/blog/2013/06/05/synchronizes-data-efficiently-by-pt-table-sync/
[pt-table-checksum]: http://nettedfish.sinaapp.com/blog/2013/06/04/check-replication-consistency-by-pt-table-checksum/
[master_pos_wait]: http://dev.mysql.com/doc/refman/5.6/en/master-pos-wait.html