mysql pt table sync_pt-table-sync修复mysql主从不一致的数据

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

工做原理

1. 单行数据checksum值的计算

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

2. 数据块checksum值的计算

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

3. 坏块检测和修复

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

pt-table-checksum只是校验，因此它把checksum结果存储到统计表，而后把执行过的sql语句记录到binlog中，任务就算完成。语句级的复制把计算逻辑传递到从库，并在从库执行相同的计算。pt-table-checksum的算法自己并不在乎从库的延迟，延迟多少都同样计算(有同事对此不理解，能够参考个人前一篇文章)，不会影响计算结果的正确性(可是咱们仍是会检测延迟，由于延迟太多会影响业务，因此老是要加上—max-lag来限流)。

pt-table-sync则不一样。它首先要完成chunk的checksum值的计算，一旦发现主从上一样的chunk的checksum值不一样，就深刻到该chunk内部，逐行比较并修复有问题的行。其计算逻辑描述以下(以修复主从结构的数据不一致为例，业务双写的状况修复起来更复杂—由于涉及到冲突解决和基准选择的问题，限于篇幅，这里不介绍)：数据库

对每个从库，每个表，循环进行以下校验和修复过程。

对每个chunk，在校验时加上for update锁。一旦得到锁，就记录下当前主库的show master status值。

在从库上执行select master_pos_wait()函数，等待从库sql线程执行到show master status获得的位置。以此保证，主从上关于这个chunk的内容均再也不改变。

对这个chunk执行checksum，而后与主库的checksum进行比较。

若是checksum相同，说明主从数据一致，就继续下一个chunk。

若是checksum不一样，说明该chunk有不一致。深刻chunk内部，逐行计算checksum并比较(单行的checksum的比较过程与chunk的比较过程同样，单行实际是chunk的size为1的特例)。

若是发现某行不一致，则标记下来。继续检测剩余行，直到这个chunk结束。

对找到的主从不一致的行，采用replace into语句，在主库执行一遍以生成该行全量的binlog，并同步到从库，这会以主库数据为基准来修复从库；对于主库有的行而从库没有的行，采用replace在主库上插入(必须不能是insert)；对于从库有而主库没有的行，经过在主库执行delete来删除(pt-table-sync强烈建议全部的数据修复都只在主库进行，而不建议直接修改从库数据；可是也有特例，后面会讲到)。

直到修复该chunk全部不一致的行。继续检查和修复下一个chunk。

直到这个从库上全部的表修复结束。开始修复下一个从库。

重要选项

安全选项

—[no]check-triggers 检查是否有触发器，有则警告

—[no]foreign-key-checks 默认检查主外键约束，有则警告

—[no]unique-checks 检查是否有惟一索引，无则警告

—print 显示同步须要执行的语句安全

过滤选项

—ignore-databases

—ignore-engines

—ignore-tablesapp

其余选项

—replicate=s 与pt-table-checksum结合起来，只修复，而不校验。使用pt-table-checksum以前校验的结果

—bidirectional 双向同步。一般都以主库的数据为准，若是开启双向同步，就要定义冲突解决规则，会比较复杂

—execute 执行数据同步

—charset=utf8 设置字符集函数

用法举例

假设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)

master:10.0.0.96

slave:10.0.0.30

主从数据同步(同步主库的数据到从库)

1.同步单个数据库

./pt-table-sync --execute --sync-to-master --charset=utf8 --user=root --password=qazwsx$%^456 h=10.0.0.30 --database=common --port=3306

2.根据pt-table-checksum的结果同步主库数据到从库

./pt-table-sync --execute --charset=utf8 --replicate common.checksums --user=root --password=qazwsx$%^456 h=10.0.0.96 --database=common --port=3306

3.同步某个数据库的指定表(单表或者多表)

./pt-table-sync --execute --sync-to-master --charset=utf8 --user=root --password=qazwsx$%^456 h=10.0.0.30 --database=common --tables=tmp_order --port=3306

./pt-table-sync --execute --sync-to-master --charset=utf8 --user=root --password=qazwsx$%^456 h=10.0.0.30 --database=common --tables=tmp_order,system_shipping_method --port=3306

4.同步多个数据库

./pt-table-sync --execute --sync-to-master --charset=utf8 --user=root --password=qazwsx$%^456 h=10.0.0.30 --database=common,db1 --port=3306工具

注意事项

采用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修复剩下的从库。this