Percona-Toolkit 之 pt-table-sync 总结--转发

目录

正文

pt-table-sync - Synchronize MySQL table data efficiently.

pt-table-sync synchronizes data efficiently between MySQL tables.

pt-table-sync是Percona-Toolkit工具集中的一个组件，主要用于表数据的高效同步。

因为涉及到数据的修改，所以为了安全，建议在工具修改数据前先进行备份。在主从环境下，它所做的变更都是基于主库，而不是直接修改从库，因为这是保持主从复制数据一致性最佳方式。一般情况下出现主从数据不一致都是由于从库发生了变化，因此，工具进行同步数据时并不会影响主库当前的数据，而是影响从库的数据以达到一致性。

pt-table-sync工具可以用来修复由另一个工具pt-table-checksum检查校验出主从不一致的表数据进行修复。关于工具pt-table-checksum的使用可以参考我另一篇博文：Percona-Toolkit 之 pt-table-checksum 总结。

本文是关于之前有关pt-table-checksum工具使用的学习笔记进行重新整理，使用最新版本的工具同时也进行原理知识的梳理。

关于获取和安装Percona-Toolkit工具集可以参考我另一篇博文：Percona-Toolkit 之 pt-online-schema-change 总结中的安装部分。

基本说明

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

常用选项(OPTIONS)

--ask-pass
命令行提示密码输入，保护密码安全，前提需安装模块perl-TermReadKey。

--[no]bin-log
默认值：yes
指定同步操作记录二进制日志，相当于执行SET SQL_LOG_BIN=1。如果指定'--no-bin-log'，则对应执行SET SQL_LOG_BIN=0。

--channel
指定当主从复制环境是多源复制时需要进行同步哪个主库的数据，适用于多源复制中多个主库对应一个从库的情形。

--charset，-A
指定连接字符集。

--[no]check-child-tables
默认值：yes
指定检查当同步指定选项'--execute'时是否会对同步的表的子表(如有存在)有影响。当指定选项'--replace'、'--replicate'或'--sync-to-master'时，同步使用的REPLACE语句，如果被同步的表存在子表，并且子表有'ON DELETE CASCADE'、'ON UPDATE CASCADE'或'ON UPDATE SET NULL'选项时，工具会打印报错信息并跳过该表的同步，因为REPLACE语句会先进行DELETE然后再进行INSERT操作，这会导致子表删除数据。
可以指定选项'--no-check-child-tables'不进行检查，如果同时不影响子表还需要指定选项'--no-foreign-key-checks'，这样被同步表的操作不会级联影响到它的子表。
该选项对应的检查只当指定选项'--execute'和选项('--replace'、'--replicate'和'--sync-to-master')其中之一同时使用时才会进行。指定选项'--print'并不会进行检查。

--[no]check-master
默认值：yes
指定当选项'--sync-to-master'使用时，尝试验证工具连接到的主库是否是真正的主库。

--[no]check-slave
默认值：yes
指定检查目标服务器是否是从库所在服务器。
如果目标服务器是从库，则对其进行变更是不安全的，但某些情况却必须这么做，比如当主库需要同步的表上没有唯一索引时，指定选项'--replace'是不会进行工作的，所以在这种情况下无法对主库进行变更。默认情况下如果需要对从库上进行变更，则工具会有提示，可以指定选项'--no-check-slave'禁止检查。

--[no]check-triggers
默认值：yes
指定检查目标表是否未定义触发器。

--chunk-index
指定使用哪个索引对表进行chunk分块操作。默认情况下会选择最优的索引，工具会在SQL语句中添加FORCE INDEX子句。

--chunk-size
默认值：1000
指定表分块的chunk大小，每个chunk对应的表行数，也可以是数据块大小，当指定大小时允许的后缀单位为k、M、G。

--chunk-column
指定根据表中字段对表进行chunk操作。

--host，-h
指定连接的数据库IP地址。

--port，-P
指定连接的数据库Port端口。

--user，-u
指定连接的数据库用户。

--password，-p
指定连接的数据库用户密码。

--socket，-S
指定使用SOCKET文件连接。

--databases，-d
指定需要进行同步的数据库，如有多个则用','(逗号)隔开。

--tables，-t
指定仅需要进行同步的表，如有多个则用','(逗号)隔开。表名称可以使用数据库名加以限定。

--columns，-c
指定进行比较的表字段，如有多个则用','(逗号)隔开。

--where
通过where语句条件限制表的同步内容。

--dry-run
分析、选择同步的算法，并打印信息和退出。
意味着指定选项'--verbose'可以得出工具分析的结果。分析结果的输出格式与工具实际执行时的输出一致，但是并不会有数据被影响。

--engines，-e
指定仅同步对应存储引擎的表。

--execute
指定工具执行同步操作使表数据达成一致状态。
工具使用此选项意味着将同步指定出现数据不一致的表，因此表的数据将被更改，除非指定了选项'--verbose'，否则表数据的更改将以静默的方式进行。

--explain-hosts
指定打印工具的连接信息和选项信息并退出执行。

--[no]foreign-key-checks
默认值：yes
指定进行外键约束检查，相当于执行SET FOREIGN_KEY_CHECKS=1。如果指定'--on-foreign-key-checks'，则对应执行SET FOREIGN_KEY_CHECKS=0。

--[no]unique-checks
默认值：yes
指定唯一键约束检查，相当于执行SET UNIQUE_CHECKS=1。如果指定'--on-unique-check'，则对就执行SET UNIQUE_CHECKS=0。

--function
默认值：CRC32
指定校验操作使用的哈希函数。可选函数有SHA1、MD5等。

--ignore-columns
指定需要忽略比较的字段，如有多个则用','(逗号)隔开。

--ignore-databases
指定需要忽略比较的数据库，如有多个则用','(逗号)隔开，系统数据库information_schema和performance_schema默认被忽略。

--ignore-engines
默认值：FEDERATED，MRG_MyISAM
指定需要忽略同步的存储引擎类型的表，如有多个则用','(逗号)隔开。

--ignore-tables
指定需要忽略同步的表，如有多个则用','(逗号)隔开。表名称可以使用数据库名加以限定。

--ignore-tables-regex
指定采用正则表达式匹配忽略同步的表。

--[no]index-hint
默认值：yes
指定为查询表chunk对应的行添加索引提示(FORCE/USE INDEX)。

--lock
指定哪个过程中进行锁表(LOCK TABLES)操作，主要有如下取值：
'
VALUE  MEANING
=====  ===========================================================================
0      永远不进行锁表操作；
1      每个同步周期进行锁表操作，例如锁定每次同步chunk对就的表行，这是最细粒度的锁定级别；
2      在表执行操作的时候进行锁表操作；
3      为连接的每个DSN连接的服务器进行锁表操作，指定语句FLUSH TABLES WITH READ LOCK。
'
当指定选项'--replicate'或'--sync-to-master'时，从库的表是不会被锁定的。如果指定了选项'--wait'，则主库对应的表被锁定，工具暂定执行操作直到从库追上主库才继续执行。
如果指定选项'--transaction'，则锁表操作(LOCK TABLES)不会执行，取代的方式是通过事务的开始和提交来进行锁定操作，例外情况是'--lock=3'，如果指定选项'--no-transaction'，则锁表操作(LOCK TABLES)适应所有'--lock'取值情况。

--lock-and-rename
指定锁定源表和目标表，执行同步操作，然后进行表名交换。这类似于一次轻量型的ALTER TABLE操作，它需要两个DSN来连接并假设都在同一服务器上，选项使用的是LOCK TABLES语句对表进行锁定。

--print
指定打印工具需要执行哪些查询语句来同步表，解决数据不一致，只是打印输出，并不会真正执行。

--recursion-method
默认值：processlist，hosts
指定获取从库的方式。
'
METHOD       USES
===========  =============================================
processlist  SHOW PROCESSLIST   
hosts        SHOW SLAVE HOSTS   
none         Do not find slaves
==========================================================
'
processlist：通过SHOW PROCESSLIST方式找到slave，为默认方式，当SHOW SLAVE HOSTS不可用时。一旦实例运行在非3306端口上时，hosts方式就会变为默认方式；
hosts：通过SHOW SLAVE HOSTS方式找到slave，hosts方式要求从库配置'--report_host'和'--report_port'这两个参数。

--replace
指定所有的INSERT和UPDATE语句改写为REPLACE语句。如果数据有违反唯一性约束，则工具会自动进行改写。

--replicate
指定参照该选项中列出的表进行表同步操作。
工具将在该选项指定的表中去查询数据不一致表的信息并进行同步操作，这个选项跟工具pt-table-checksum当中的同名选项是相同意义的。该选项会自动设置选项'--wait=60'并确保在主库进行变更。
如果指定选项'--sync-to-master'，则工具会假设指定的连接是从库，并会寻找主库连接进行表数据同步操作，如果指定的连接不是从库，工具将使用选项'--recursion-method'中查找从库方法进行查找操作，然后再找到需要进行同步操作的表。

--set-vars
默认：
    wait_timeout=10000
    innodb_lock_wait_timeout=1
    lock_wait_timeout=60
运行检查时指定参数值，如有多个用','(逗号)分隔。如'--set-vars=wait_timeout=5000'。

--sync-to-master
指定将DSN连接信息确认为从库，并同步信息到主库。
该选项将指定的服务器当作是从库，并且检查从库的主库，连接主库。将主库作为数据同步的源端，从库作为数据同步的目标端进行同步。选项会默认设置选项'--wait=60'和'--lock=1'，并且设置选项'--no-transaction'。

--timeout-ok
指定当选项'--wait'导致工具执行失败时跳过失败继续执行。
如果指定了选项'--wait'，但是从库在指定时间内还是没能追上主库且依然存在主从延迟，则工具将中止操作并退出。指定选项'--timeout-ok'则会继续执行操作。

--[no]transaction
指定工具操作使用事务代替LOCK TABLES语句进行锁表。
事务开始和提交的锁粒度由选项'--lock'控制，这是默认的状态，但是选项'--lock'默认是关闭的，所以并不会生效。大多数选项如果指定锁定则默认也会禁止使用事务方式，如果想使用基于事务的锁定(如通过语句LOCK IN SHARE MODE或FOR UPDATE)，则需要显式指定选项'--transaction'。如果选项'--transaction'没有显式指定，则工具根据表类型来决定是基于事务锁定还是使用语句LOCK TABLES进行锁定，默认情况是InnoDB表使用事务，而其他类型表使用LOCK TABLES。如果指定了选项'--no-transaction'，则工具在执行过程中都不会开启事务(即使操作的InnoDB引擎的表)，表的锁定由选项'--lock'控制。
无论是显式指定该选项或者是隐式触发该选项，则都会开启隔离级别为REPEATABLE READ的一致性快照事务(WITH CONSISTENT SNAPSHOT)。

--verbose，-v
指定打印更详细的操作信息。

--version
显示工具的版本并退出。

--[no]version-check
默认值：yes
检查Percona Toolkit、MySQL和其他程序的最新版本。

--wait，-w
指定存在主从复制延迟时从库可以等待多长时间追上主库，如果超过时间依然存在延迟就中止退出(除非指定选项'--timeout-ok')。

DSN选项(DSN)

可以使用DSN方式来连接数据库，DSN选项为key=value方式，在等号的两侧不能有空格出现，并且区分大小写，多个选项之前以','(逗号)隔开，主要选项如下：

  • A
    指定字符集
  • D
    指定需要同步数据库
  • t
    指定需要同步的表
  • h
    指定要连接的HOST
  • P
    指定要连接的PORT
  • S
    指定连接所使用的SOCKET文件(Unix systems)
  • u
    指定连接的用户名
  • p
    指定连接的用户名密码

示例：
h=192.168.58.3,P=3306,D=employees,t=employees

--replicate选项说明

pt-table-sync工具使用起来有点复杂，它可以通过很多不同方式起作用，其中选项--replicate的使用对于工具至关重要，以下关于该选项的使用作进一步的说明。

该选项的使用逻辑如下：

  • 如果不指定--replicate选项，DSN选项中有涉及到表选项t，则只同步指定的表：
    • 如果DSN只有1个主机信息，并指定选项--sync-to-master：
      • 如果DSN代表的是从库，工具也会连接它的主库并且同步；
      • 如果DSN代表的是主库，工具会报错找不到主库。
    • 如果DSN里大于1个主机信息：
      • 第1个DSN主机是源端数据库(并不区分主库和从库)，按顺序向之后DSN主机同步。如果第一个是DSN主机是从库的话会将从库的数据同步到主库；
      • 如果第1个DSN主机是主库，确保同步的表有唯一索引以便在主库执行REPLACE变更操作，并指定选项--no-check-slave。
  • 如果指定--replicate选项，证明已经存在保存数据差异结果的表(可以先使用工具pt-table-checksum进行校验)：
    • 指定--sync-to-master选项：
      • 当这两个选项一起使用时，只允许有1个DSN主机，否则工具会报错退出。DSN代表的是从库。工具会连接它的主库，找出差异数据并进行同步修复。
    • 不指定--sync-to-master选项：
      • DSN代表的是主库。工具会找出所有从库并连接，找出差异数据并进行同步修复。
  • 其他情况，不指定选项--replicate和--sync-to-master：
    • 有多个DSN主机：
      • 通过--databases或者--ignore-databases等过滤选项找出指定数据库所有表数据的差异，以DSN第1个主机为主，并同步差异到DSN其余所有主机。

总结：

  1. 如果DSN只有1个主机信息，则必须指定选项--sync-to-master或者--replicate其中之一，否则报错；
  2. 如果有指定选项--replicate和--sync-to-master，或者两者其中之一，参考以上使用逻辑；
  3. 如果DSN不只1个主机信息，并且都没有指定选项--replicate和--sync-to-master，则以第1个主机为主，同时指定选项--no-check-slave，在其余主机上同步差异数据。

复制安全

对于主从数据库的复制环境来说，当出现数据不一致的情形，有时候需要判断哪一个库的数据才是最终确定的数据。但大部分情况都希望在主库执行想应的数据同步变更，这样可以同时将变更同步到从库，既修复了从库数据的不一致性，也保证了变更在从库上得到执行。这样的情况只有在主库上的表有唯一键(主键)时并执行REPLACE语句时才会生效，否则REPLACE语句就像普通的INSERT语句，会导致主库插入的数据重复。
如果表上具有唯一键(主键)时，对于主从复制架构来说，最理想的做法是指定选项--replicate或--sync-to-master将同步需要执行的变更语句放在主库上执行，并将变更的操作通过主从复制传递给从库来执行。如果表上没有唯一键，则变更只好在从库进行，但前提需指定选项--no-check-slave。
对于是主主复制架构来说，变更操作必须在目标端数据库进行，在变更的同时需要指定选项--no-bin-log，即变更的操作不写入binlog中，否则变更操作会反向复制到另一台主库中执行变更操作，造成数据的不一致。

使用限制

当pt-table-sync工具指定选项--replicate和--sync-to-master进行数据同步操作时会使用基于语句(binlog_format=STATEMENT)的方式进行，所以需确保执行操作的用户拥有SUPER权限。

用法示例

环境与数据准备

MySQL：5.7.24
MASTER：192.168.58.3:3306
SLAVE：192.168.58.5:3306

本文基于MySQL官方示例数据库employee：Example Databases进行测试。

创建测试表employees_ptsync：

-- 表employees_ptsync信息
mysql admin@192.168.58.3:employees> create table employees_ptsync as select * from employees;
Query OK, 300024 rows affected
Time: 2.244s
mysql admin@192.168.58.3:employees> show create table employees_ptsync;
+------------------+--------------------------------------+
| Table            | Create Table                         |
+------------------+--------------------------------------+
| employees_ptsync | CREATE TABLE `employees_ptsync` (    |
|                  |   `emp_no` int(11) NOT NULL,         |
|                  |   `birth_date` date NOT NULL,        |
|                  |   `first_name` varchar(14) NOT NULL, |
|                  |   `last_name` varchar(16) NOT NULL,  |
|                  |   `gender` enum('M','F') NOT NULL,   |
|                  |   `hire_date` date NOT NULL          |
|                  | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------+--------------------------------------+
1 row in set
Time: 0.025s

-- 从库更新部分数据，使得主从出现数据不一致
mysql admin@192.168.58.5:employees> set sql_log_bin = 0;
Query OK, 0 rows affected
Time: 0.002s

mysql admin@192.168.58.5:employees> update employees_ptsync set first_name = 'Georgi_ptsync' where first_name = 'Georgi';
Query OK, 252 rows affected
Time: 0.248s

mysql admin@192.168.58.5:employees> set sql_log_bin = 1;
Query OK, 0 rows affected
Time: 0.002s

只有1个DSN主机

  • 只指定选项--sync-to-master

因为只有1个DSN主机并且指定了选项--sync-to-master，则DSN主机对应为从库的连接串，先使用选项--dry-run查看执行信息。

# pt-table-sync h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --dry-run
Enter password for 192.168.58.5:

# NOTE: --dry-run does not show if data needs to be synced because it
#       does not access, compare or sync data.  --dry-run only shows
#       the work that would be done.
# Syncing A=utf8,P=3306,h=192.168.58.5,p=...,u=admin in dry-run mode, without accessing or comparing data
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
Can't make changes on the master because no unique index exists at /usr/bin/pt-table-sync line 10857.  while doing employees.employees_ptsync on 192.168.58.5
#      0       0      0      0 0         14:41:09 14:41:09 1    employees.employees_ptsync

因为表employees_ptsync没有任何索引的信息，以上报错提示表没有唯一键，先为表添加主键并再次执行。

-- 添加主键
mysql admin@192.168.58.3:employees> alter table employees_ptsync add primary key(emp_no);
Query OK, 0 rows affected
Time: 2.537s

-- 再次执行
# pt-table-sync h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --dry-run
Enter password for 192.168.58.5:

# NOTE: --dry-run does not show if data needs to be synced because it
#       does not access, compare or sync data.  --dry-run only shows
#       the work that would be done.
# Syncing A=utf8,P=3306,h=192.168.58.5,p=...,u=admin in dry-run mode, without accessing or comparing data
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 Chunk     14:42:55 14:42:55 0    employees.employees_ptsync

从以上输出信息可得知并无报错信息，但因为使用了选项--dry-run，所以工具并不会进行校验和同步操作。

将选项--dry-run换成选项--execute再次执行。

# pt-table-sync h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --execute
Enter password for 192.168.58.5:

# Syncing A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0     252      0      0 Chunk     14:44:18 14:44:28 2    employees.employees_ptsync

可以看出在REPLACE字段变更了252行记录，跟之前构建从库的数据行数一致，可以查询从库刚才表更新数据的情况进行验证：

-- 查询之前变更表的数据量
mysql admin@192.168.58.5:employees> select count(*) from employees_ptsync where first_name = 'Georgi_ptsync';
+----------+
| count(*) |
+----------+
| 0        |
+----------+
1 row in set
Time: 0.197s

-- 可以再使用pt-table-checksum进行验证
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptsync --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns
Enter MySQL password:

Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
04-09T14:47:09      0      0   300024          0       5       0   0.807 employees.employees_ptsync
  • 只指定选项--replicate

如果是只指定选项--replicate，则DSN主机对应的为主库的连接串，在这之前选项--replicate指定的表有保存之前数据不一致的校验结果，可以先通过工具pt-table-checksum进行校验，否则并不会进行同步变更修复。

-- 查询校验表percona.checksums信息
mysql admin@192.168.58.3:employees> select count(*) from percona.checksums;
+----------+
| count(*) |
+----------+
| 0        |
+----------+
1 row in set
Time: 0.022s

-- 执行同步操作
# pt-table-sync h=192.168.58.3,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --transaction --verbose --execute
Enter password for 192.168.58.3:

# Syncing via replication A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE

-- 先使用pt-table-checksum校验
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptsync --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns
Enter MySQL password:

Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
04-09T15:04:04      0      3   300024          0       5       0   0.766 employees.employees_ptsync

-- 再次执行同步操作
# pt-table-sync h=192.168.58.3,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --transaction --verbose --execute
Enter password for 192.168.58.3:

# Syncing via replication A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       1      0      0 Chunk     15:05:51 15:05:52 2    employees.employees_ptsync
#      0      93      0      0 Chunk     15:05:52 15:05:55 2    employees.employees_ptsync
#      0     158      0      0 Chunk     15:05:55 15:06:01 2    employees.employees_ptsync
  • 同时指定选项--sync-to-master和--replicate

因为选项--sync-to-master出现，所以DSN主机对应的为从库的连接串，默认之前还是没有进行校验。

-- 执行同步操作
# pt-table-sync h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --sync-to-master --transaction --verbose --execute
Enter password for 192.168.58.5:

# Syncing via replication A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE

-- 还是需要进行pt-table-checksum进行校验
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptsync --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns
Enter MySQL password:

Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
04-09T15:08:00      0      4   300024          0       6       0   0.926 employees.employees_ptsync

-- 再次执行同步操作
# pt-table-sync h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --sync-to-master --transaction --verbose --execute
Enter password for 192.168.58.5:

# Syncing via replication A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       1      0      0 Chunk     15:10:29 15:10:29 2    employees.employees_ptsync
#      0      28      0      0 Chunk     15:10:29 15:10:30 2    employees.employees_ptsync
#      0     114      0      0 Chunk     15:10:30 15:10:35 2    employees.employees_ptsync
#      0     109      0      0 Chunk     15:10:35 15:10:40 2    employees.employees_ptsync
  • 不指定选项--sync-to-master和--replicate

当DSN只有1个主机的时候，必须指定至少其中一个选项，否则工具报错退出。

# pt-table-sync h=192.168.58.3,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --transaction --verbose --execute
Usage: pt-table-sync [OPTIONS] DSN [DSN]

Errors in command-line arguments:
  * At least one DSN is required, and at least two are required unless --sync-to-master or --replicate is specified

pt-table-sync synchronizes data efficiently between MySQL tables.  For more
details, please use the --help option, or try 'perldoc /usr/bin/pt-table-sync'
for complete documentation.

有多个DSN主机

  • 只指定选项--sync-to-master

因为有多个DSN主机，必须确保所列出DSN主机均为从库，否则工具报错退出。

# pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --execute
Enter password for 192.168.58.3:

Can't determine master of A=utf8,P=3306,h=192.168.58.3,p=...,u=admin at /usr/bin/pt-table-sync line 10020, <STDIN> line 1.
  • 只指定选项--replicate

如果只指定选项--replicate，也必须之前做过校验操作。

-- 执行同步操作
# pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --transaction --verbose --execute
Enter password for 192.168.58.3:
Enter password for 192.168.58.5:

# Syncing via replication A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE

-- 还是需要进行pt-table-checksum进行校验
# pt-table-checksum h=192.168.58.3,P=3306,u=admin --ask-pass --databases=employees --tables=employees_ptsync --no-check-binlog-format --recursion-method dsn=D=percona,t=dsns
Enter MySQL password:

Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
04-09T15:15:46      0      3   300024          0       5       0   0.805 employees.employees_ptsync

-- 再次执行同步操作
# pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --transaction --verbose --execute
Enter password for 192.168.58.3:
Enter password for 192.168.58.5:

# Syncing via replication A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       1      0      0 Chunk     15:15:53 15:15:53 2    employees.employees_ptsync
#      0      86      0      0 Chunk     15:15:53 15:15:57 2    employees.employees_ptsync
#      0     165      0      0 Chunk     15:15:57 15:16:03 2    employees.employees_ptsync
  • 同时指定选项--sync-to-master和--replicate

当同时指定这两个选项时，DSN主机只允许有一个，否则工具报错退出。

# pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --sync-to-master --transaction --verbose --execute
Usage: pt-table-sync [OPTIONS] DSN [DSN]

Errors in command-line arguments:
  * --sync-to-master and --replicate require only one DSN

pt-table-sync synchronizes data efficiently between MySQL tables.  For more
details, please use the --help option, or try 'perldoc /usr/bin/pt-table-sync'
for complete documentation.
  • 不指定选项--sync-to-master和--replicate

因为都不指定这两个选项，所以DSN主机的顺序必须格外注意，最好是先写主库再写从库，或者根据同步的方向来确定。同时如果需要做同步变更修复的表上没有唯一键(主键)，最好都指定选项--no-check-slave直接在从库进行变更修复。

# pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --transaction --verbose --execute
Enter password for 192.168.58.3:
Enter password for 192.168.58.5:

# Syncing A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
Can't make changes on A=utf8,P=3306,h=192.168.58.5,p=...,u=admin because it's a slave. See the documentation section 'REPLICATION SAFETY' for solutions to this problem. at /usr/bin/pt-table-sync line 10878.  while doing employees.employees_ptsync on 192.168.58.5
#      0       0      0      0 0         15:19:19 15:19:19 1    employees.employees_ptsync

-- 加上选项--no-check-slave
# pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --no-check-slave --transaction --verbose --execute
Enter password for 192.168.58.3:
Enter password for 192.168.58.5:

# Syncing A=utf8,P=3306,h=192.168.58.5,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0    252 Chunk     15:20:45 15:20:52 2    employees.employees_ptsync

可以看出如果在从库进行变更修复，执行的是UPDATE操作。

输出说明

工具同步执行操作的输出解析如下：

字段名说明
DELETE 删除的行数
REPLACE 替换的行数
INSERT 插入的行数
UPDATE 更新的行数
ALGORITHM 校验使用的算法
START 执行开始的时间
END 执行结束的时间
EXIT 退出状态码
DATABASE.TABLE 涉及的数据库.表

关于退出状态码的说明如下：

STATUS  MEANING
======  ========================================================================================
0       工具没有执行同步变更操作并成功退出。(Success)
1       工具出现内部错误。(Internal error)
2       至少有一张表校验有不一致并进行同步变更修复。(At least one table differed on the destination)
3       状态1和状态2共同出现的情况。(Combination of 1 and 2)

注意事项

  1. 只使用1个DSN主机信息，连接的主库，加上选项--replicate，需先进行校验，与pt-table-checksum工具配合使用；
  2. 只使用1个DSN主机信息，连接的从库，加上选项--sync-to-master，无需先进行校验；
  3. 使用多个DSN主机信息，按照数据同步方向，顺序写好主机信息，根据实际需求加上选项--no-check-slave；
  4. 推荐使用多个DSN主机信息的方式，同时避免使用选项--replicate和--sync-to-master，无需先进行校验，只需考虑数据同步的方向。

工作流程

因为DSN主机提供了不同的方式进行校验和同步修复方法，所以工作流程分为两种情况进行分析：

  1. 只使用1个DSN主机连接从库，只使用选项--sync-to-master。
  2. 使用多个DSN主机同时连接主库和从库，只使用选项--replicate，需先使用工具pt-table-checksum校验。

还是通过general log记录操作来了解。

  • 第1种情形：

为了减少general log的篇幅，加上选项--chunk-size=50000。

# pt-table-sync h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --chunk-size=50000 --verbose --execute

-- 开启会话线程167进行初始的一些检查数据库参数、负载信息这里不再细说
167 Connect	admin@dbabd1 on  using TCP/IP
167 Query	set autocommit=0
167 Query	/*!40101 SET NAMES "utf8"*/
167 Query	SHOW VARIABLES LIKE 'wait\_timeout'
167 Query	SET SESSION wait_timeout=10000
167 Query	SELECT @@SQL_MODE
167 Query	SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
167 Query	/*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
167 Query	SHOW VARIABLES LIKE 'version%'
167 Query	SHOW ENGINES
167 Query	SHOW VARIABLES LIKE 'innodb_version'

-- 设置binlog_format=STATEMENT
167 Query	SELECT @@binlog_format
167 Query	/*!50108 SET @@binlog_format := 'STATEMENT'*/

-- 设置会话级隔离级别为REPEATABLE READ
167 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
167 Query	SHOW MASTER STATUS

-- 查询当前连接用户的权限
167 Query	SHOW GRANTS FOR CURRENT_USER()
167 Query	SHOW FULL PROCESSLIST
167 Query	SHOW VARIABLES LIKE 'port'
167 Query	SELECT CONCAT(@@hostname, @@port)

-- 开启别一个会话线程168持续检查状态
168 Connect	admin@dbabd1 on  using TCP/IP
168 Query	set autocommit=0
168 Query	/*!40101 SET NAMES "utf8"*/
168 Query	SHOW VARIABLES LIKE 'wait\_timeout'
168 Query	SET SESSION wait_timeout=10000
168 Query	SELECT @@SQL_MODE
168 Query	SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
168 Query	/*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
168 Query	SHOW VARIABLES LIKE 'version%'
168 Query	SHOW ENGINES
168 Query	SHOW VARIABLES LIKE 'innodb_version'
168 Query	SELECT @@binlog_format
168 Query	/*!50108 SET @@binlog_format := 'STATEMENT'*/
168 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

-- 会话线程167查询当前数据库和需要操作表的状态信息，表结构信息等，记录开始时间
167 Query	SHOW DATABASES
167 Query	SHOW /*!50002 FULL*/ TABLES FROM `employees`
167 Query	/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
167 Query	USE `employees`
167 Query	SHOW CREATE TABLE `employees`.`employees_ptsync`
167 Query	/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
167 Query	SELECT NOW()

-- 查询需要操作表是否被外键约束
167 Query	SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='employees' AND referenced_table_name='employees_ptsync'
167 Query	SET NAMES 'utf8'

-- 确定需要操作表下边界和上边界值，依赖主键(唯一键)
167 Query	SELECT MIN(`emp_no`), MAX(`emp_no`) FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`)
167 Query	EXPLAIN SELECT * FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`)
167 Query	SELECT CRC32('test-string')
167 Query	SELECT CRC32('a')
167 Query	SELECT CRC32('a')

-- 开启执行校验，并同步进行不一致的同步修复，每执行一次chunk校验和变更都开启了一致性快照事务
167 Query	USE `employees`
167 Query	SET @crc := '', @cnt := 0
167 Query	commit
167 Query	START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
167 Query	SELECT /*employees.employees_ptsync:1/7*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` = 0) FOR UPDATE
168 Query	SHOW MASTER STATUS
167 Query	SET @crc := '', @cnt := 0
167 Query	commit
167 Query	START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
167 Query	SELECT /*employees.employees_ptsync:2/7*/ 1 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` > 0 AND `emp_no` < '91801') FOR UPDATE
168 Query	SHOW MASTER STATUS
167 Query	SET @crc := '', @cnt := 0
167 Query	SELECT /*rows in chunk*/ `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`, CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS __crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` > 0 AND `emp_no` < '91801') ORDER BY `emp_no` FOR UPDATE
167 Query	SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='10909' LIMIT 1
167 Query	REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('10909', '1954-11-11', 'Georgi', 'Atchley', 'M', '1985-04-21') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:18953 user:root host:dbabd*/
167 Query	SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='11029' LIMIT 1
167 Query	REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('11029', '1962-07-12', 'Georgi', 'Itzfeldt', 'M', '1992-12-27') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:18953 user:root host:dbabd*/

……省略……

-- 直到最后一个chunk校验完并进行修复，可以看出在校验过程中为这些加了行锁，使用语句FOR UPDATE
167 Query	START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
167 Query	SELECT /*employees.employees_ptsync:7/7*/ 6 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` >= '419001') FOR UPDATE
168 Query	SHOW MASTER STATUS
167 Query	SET @crc := '', @cnt := 0
167 Query	SELECT /*rows in chunk*/ `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`, CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS __crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` >= '419001') ORDER BY `emp_no` FOR UPDATE
167 Query	SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='420266' LIMIT 1
167 Query	REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('420266', '1955-03-30', 'Georgi', 'Armand', 'M', '1998-06-13') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:18953 user:root host:dbabd*/
167 Query	SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='420295' LIMIT 1
167 Query	commit

-- 记录结束时间，并退出
167 Query	SELECT NOW()
167 Query	commit
167 Quit	
168 Query	commit
168 Quit

流程总结：

  1. 开启两个会话连接，一个会话负责校验同步，一个会话负责持续检查服务器状态信息；
  2. 连接DSN主机对应的主库，检查当前服务器负载信息，参数设置信息，关闭自动提交功能；
  3. 设置二进制日志格式为STATEMENT，设置会话级别隔离级别为REPEATABLE READ；
  4. 检查当前连接用户的权限，检查操作表是否被外键约束；
  5. 通过主键或唯一键(如有)对表进行chunk上边界和下边界的确定，以便更好进行chunk分块操作；
  6. 表被分成多个chunk进行校验和同步修复，chunk大小由选项--chunk-size控制；
  7. 完成所有的chunk校验和同步修复，退出。
  • 第2种情形

需要先进行pt-table-checksum校验操作，这里不再说明。

pt-table-sync h=192.168.58.3,P=3306,u=admin h=192.168.58.5,P=3306,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --transaction --chunk-size=50000 --verbose --execute

-- 开启会话线程186进行初始的一些检查数据库参数、负载信息这里不再细说
186 Connect	admin@dbabd1 on  using TCP/IP
186 Query	set autocommit=0
186 Query	/*!40101 SET NAMES "utf8"*/
186 Query	SHOW VARIABLES LIKE 'wait\_timeout'
186 Query	SET SESSION wait_timeout=10000
186 Query	SELECT @@SQL_MODE
186 Query	SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
186 Query	/*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
186 Query	SHOW VARIABLES LIKE 'version%'
186 Query	SHOW ENGINES
186 Query	SHOW VARIABLES LIKE 'innodb_version'

-- 设置binlog_format=STATEMENT
186 Query	SELECT @@binlog_format
186 Query	/*!50108 SET @@binlog_format := 'STATEMENT'*/

-- 设置会话级隔离级别为REPEATABLE READ
186 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

-- 开启别一个会话线程187持续检查状态
187 Connect	admin@dbabd1 on  using TCP/IP
187 Query	set autocommit=0
187 Query	/*!40101 SET NAMES "utf8"*/
187 Query	SHOW VARIABLES LIKE 'wait\_timeout'
187 Query	SET SESSION wait_timeout=10000
187 Query	SELECT @@SQL_MODE
187 Query	SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
187 Query	/*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
187 Query	SHOW VARIABLES LIKE 'version%'
187 Query	SHOW ENGINES
187 Query	SHOW VARIABLES LIKE 'innodb_version'
187 Query	SELECT @@binlog_format
187 Query	/*!50108 SET @@binlog_format := 'STATEMENT'*/
187 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

-- 会话线程186查询选项--replicate指定表确定不一致的信息，包括表chunk信息等
186 Query	SELECT @@SERVER_ID
186 Query	SELECT db, tbl, CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM percona.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)

-- 查询当前连接用户的权限，记录开始时间，表结构信息等，检查是否存在外键约束
186 Query	SHOW GRANTS FOR CURRENT_USER()
186 Query	SHOW FULL PROCESSLIST
186 Query	SELECT NOW()
186 Query	/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
186 Query	USE `employees`
186 Query	SHOW CREATE TABLE `employees`.`employees_ptsync`
186 Query	/*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
186 Query	SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='employees' AND referenced_table_name='employees_ptsync'
186 Query	SET NAMES 'utf8'

187 Query	SET NAMES 'utf8'

-- 确定首个chunk需要操作表下边界和上边界值，依赖主键(唯一键)
186 Query	SELECT MIN(`emp_no`), MAX(`emp_no`) FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (((`emp_no` >= '10001')) AND ((`emp_no` <= '11000')))
186 Query	EXPLAIN SELECT * FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE ((`emp_no` >= '10001')) AND ((`emp_no` <= '11000'))
186 Query	SELECT CRC32('test-string')
186 Query	SELECT CRC32('a')
186 Query	SELECT CRC32('a')

-- 获取chunk中表行数据进行校验，并同步进行不一致的同步修复，每执行一次chunk校验和变更都开启了一致性快照事务
186 Query	USE `employees`
186 Query	SET @crc := '', @cnt := 0
186 Query	commit
186 Query	START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
186 Query	SELECT /*employees.employees_ptsync:1/4*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` = 0) AND ((((`emp_no` >= '11001')) AND ((`emp_no` <= '242216')))) FOR UPDATE
187 Query	SHOW MASTER STATUS
186 Query	SET @crc := '', @cnt := 0
186 Query	commit
186 Query	START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
186 Query	SELECT /*employees.employees_ptsync:2/4*/ 1 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` > 0 AND `emp_no` < '88199') AND ((((`emp_no` >= '11001')) AND ((`emp_no` <= '242216')))) FOR UPDATE
187 Query	SHOW MASTER STATUS
186 Query	SET @crc := '', @cnt := 0
186 Query	SELECT /*rows in chunk*/ `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`, CRC32(CONCAT_WS('#', `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`)) AS __crc FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (`emp_no` > 0 AND `emp_no` < '88199') AND (((`emp_no` >= '11001')) AND ((`emp_no` <= '242216'))) ORDER BY `emp_no` FOR UPDATE
186 Query	SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='11029' LIMIT 1
186 Query	REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('11029', '1962-07-12', 'Georgi', 'Itzfeldt', 'M', '1992-12-27') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:26323 user:root host:dbabd*/
186 Query	SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='11430' LIMIT 1
186 Query	REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('11430', '1957-01-23', 'Georgi', 'Klassen', 'M', '1996-02-27') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:26323 user:root host:dbabd*/

……省略……

-- 首个chunk执行结束，记录结束时间
186 Query	SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='239542' LIMIT 1
186 Query	REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('239542', '1957-06-20', 'Georgi', 'Barriga', 'F', '1994-04-28') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:26323 user:root host:dbabd*/
186 Query	commit
186 Query	SELECT NOW()

-- 确定接下去chunk需要操作表下边界和上边界值，依赖主键(唯一键)
186 Query	SELECT NOW()
186 Query	SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='employees' AND referenced_table_name='employees_ptsync'
186 Query	SET NAMES 'utf8'
187 Query	SET NAMES 'utf8'
186 Query	SELECT MIN(`emp_no`), MAX(`emp_no`) FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE (((`emp_no` >= '242217')) AND ((`emp_no` <= '499999')))
186 Query	EXPLAIN SELECT * FROM `employees`.`employees_ptsync` FORCE INDEX (`PRIMARY`) WHERE ((`emp_no` >= '242217')) AND ((`emp_no` <= '499999'))
186 Query	SELECT CRC32('test-string')
186 Query	SELECT CRC32('a')
186 Query	SELECT CRC32('a')

……省略……

-- 完成最后chunk的校验并进行不一致的同步修复
186 Query	SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='498809' LIMIT 1
186 Query	REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('498809', '1961-10-24', 'Georgi', 'Ferriere', 'M', '1993-02-21') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:26323 user:root host:dbabd*/
186 Query	SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees_ptsync` WHERE `emp_no`='499814' LIMIT 1
186 Query	REPLACE INTO `employees`.`employees_ptsync`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES ('499814', '1960-12-28', 'Georgi', 'Wielonsky', 'M', '1989-01-31') /*percona-toolkit src_db:employees src_tbl:employees_ptsync src_dsn:A=utf8,P=3306,h=192.168.58.3,p=...,u=admin dst_db:employees dst_tbl:employees_ptsync dst_dsn:A=utf8,P=3306,h=192.168.58.5,p=...,u=admin lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:26323 user:root host:dbabd*/
186 Query	commit

-- 记录结束时间，并退出
186 Query	SELECT NOW()
186 Query	commit
186 Quit	
187 Query	commit
187 Quit

工作流程与第1种情形类似，只不过在处理chunk时使用的是选项--replicate指定表中的记录情况，而不是实时进行chunk处理，其他操作流程与第1种情形一致。

总结

pt-table-sync工具作为一款高效的数据同步工具，通常与pt-table-checksum工具一起使用，但是它涉及到数据的更改，在使用时最好先进行测试之后再对线上服务器进行操作，虽然对生产服务器的性能影响服务较小，但是在进行校验分析的同时会对操作的表行执行FOR UPDATE语句进行锁定，所以尽量选择在业务低峰期进行操作，同时避免在高并发场景下进行操作数据以免造成阻塞。

本文只说明了pt-table-sync工具的单向同步方式，其实它也支持双向同步，只是有许多限制条件并且也处在一个测试功能的阶段，更多关于pt-table-sync的说明可以参考官方说明：pt-table-sync

参考

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

☆〖本人水平有限，文中如有错误还请留言批评指正！〗☆

posted @ 2021-08-04 14:25  北方客888  阅读(260)  评论(0)  编辑  收藏  举报