Percona Toolkit系列之十三：pt-table-sync

Percona Toolkit系列之十三：pt-table-sync

1. pt-table-sync 同步 MySQL 表数据

基本使用语法为：pt-table-sync [OPTIONS] DSN [DSN]

下面列表列出部分options，更加详细的请使用pt-table-sync --help进行查看

OPTIONS：

OPTIONS	短操作符	类型	默认值	简要说明
--ask-pass	连接 MySQL æ—¶æç¤ºè¾“å ¥å¯†ç ã€‚
--bidirectional	在第一台主机和后续主机之间启用双向同步
--[no]bin-log	记录到二进制日志
--buffer-in-mysql	指示 MySQL åœ¨å ¶å† å­˜ä¸­ç¼“å†²æŸ¥è¯¢
--[no]buffer-to-client	比较时从 MySQL 中一一获取行
--[no]check-child-tables	检查是否会对子表产生不利影响。
--[no]check-master	尝试验证检测到的 master 是真正的 master
--[no]check-slave	检查目标服务器是否为从服务器
--[no]check-triggers	检查目标表上是否未定义触发器
--database	-D	s	连接到这个数据库。
--execute	执行查询
--host	-h	s	连接到主机。
--ignore-databases	H	忽略这个以逗号分隔的数据库列表
--ignore-engines	H	忽略这个逗号分隔的存储引擎列表
--ignore-tables	H	忽略这个以逗号分隔的表格列表
--lock	i	锁定表：0=无，1=每个同步周期，2=每个表，或 3=å ¨å±€
--lock-and-rename	锁定源表和目标表，同步，然后交换名称
--password	-p	è¿žæŽ¥æ—¶ä½¿ç”¨çš„å¯†ç ã€‚å¦‚æžœå¯†ç åŒ å«é€—å·ï¼Œå®ƒä»¬å¿ é¡»ç”¨åæ–œæ è½¬ä¹‰
--port	-P	i	用于连接的端口号。
--socket	-S	用于连接的套接字文件。
--sync-to-master	将 DSN è§†ä¸ºä»Žå¹¶å°†å ¶åŒæ­¥åˆ°ä¸»ã€‚
--tables	-t	h	ä» åŒæ­¥æ­¤é€—å·åˆ†éš”çš„è¡¨åˆ—è¡¨ã€‚
--user	-u	如果不是当前用户，则用于登录的用户。
--where	s	WHERE 子句限制同步到表的一部分

类型备注：s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time

DSN为要操作的数据库和表。

DSN：

DSN	简要说明
A	默认字符集。
D	数据库。
F	ä» ä»Žç»™å®šæ–‡ä»¶ä¸­è¯»å–é»˜è®¤é€‰é¡¹
h	连接到主机。
p	è¿žæŽ¥æ—¶ä½¿ç”¨çš„å¯†ç ã€‚å¦‚æžœå¯†ç åŒ å«é€—å·ï¼Œå®ƒä»¬å¿ é¡»ç”¨åæ–œæ è½¬ä¹‰
P	用于连接的端口号。
S	用于连接的套接字文件。
u	如果不是当前用户，则用于登录的用户。

1. 基本使用样例

• 单服务器多个数据库原表与备份表的同步：同步指定表

目前，有数据库test，存在如下表t_goods：

CREATE TABLE `t_goods` (

`goodid` int NOT NULL,

`goodname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,

PRIMARY KEY (`goodid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

有数据库test_bak，存在备份表t_goods_bak：

CREATE TABLE `t_goods_bak` (

`goodid` int NOT NULL,

`goodname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,

PRIMARY KEY (`goodid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

原表：test.t_goods	备份表：test_bak.t_goods_bak
goodid	goodname	goodid	goodname
1	电视	1	电视
2	冰箱	2	冰箱
3	烤箱	3	烤箱
4	电脑	4	电脑
5	手机	5	手机
6	自行车	6	摩托车

可以看到原表和备份表有差异，需要以原表进行同步

pt-table-sync h=192.168.246.135,u=root,p=Ygzlpaul*2022,D=test,t=t_goods h=192.168.246.135,u=root,p=Ygzlpaul*2022,D=test_bak,t=t_goods_bak --execute

执行后，检查表数据，发现已经一致，都是：6 自行车

那么这个同步数据，是究竟做了什么操作呢？

我们查看日志，发现如下信息：

BEGIN

/*!*/;

# at 6936

#221120 16:53:26 server id 111 end_log_pos 7005 CRC32 0xdfbfa009 Table_map: `test_bak`.`t_goods_bak` mapped to number 100

# at 7005

#221120 16:53:26 server id 111 end_log_pos 7071 CRC32 0x07dc3de9 Update_rows: table id 100 flags: STMT_END_F

### UPDATE `test_bak`.`t_goods_bak`

### WHERE

### @1=6

### @2='摩托车'

### SET

### @1=6

### @2='自行车'

# at 7071

#221120 16:53:26 server id 111 end_log_pos 7102 CRC32 0x8abb3f76 Xid = 907

COMMIT/*!*/;

让我们将原表和备份表的数据分别改动如下：

原表：test.t_goods	备份表：test_bak.t_goods_bak
goodid	goodname	goodid	goodname
1	冰箱	1	电视
2	电视	2	冰箱
3	烤箱
4	电脑	4	电脑
5	手机	5	手机
6	自行车
7	自行车

再次同步：

pt-table-sync h=192.168.246.135,u=root,p=Ygzlpaul*2022,D=test,t=t_goods h=192.168.246.135,u=root,p=Ygzlpaul*2022,D=test_bak,t=t_goods_bak --execute

我们查看日志，发现如下信息：

BEGIN

/*!*/;

# at 8526

#221120 17:06:30 server id 111 end_log_pos 8595 CRC32 0x821534a9 Table_map: `test_bak`.`t_goods_bak` mapped to number 100

# at 8595

#221120 17:06:30 server id 111 end_log_pos 8645 CRC32 0xb68b586e Delete_rows: table id 100 flags: STMT_END_F

### DELETE FROM `test_bak`.`t_goods_bak`

### WHERE

### @1=7

### @2='自行车'

# at 8645

#221120 17:06:30 server id 111 end_log_pos 8714 CRC32 0x34b313c6 Table_map: `test_bak`.`t_goods_bak` mapped to number 100

# at 8714

#221120 17:06:30 server id 111 end_log_pos 8764 CRC32 0xec353b66 Write_rows: table id 100 flags: STMT_END_F

### INSERT INTO `test_bak`.`t_goods_bak`

### SET

### @1=6

### @2='自行车'

# at 8764

#221120 17:06:30 server id 111 end_log_pos 8833 CRC32 0xfec401d2 Table_map: `test_bak`.`t_goods_bak` mapped to number 100

# at 8833

#221120 17:06:30 server id 111 end_log_pos 8893 CRC32 0x9f48a3e2 Update_rows: table id 100 flags: STMT_END_F

### UPDATE `test_bak`.`t_goods_bak`

### WHERE

### @1=1

### @2='电视'

### SET

### @1=1

### @2='冰箱'

# at 8893

#221120 17:06:30 server id 111 end_log_pos 8962 CRC32 0x06080cbf Table_map: `test_bak`.`t_goods_bak` mapped to number 100

# at 8962

#221120 17:06:30 server id 111 end_log_pos 9022 CRC32 0x76ff056f Update_rows: table id 100 flags: STMT_END_F

### UPDATE `test_bak`.`t_goods_bak`

### WHERE

### @1=2

### @2='冰箱'

### SET

### @1=2

### @2='电视'

# at 9022

#221120 17:06:30 server id 111 end_log_pos 9053 CRC32 0x2e673e08 Xid = 1084

COMMIT/*!*/;

2.2 主从复制表同步：同步指定表

有时候，我们会在Slave上做一些操作，导致一些数据意外的发生，而引起数据的不一致性。

例如当对Slave的表做了一些修改后，数据如下：

192.168.246.135 Master库：test.t_goods	192.168.246.136 Slave库：test.t_goods
goodid	goodname	goodid	goodname
1	冰箱	1	电视
2	电视	2	冰箱
3	烤箱
4	电脑	4	电脑
5	手机	5	手机
6	自行车
7	自行车

pt-table-sync h=192.168.246.136,u=root,p=Ygzlpaul*2022,D=test,t=t_goods --sync-to-master --execute

注意：这里Host是Slave

执行后，检查表数据，发现已经一致。

我们查看Master的日志，发现如下信息：

BEGIN

/*!*/;

# at 358

#221121 11:42:08 server id 111 end_log_pos 782 CRC32 0x241f0623 Query thread_id=12 exec_time=0 error_code=0

use `test`/*!*/;

SET TIMESTAMP=1669002128/*!*/;

DELETE FROM `test`.`t_goods` WHERE `goodid`='7' LIMIT 1 /*percona-toolkit src_db:test src_tbl:t_goods src_dsn:D=test,P=3306,h=192.168.246.135,p=...,t=t_goods,u=root dst_db:test dst_tbl:t_goods dst_dsn:D=test,h=192.168.246.136,p=...,t=t_goods,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:1268 user:root host:m_master*/

/*!*/;

# at 782

#221121 11:42:08 server id 111 end_log_pos 1225 CRC32 0xa00919e8 Query thread_id=12 exec_time=0 error_code=0

SET TIMESTAMP=1669002128/*!*/;

REPLACE INTO `test`.`t_goods`(`goodid`, `goodname`) VALUES ('1', '冰箱') /*percona-toolkit src_db:test src_tbl:t_goods src_dsn:D=test,P=3306,h=192.168.246.135,p=...,t=t_goods,u=root dst_db:test dst_tbl:t_goods dst_dsn:D=test,h=192.168.246.136,p=...,t=t_goods,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:1268 user:root host:m_master*/

/*!*/;

# at 1225

#221121 11:42:08 server id 111 end_log_pos 1668 CRC32 0x4d0c2205 Query thread_id=12 exec_time=0 error_code=0

SET TIMESTAMP=1669002128/*!*/;

REPLACE INTO `test`.`t_goods`(`goodid`, `goodname`) VALUES ('2', '电视') /*percona-toolkit src_db:test src_tbl:t_goods src_dsn:D=test,P=3306,h=192.168.246.135,p=...,t=t_goods,u=root dst_db:test dst_tbl:t_goods dst_dsn:D=test,h=192.168.246.136,p=...,t=t_goods,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:1268 user:root host:m_master*/

/*!*/;

# at 1668

#221121 11:42:08 server id 111 end_log_pos 2111 CRC32 0x4da887bb Query thread_id=12 exec_time=0 error_code=0

SET TIMESTAMP=1669002128/*!*/;

REPLACE INTO `test`.`t_goods`(`goodid`, `goodname`) VALUES ('3', '烤箱') /*percona-toolkit src_db:test src_tbl:t_goods src_dsn:D=test,P=3306,h=192.168.246.135,p=...,t=t_goods,u=root dst_db:test dst_tbl:t_goods dst_dsn:D=test,h=192.168.246.136,p=...,t=t_goods,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:1268 user:root host:m_master*/

/*!*/;

# at 2111

#221121 11:42:08 server id 111 end_log_pos 2557 CRC32 0x9ed96fbf Query thread_id=12 exec_time=0 error_code=0

SET TIMESTAMP=1669002128/*!*/;

REPLACE INTO `test`.`t_goods`(`goodid`, `goodname`) VALUES ('6', '自行车') /*percona-toolkit src_db:test src_tbl:t_goods src_dsn:D=test,P=3306,h=192.168.246.135,p=...,t=t_goods,u=root dst_db:test dst_tbl:t_goods dst_dsn:D=test,h=192.168.246.136,p=...,t=t_goods,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:1268 user:root host:m_master*/

/*!*/;

# at 2557

#221121 11:42:08 server id 111 end_log_pos 2588 CRC32 0xbb552163 Xid = 76

COMMIT/*!*/;

假如我们执行如下：

pt-table-sync h=192.168.246.135,u=root,p=Ygzlpaul*2022,D=test,t=t_goods --sync-to-master --execute

192.168.246.135 是Master，执行会报告如下错误信息：

Can't determine master of D=test,h=192.168.246.135,p=...,t=t_goods,u=root at /usr/bin/pt-table-sync line 10053.