目录
正文
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。
- 如果DSN只有1个主机信息，并指定选项
- 如果指定
--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其余所有主机。
- 通过
- 有多个DSN主机：
总结：
- 如果DSN只有1个主机信息，则必须指定选项
--sync-to-master或者--replicate其中之一，否则报错； - 如果有指定选项
--replicate和--sync-to-master，或者两者其中之一，参考以上使用逻辑； - 如果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个DSN主机信息，连接的主库，加上选项
--replicate，需先进行校验，与pt-table-checksum工具配合使用； - 只使用1个DSN主机信息，连接的从库，加上选项
--sync-to-master，无需先进行校验； - 使用多个DSN主机信息，按照数据同步方向，顺序写好主机信息，根据实际需求加上选项
--no-check-slave； - 推荐使用多个DSN主机信息的方式，同时避免使用选项
--replicate和--sync-to-master，无需先进行校验，只需考虑数据同步的方向。
工作流程
因为DSN主机提供了不同的方式进行校验和同步修复方法，所以工作流程分为两种情况进行分析：
- 只使用1个DSN主机连接从库，只使用选项
--sync-to-master。- 使用多个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