使用pt-table-checksum、pt-table-sync修复Mysql主从不一致

环境
mysql版本：5.7.29
端口：3306
percona-toolkit版本2.2.20
192.168.57.200 主
192.168.57.100 从1
192.168.57.120 从2
192.168.57.121 从3

创建测试库及表
Create Table: CREATE TABLE ts (
id int(11) DEFAULT NULL
);
主
insert into ts values(1)

从2：
insert into ts values(2)；

主从上建用户
grant all privileges on . to ‘checkq’@‘192.168.57.200’ identified by ‘123456’;

修复主从复制报错：
1、使用pt-slave-restart将主从复制恢复正常(该工具只是将主从复制恢复正常，跳过报错)
pt-slave-restart --user=root --password=root --socket=/tmp/mysql.sock

2、检查及修复不一致的数据,使用pt-table-checksum，pt-table-sync

./pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=ttt.checksum --create-replicate-table --databases ttt -h 192.168.57.200 -u checkq -p 123456 -P 3306 --recursion-method=processlist --empty-replicate-table
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-14T09:41:50 0 1 1 1 0 0.030 ttt.ts

使用pt-table-sync修复数据：

1、表没有主键
–print
./pt-table-sync --ignore-databases=mysql,sys --no-check-slave dsn=u=checkq,p=123456,h=192.168.57.200,P=3306 dsn=u=checkq,p=123456,h=192.168.57.120,P=3306 --databases=ttt --tables=ts --print

DELETE FROM ttt.ts WHERE id=‘2’ LIMIT 1 /percona-toolkit src_db:ttt src_tbl:ts src_dsn:P=3306,h=192.168.57.200,p=… dst_db:ttt dst_tbl:ts dst_dsn:P=3306,h=192.168.57.120,p=… lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:3547 user:root host:mm1/;

–execute
./pt-table-sync --ignore-databases=mysql,sys --no-check-slave dsn=u=checkq,p=123456,h=192.168.57.200,P=3306 dsn=u=checkq,p=123456,h=192.168.57.120,P=3306 --databases=ttt --tables=ts --execute --print

从2上的数据id=2的备删除了
(root@localhost)-[23:41:44]-[ttt]>select * from ts;
±-----+
| id |
±-----+
| 1 |
±-----+
1 row in set (0.00 sec)

2、表有主键
–execute

./pt-table-sync --sync-to-master --charset=utf8mb4 --ignore-databases=mysql,sys --no-check-slave h=192.168.57.120,u=checkq,p=123456,P=3306 --databases=ttt --tables=ts --print
DELETE FROM ttt.ts WHERE id=‘2’ LIMIT 1 /percona-toolkit src_db:ttt src_tbl:ts src_dsn:A=utf8mb4,P=3306,h=192.168.57.200,p=…,u=checkq dst_db:ttt dst_tbl:ts dst_dsn:A=utf8mb4,P=3306,h=192.168.57.120,p=…,u=checkq lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:3583 user:root host:mm1/;

(root@localhost)-[23:51:24]-[ttt]>select * from ts;
±—+
| id |
±—+
| 1 |
| 2 |
±—+
2 rows in set (0.00 sec)

./pt-table-sync --execute --sync-to-master --charset=utf8mb4 --ignore-databases=mysql,sys --no-check-slave h=192.168.57.120,u=checkq,p=123456,P=3306 --databases=ttt --tables=ts --print
DELETE FROM ttt.ts WHERE id=‘2’ LIMIT 1 /percona-toolkit src_db:ttt src_tbl:ts src_dsn:A=utf8mb4,P=3306,h=192.168.57.200,p=…,u=checkq dst_db:ttt dst_tbl:ts dst_dsn:A=utf8mb4,P=3306,h=192.168.57.120,p=…,u=checkq lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:3587 user:root host:mm1/;

从192.168.57.120修复后数据，
(root@localhost)-[23:51:26]-[ttt]>select * from ts;
±—+
| id |
±—+
| 1 |
±—+
1 row in set (0.00 sec)

从库192.168.57.120默认端口3306端口改成3307

[root@mm1 bin]# ./pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=ttt.checksum --create-replicate-table --databases ttt -h 192.168.57.200 -u checkq -p 123456 -P 3306 --recursion-method=processlist --empty-replicate-table
Cannot connect to P=3306,h=192.168.57.120,p=…,u=checkq
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-14T10:11:34 0 0 2 1 0 0.026 ttt.ts

按照之前的方法已无法检测到从库：
解决方法：如果主库或者从库使用了非默认端口，建议通过dsn指定从库信息

在主库创建dsn表，并插入从库信息

CREATE TABLE ttt.dsns (

id int(11) NOT NULL AUTO_INCREMENT,

parent_id int(11) DEFAULT NULL,

dsn varchar(255) NOT NULL,

PRIMARY KEY (id)

);

–replicate-check-only

insert into ttt.dsns select 1,1,‘h=192.168.57.200,u=checkq,p=123456,P=3306’;
insert into ttt.dsns select 2,2,‘h=192.168.57.120,u=checkq,p=123456,P=3307’;

./pt-table-checksum --socket=/tmp/mysql.sock --user=root --password=123456 --tables=ttt.ts --replicate=ttt.checksum --no-check-binlog-format --no-check-replication-filters --recursion-method=dsn=t=ttt.dsns,h=192.168.57.120,P=3307,u=checkq,p=123456
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-14T10:41:21 0 1 2 1 0 0.038 ttt.ts

修复：
1、表没有主键
–print
./pt-table-sync --ignore-databases=mysql,sys --no-check-slave dsn=u=checkq,p=123456,h=192.168.57.200,P=3306 dsn=u=checkq,p=123456,h=192.168.57.120,P=3307 --databases=ttt --tables=ts --print

DELETE FROM ttt.ts WHERE id=‘2’ LIMIT 1 /percona-toolkit src_db:ttt src_tbl:ts src_dsn:P=3306,h=192.168.57.200,p=… dst_db:ttt dst_tbl:ts dst_dsn:P=3306,h=192.168.57.120,p=… lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:3547 user:root host:mm1/;

–execute
./pt-table-sync --ignore-databases=mysql,sys --no-check-slave dsn=u=checkq,p=123456,h=192.168.57.200,P=3306 dsn=u=checkq,p=123456,h=192.168.57.120,P=3307 --databases=ttt --tables=ts --execute --print

2、表有主键
–execute

./pt-table-sync --sync-to-master --charset=utf8mb4 --ignore-databases=mysql,sys --no-check-slave h=192.168.57.120,u=checkq,p=123456,P=3307 --databases=ttt --tables=ts --print
DELETE FROM ttt.ts WHERE id=‘2’ LIMIT 1 /percona-toolkit src_db:ttt src_tbl:ts src_dsn:A=utf8mb4,P=3306,h=192.168.57.200,p=…,u=checkq dst_db:ttt dst_tbl:ts dst_dsn:A=utf8mb4,P=3306,h=192.168.57.120,p=…,u=checkq lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:3583 user:root host:mm1/;

./pt-table-sync --execute --sync-to-master --charset=utf8mb4 --ignore-databases=mysql,sys --no-check-slave h=192.168.57.120,u=checkq,p=123456,P=3307 --databases=ttt --tables=ts --print
DELETE FROM ttt.ts WHERE id=‘2’ LIMIT 1 /percona-toolkit src_db:ttt src_tbl:ts src_dsn:A=utf8mb4,P=3306,h=192.168.57.200,p=…,u=checkq dst_db:ttt dst_tbl:ts dst_dsn:A=utf8mb4,P=3306,h=192.168.57.120,p=…,u=checkq lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:3587 user:root host:mm1/;