pt-table-sync工具的使用(二)

pt-table-sync工具的使用(二)

一 基本说明

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

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

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

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

二 实验部分

2.1 实验环境搭建与测试数据初始化步骤见【pt-table-sync工具的使用(一)】
MySQL：8.0.25
MASTER：172.17.0.31:3307
SLAVE：172.17.0.56:3307

2.2 安装termreadkey工具包
yum install -y conda
conda install conda-forge::perl-termreadkey

创建测试表employees_ptsync：

-- 表employees_ptsync信息
mysql admin@172.17.0.31:employees> create table employees_ptsync as select * from employees;
Query OK, 300024 rows affected
Time: 2.244s
mysql admin@172.17.0.31: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@172.17.0.56:employees> set sql_log_bin = 0;
Query OK, 0 rows affected
Time: 0.002s

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

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

实验一：只有1个DSN主机

场景一：只有1个DSN主机,只指定选项--sync-to-master

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

从库上执行：
# pt-table-sync h=172.17.0.56,P=3307,u=admin -- --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --dry-run
Enter password for 172.17.0.56:

# 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=3307,h=172.17.0.56,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 172.17.0.56
# 0 0 0 0 0 14:41:09 14:41:09 1 employees.employees_ptsync

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

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

-- 执行dry-run命令
# pt-table-sync h=172.17.0.56,P=3307,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --dry-run
Enter password for 172.17.0.56:

# 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=3307,h=172.17.0.56,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，所以工具并不会进行校验和同步操作。

-- 执行execute命令

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

# Syncing A=utf8,P=3307,h=172.17.0.56,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
# 0 253 0 0 Chunk 18:45:39 18:45:49 2 employees.employees_ptsync

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

-- 查询之前变更表的数据量
mysql admin@172.17.0.56: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命令前，在主库percona数据库中创建dsns表

CREATE TABLE `dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

insert into dsns values (1,1,'h=172.17.0.56,P=3307');
select * from dsns;

主库执行：
# pt-table-checksum h=172.17.0.31,P=3307,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 ...
07-13T18:47:15 Error checksumming table employees.employees_ptsync: DBI connect(';;mysql_read_default_group=client','',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at /root/percona-toolkit-3.6.0/bin/pt-table-checksum line 1639.

TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
07-13T18:47:15 1 0 300024 0 6 0 0.857 employees.employees_ptsync

场景二：只有1个DSN主机,只指定选项--replicate

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

-- 查询校验表percona.checksums信息
mysql admin@172.17.0.31:employees> select count(*) from percona.checksums;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)

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

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

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

或者

pt-table-checksum h=172.17.0.31,P=3307,u=admin --ask-pass --databases=employees --tables=employees_ptsync --no-check-binlog-format --recursion-method processlist
Enter MySQL password:
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
07-13T19:23:01 0 0 300024 0 6 0 1.252 employees.employees_ptsync

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

# Syncing via replication A=utf8,P=3307,h=172.17.0.56,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=172.17.0.56,P=3307,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --sync-to-master --transaction --verbose --execute
Enter password for 172.17.0.56:

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

-- 还是需要进行pt-table-checksum进行校验
# pt-table-checksum h=172.17.0.31,P=3307,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=172.17.0.56,P=3307,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --sync-to-master --transaction --verbose --execute
Enter password for 172.17.0.56:

# Syncing via replication A=utf8,P=3307,h=172.17.0.56,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=172.17.0.31,P=3307,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=172.17.0.31,P=3307,u=admin h=172.17.0.56,P=3307,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --execute
Enter password for 172.17.0.31:

Can't determine master of A=utf8,P=3307,h=172.17.0.31,p=...,u=admin at /usr/bin/pt-table-sync line 10020, line 1.

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

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

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

-- 还是需要进行pt-table-checksum进行校验

# pt-table-checksum h=172.17.0.31,P=3307,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=172.17.0.31,P=3307,u=admin h=172.17.0.56,P=3307,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --replicate='percona.checksums' --transaction --verbose --execute
Enter password for 172.17.0.31:
Enter password for 172.17.0.56:

# Syncing via replication A=utf8,P=3307,h=172.17.0.56,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=172.17.0.31,P=3307,u=admin h=172.17.0.56,P=3307,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=172.17.0.31,P=3307,u=admin h=172.17.0.56,P=3307,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --transaction --verbose --execute
Enter password for 172.17.0.31:
Enter password for 172.17.0.56:

# Syncing A=utf8,P=3307,h=172.17.0.56,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
Can't make changes on A=utf8,P=3307,h=172.17.0.56,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 172.17.0.56
# 0 0 0 0 0 15:19:19 15:19:19 1 employees.employees_ptsync

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

# Syncing A=utf8,P=3307,h=172.17.0.56,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操作。

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

三 实验总结

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

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