Percona Toolkit使用之pt-table-sync

     pt-table-sync的功能是有效地同步MySQL表数据。

     用法如下：

pt-table-sync [OPTIONS] DSN [DSN]

     pt-table-sync有效地于MySQL表之间同步数据。

     该工具会修改数据，因此为了尽可能安全，在使用它之前你最好备份一下你的数据。当使用--replicate或者--sync-to-master方式同步replication slave服务器时，它总是在replication master上作修改，而不是直接更改slave。一般来说这是让replica与其master同步的唯一安全的方式；直接对replica作修改通常压根就是问题的根源。然而在master上做的改动得是修改数据为其当前值的无变动更改，实际只会影响到replica。

     ①同步host1上的db.tbl到host2。

pt-table-sync --execute h=host1,D=db,t=tbl h=host2

     ②同步host1上的所有表到host2和host3。

pt-table-sync --execute host1 host2 host3

     ③让slave1与其replication master有相同的数据。

pt-table-sync --execute --sync-to-master slave1

     ④解决由pt-table-checksum（https://blog.csdn.net/sweeper_freedoman/article/details/80201492）工具检查到的master1的所有slave上的不一致。

pt-table-sync --execute --replicate test.checksum master1

     ⑤同上但只解决slave1上的不一致。

pt-table-sync --execute --replicate test.checksum \
  --sync-to-master slave1

     ⑥主主复制配置中同步master2（master2中db.tbl的拷贝已知或疑似不正确）。

pt-table-sync --execute --sync-to-master h=master2,D=db,t=tbl

     请注意在主主复制配置中，以下不会做你所期望的，因为它会将改动直接作用于master2，然后将会通过复制流回并更改master1的数据。

# Don't do this in a master-master setup!
pt-table-sync --execute h=master1,D=db,t=tbl master2

     警告：pt-table-sync修改数据！使用该工具之前，请：

• 阅读工具文档说明
• 查看工具已知BUG
• 在非生产服务器测试工具
• 备份生产服务器然后验证该备份

pt-table-sync是成熟的，在现实世界被证明，并且经过良好测试。但是如果使用不当，其会导致不良后果。请使用--dry-run和--print选项总是先测试同步。

     pt-table-sync执行表数据的单向和双向同步。它不会同步表结构、索引、或者其他框架对象。下面说明单向同步。

     该工具是复杂的，并且以若干不同的方式工作。为了安全和有效地使用它，你需要明白三点：--replicate选项的目的，查找不一致以及指定host。这三个概念密切相关并决定工具将如何运行。以下为伪逻辑。

if DSN has a t part, sync only that table:
   if 1 DSN:
      if --sync-to-master:
         The DSN is a slave.  Connect to its master and sync.
   if more than 1 DSN:
      The first DSN is the source.  Sync each DSN in turn.
else if --replicate:
   if --sync-to-master:
      The DSN is a slave.  Connect to its master, find records
      of differences, and fix.
   else:
      The DSN is the master.  Find slaves and connect to each,
      find records of differences, and fix.
else:
   if only 1 DSN and --sync-to-master:
      The DSN is a slave.  Connect to its master, find tables and
      filter with --databases etc, and sync each table to the master.
   else:
      find tables, filtering with --databases etc, and sync each
      DSN to the first.

     pt-table-sync可以以两种方式运行：带--replicate选项和不带--replicate选项。默认以不带--replicate选项运行，这将使pt-table-sync采用若干算法中的一种去有效地自动查找不一致。或者如果--replicate选项指定了值，pt-table-sync会使用已经由之前pt-table-checksum带其自身的--replicate选项运行而得的不一致。严格来说你并不需要使用--replicate选项，因为pt-table-sync可以发现不一致，但是很多人使用--replicate，例如，如果他们使用pt-table-checksum定期作校验然后使用pt-table-sync按需修复不一致。如果你不能确定，认真阅读每一个工具的文档然后自己决定，或者咨询专家。

     除了是否使用--replicate选项，你需要指定同步哪个host。有两种方式：带--sync-to-master选项和不带--sync-to-master选项。带--sync-to-master使pt-table-sync在命令行上仅接受一个且仅接受slave DSN（MySQL连接访问）。该工具会自动查找slave的master然后同步，以让数据和其master一致。这通过在master作修改然后通过复制流入来更新slave以解决其不一致来实现。但请当心：虽然该选项仅指定和同步单个slave，但是如果该master还有其他slave连接，通过复制它们也将接收到你本打算同步的slave的改动。

     另外，如果你没有指定--sync-to-master选项，命令行上给出的第一个DSN（MySQL连接访问）就是source host。只能有一个source host。如果你也没有指定--replicate选项，那么你必须至少再指定一个其他的DSN（MySQL连接访问）作为destination host。可以有一个或者多个destination host。source host和destination host之间必须要是相互独立的；它们不能处于同一个复制拓扑里。如果检测到有destination host是slave，pt-table-sync会报错死掉，因为改动会直接写入destination host（而直接写入slave是不安全的）。或者，如果你指定--replicate选项（但不是--sync-to-master选项）那么pt-table-sync在命令行上接受一个且仅接受一个master DSN（MySQL连接访问）。工具会自动查找该master的所有slave然后将它们与master同步。这是一次性同步若干（所有）slave的唯一方式（因为--sync-to-master选项只能指定一个slave）。

     命令行上的每个host都以DSN（MySQL连接访问）的方式指定。第一个DSN（或者对于像--sync-to-master的单DSN情况）为其他DSN提供默认值，无论这些DSN是于命令行上指定还是由该工具自动检测到。因此在这个示例中，host2 DSN从host1 DSN继承 u 和 p 部分。使用--explain-hosts选项来查看pt-table-sync如何解析命令行上给出的DSN。

pt-table-sync --execute h=host1,u=msandbox,p=msandbox h=host2

     当pt-table-sync以--sync-to-master选项或者--replicate选项使用时需要基于语句的复制（statement-based replication）。因此如果需要它将在其会话级别设置master的binlog_format=STATEMENT。要做此操作user必须拥有SUPER权限。

     如果指定--verbose选项，你将看到表之间的不一致信息。每张表一行。每个服务器分开打印。例如：

# Syncing h=host1,D=test,t=test1
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      3      0 Chunk     13:00:00 13:00:17 2    test.test1

host1上的test.test1表需要3条INSERT语句来同步，它使用Chunk算法。对该表的同步操作始于13:00:00结束于17秒之后（时间取自source host上的NOW()）。因为发现了不一致，其退出状态为2。

     如果指定--print选项，你将看到脚本用来同步表的实际SQL语句（如果--execute选项也被指定的话）。

     如果你想查看pt-table-sync用来查询块、nibble以及记录等的SQL语句，指定--print选项一次然后指定--verbose选项两次。但请注意：这会打印输出很多SQL语句。

     不违背唯一键约束，有INSERT、UPDATE或DELETE语句组合起来还不能解决不一致的情况。例如，假设 a 字段上有主键并且 b 字段上有一个唯一索引，那么直接用UPDATE语句是无法同步以下两张表的：

+---+---+  +---+---+
| a | b |  | a | b |
+---+---+  +---+---+
| 1 | 2 |  | 1 | 1 |
| 2 | 1 |  | 2 | 2 |
+---+---+  +---+---+

在这种情况下工具将查询重写为DELETE和REPLACE。这是在第一个索引约束被干掉后自动处理的，因此你不需要就此担心。

     在主主复制设置里使用pt-table-sync时请小心翼翼。主主复制原生就复杂所以很容易出错。你要确定对主主复制正确地使用该工具。

     同样小心有ON DELETE或ON UPDATE外键约束定义的表，因为这些可能导致子表意外改动。参考--[no]check-child-tables选项。

     一般而言，该工具最适用于当表有一个主键或者唯一索引时。虽然它也可以在没有主键或者唯一索引的表之间同步数据，但最好用其他方式同步这种数据。

     一般来说安全地同步复制架构中的master和slave并不是一个简单的问题。有各种各样的状况需要考虑，例如其他进程修改数据、尝试改动slave上的数据、源和目标host是否是一个主主对、以及更多。

     通常执行该操作的安全做法是在master上修改数据，然后让这些更改像其他更改一样通过复制流入到slave。然而，这仅在有可能对master上的表执行REPLACE写入时才有效。REPLACE只有在表上有唯一索引时才有效（否则它只扮演普通INSERT的角色）。

     如果你的表包含唯一索引，你应该使用--sync-to-master和 / 或--replicate选项来同步slave到其master。这通常会做正确的事情。当表上没有唯一索引时，除了在slave上修改数据别无选择，pt-table-sync会发现你想这么做。除非你指定--no-check-slave选项，否则它会发出抱怨然后死掉。

     如果你在一个主主对上同步没有主键或者唯一索引的表，你必须在destination服务器上修改数据。因此，为了安全性你需要指定--no-bin-log选项。如若没有，你在destination服务器上做的改动将会复制回到source服务器然后修改那里的数据。

     在主主对上一般安全的做法是使用--sync-to-master选项以便你不会修改destination服务器上的数据。你也需要指定--no-check-slave以不让pt-table-sync抱怨修改了slave上的数据。

     pt-table-sync包含一个使用不同算法来查找不一致的通用数据同步框架。工具基于索引、字段类型以及由--algorithms选项指定的算法参数选择来为每张表自动挑选最佳算法。以下是可用的算法，以默认的偏好顺序列出。

①Chunk

找出一个第一个字段是数值（包括date和time类型）的索引，然后将字段的值范围以约每--chunk-size行记录为一块分割。以整个块为单位执行checksum来逐一同步块。如果source和destination上的块不一致，对块中的每一行分别执行checksum来寻找不一致的记录。

当字段包含充足的基数（cardinality）以使块最终处于适当的大小时，这是有效的。

每个块的初始checksum相当小，产生最小的网络流量和内存消耗。如果不得不检查块中的记录，仅主键字段和一个checksum查询经网络发送而非整个记录行。如果发现一条记录不一致，才会去获取整个行，但不是于此之前。

请注意如果是分块一个所有数据以相同的字符开头的char字段，该算法将无法工作。此时，工具将退出并建议采用不同的算法。

②Nibble

找到一个索引然后以--chunk-size行记录为固定大小的nibble“攀登”该索引，使用非回溯算法，参考pt-archiver（https://www.percona.com/doc/percona-toolkit/LATEST/pt-archiver.html）了解有关该算法的更多信息。它非常类似于“Chunk”算法，但不是基于索引基数预先计算表中每个块的边界，它使用LIMIT来定义每个nibble的上限，然后之前nibble的上限来定义下限。

它逐步工作：一个查询来寻找定义下一个nibble上界的记录，然后下一个查询checksum整个nibble。如果source和destination之间的nibble不一致，它就逐行检查该nibble，正如“Chunk”算法一样。

③GroupBy

以全字段分组查询整张表，额外添加一个COUNT(*)字段。比较所有的字段，如果它们是一致的，比较COUNT(*)字段值来确定往destination里插入或者删除多少行记录。在没有主键或者唯一索引的表上工作。

④Stream

在一个巨流里查询整张表然后比较所有的字段。查询所有的字段。比其他算法效率低得多，当没有合适的索引供其使用时工作。

     以下是pt-table-sync结束和退出时的退出状态（也称作返回值或返回码）。

STATUS  MEANING
======  =======================================================
0       Success.
1       Internal error.
2       At least one table differed on the destination.
3       Combination of 1 and 2.

     以下为个人本地环境的测试数据。具体是对之前用pt-table-checksum（https://blog.csdn.net/sweeper_freedoman/article/details/80201492）查找出来的master与slave之间的数据不一致（slave少了5条数据）进行修复。

     首先是看看修复不一致要执行的语句。

root@ubuntu:~# pt-table-sync h=192.168.112.129, P=3306, u=root, p=123456 h=192.168.112.128, P=3306, u=root, p=123456 -dplayer --replicate=percona.checksums --print
REPLACE INTO `player`.`player_def`(`id`, `number`, `name`, `role`) VALUES ('1', '1', 'hachi', 'GK') /*percona-toolkit src_db:player src_tbl:player_def src_dsn:h=192.168.112.129 dst_db:player dst_tbl:player_def dst_dsn:h=192.168.112.128 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:3972 user:root host:ubuntu*/;
REPLACE INTO `player`.`player_def`(`id`, `number`, `name`, `role`) VALUES ('2', '2', 'ni', 'left back') /*percona-toolkit src_db:player src_tbl:player_def src_dsn:h=192.168.112.129 dst_db:player dst_tbl:player_def dst_dsn:h=192.168.112.128 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:3972 user:root host:ubuntu*/;
REPLACE INTO `player`.`player_def`(`id`, `number`, `name`, `role`) VALUES ('3', '3', 'san', 'center back') /*percona-toolkit src_db:player src_tbl:player_def src_dsn:h=192.168.112.129 dst_db:player dst_tbl:player_def dst_dsn:h=192.168.112.128 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:3972 user:root host:ubuntu*/;
REPLACE INTO `player`.`player_def`(`id`, `number`, `name`, `role`) VALUES ('4', '4', 'yon', 'center back') /*percona-toolkit src_db:player src_tbl:player_def src_dsn:h=192.168.112.129 dst_db:player dst_tbl:player_def dst_dsn:h=192.168.112.128 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:3972 user:root host:ubuntu*/;
REPLACE INTO `player`.`player_def`(`id`, `number`, `name`, `role`) VALUES ('5', '5', 'go', 'right back') /*percona-toolkit src_db:player src_tbl:player_def src_dsn:h=192.168.112.129 dst_db:player dst_tbl:player_def dst_dsn:h=192.168.112.128 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:3972 user:root host:ubuntu*/;
root@ubuntu:~# 

     检查发现DML没有问题（5条完整的REPLACE INTO语句），然后直接执行修复。

root@ubuntu:~# pt-table-sync h=192.168.112.129, P=3306, u=root, p=123456 h=192.168.112.128, P=3306, u=root, p=123456 -dplayer --replicate=percona.checksums --execute
root@ubuntu:~# 

     参考：

https://www.percona.com/doc/percona-toolkit/LATEST/pt-table-sync.html