pt-table-sync修复主从库不一致数据

一、实例演示1

模拟主从库数据不一致环境:

192.168.0.39 master库:
mysql> select * from test01.frame01;;
+----+-----------+------------------------------------------+
| id | parent_id | dsn                                      |
+----+-----------+------------------------------------------+
|  1 |         1 | 192.168.0.22,u=pt22,p=ptchecksums,P=3307 |
|  2 |         2 | 192.168.0.33,u=pt33,p=ptchecksums,P=3308 |
+----+-----------+------------------------------------------+
2 rows in set (0.00 sec)

192.168.0.39 slave库:
mysql> update frame01 set dsn='192.168.0.55,u=umaaa,p=ptchsyeudew,P=3310' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update frame01 set dsn='192.168.0.66,u=umbbb,p=ptchsyeudew,P=3311' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from frame01;
+----+-----------+-------------------------------------------+
| id | parent_id | dsn                                       |
+----+-----------+-------------------------------------------+
|  1 |         1 | 192.168.0.55,u=umaaa,p=ptchsyeudew,P=3310 |
|  2 |         2 | 192.168.0.66,u=umbbb,p=ptchsyeudew,P=3311 |
+----+-----------+-------------------------------------------+

pt-table-sync校验修复数据具体指令:

[root@kusou-es11 ~]#  /usr/local/percona-toolkit/bin/pt-table-sync h=192.168.0.39,u=ptsum,p=ptchecksums,P=3306 --databases=test01 --tables=frame01 --replicate=percona.checksums --print
REPLACE INTO `test01`.`frame01`(`id`, `parent_id`, `dsn`) VALUES ('1', '1', '192.168.0.22,u=pt22,p=ptchecksums,P=3307') /*percona-toolkit src_db:test01 src_tbl:frame01 src_dsn:P=3306,h=192.168.0.39,p=...,u=ptsum dst_db:test01 dst_tbl:frame01 dst_dsn:P=3306,h=192.168.0.11,p=...,u=ptsum lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:176411 user:root host:kusou-es11*/;
REPLACE INTO `test01`.`frame01`(`id`, `parent_id`, `dsn`) VALUES ('2', '2', '192.168.0.33,u=pt33,p=ptchecksums,P=3308') /*percona-toolkit src_db:test01 src_tbl:frame01 src_dsn:P=3306,h=192.168.0.39,p=...,u=ptsum dst_db:test01 dst_tbl:frame01 dst_dsn:P=3306,h=192.168.0.11,p=...,u=ptsum lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:176411 user:root host:kusou-es11*/;

提示: 命令末尾的--print的指令是打印出需要修复数据的命令,不执行命令

pt-table-sync 修复主从库的表frame01数据 ,使主从库表frame01数据一致:


[root@kusou-es11 ~]#  /usr/local/percona-toolkit/bin/pt-table-sync h=192.168.0.39,u=ptsum,p=ptchecksums,P=3306 --databases=test01 --tables=frame01 --replicate=percona.checksums --execute
[root@kusou-es11 ~]# 

提示: 命令末尾的 --execute 的指令是执行修复数据的指令,使master库mysql.user表和slave库的mysql.user表数据一致

pt-table-checksum 检测主从库的表frame01数据一致 (因为 DIFFS =0 )

[root@kusou-es11 ~]#  /usr/local/percona-toolkit/bin/pt-table-checksum  h=192.168.0.39,u=ptsum,p='ptchecksums',P=3306 --databases=test01 --tables=frame01 --replicate=percona.checksums  --no-check-binlog-format --nocheck-replication-filters  
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
06-15T13:41:47      0      0        2          0       1       0   0.316 test01.frame01

slave库查看,数据和主库一致

mysql> select * from test01.frame01;
+----+-----------+------------------------------------------+
| id | parent_id | dsn                                      |
+----+-----------+------------------------------------------+
|  1 |         1 | 192.168.0.22,u=pt22,p=ptchecksums,P=3307 |
|  2 |         2 | 192.168.0.33,u=pt33,p=ptchecksums,P=3308 |
+----+-----------+------------------------------------------+
2 rows in set (0.00 sec)

提示:需要注意的是,需要同步的表上必须要有主键或者唯一索引,否则会出错。 同时,pt-table-sync 修复数据时,会造成锁表,要在业务低峰期来修复主库的数据

对找到的主从不一致的行,采用replace into语句,在主库执行一遍以生成该行全量的binlog,并同步到从库,这会以主库数据为基准来修复从库; 对于主库有的行而从库没有的行,采用replace在主库上插入(必须不能是insert); 对于从库有而主库没有的行,通过在主库执行delete来删除(pt-table-sync强烈建议所有的数据修复都只在主库进行,而不建议直接修改从库数据;但是也有特例,以后面会讲到)

二、实例演示2

在slave库上的mysql user表上,新建一个用户 qdtets@'192.168.0.%' 模拟master和slave库数据不一致


grant all on *.* to qdtets@'192.168.0.%' identified by 'DHWUOEdwerer';flush privileges;

[root@kusou-es11 ~]#  /usr/local/percona-toolkit/bin/pt-table-checksum  h=192.168.0.39,u=ptsum,p='ptchecksums',P=3306 --databases=mysql --replicate=percona.checksums  --no-check-binlog-format --nocheck-replication-filters  
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
06-15T13:32:27      0      0        0          0       1       0   0.317 mysql.columns_priv
06-15T13:32:28      0      0        2          0       1       0   0.318 mysql.db
06-15T13:32:28      0      0        2          0       1       0   0.316 mysql.engine_cost
06-15T13:32:28      0      0        0          0       1       0   0.317 mysql.event
06-15T13:32:29      0      0        0          0       1       0   0.316 mysql.func
06-15T13:32:29      0      0       41          0       1       0   0.317 mysql.help_category
06-15T13:32:29      0      0      699          0       1       0   0.318 mysql.help_keyword
06-15T13:32:30      0      0     1413          0       1       0   0.319 mysql.help_relation
06-15T13:32:30      0      0      643          0       1       0   0.325 mysql.help_topic
06-15T13:32:30      0      0        0          0       1       0   0.316 mysql.ndb_binlog_index
06-15T13:32:31      0      0        0          0       1       0   0.316 mysql.plugin
06-15T13:32:31      0      1       48          0       1       0   0.317 mysql.proc
06-15T13:32:31      0      0        0          0       1       0   0.317 mysql.procs_priv
06-15T13:32:32      0      0        1          0       1       0   0.317 mysql.proxies_priv
06-15T13:32:32      0      0        6          0       1       0   0.317 mysql.server_cost
06-15T13:32:32      0      0        0          0       1       0   0.316 mysql.servers
06-15T13:32:33      0      0        2          0       1       0   0.317 mysql.tables_priv
06-15T13:32:33      0      0        0          0       1       0   0.316 mysql.time_zone
06-15T13:32:33      0      0        0          0       1       0   0.316 mysql.time_zone_leap_second
06-15T13:32:33      0      0        0          0       1       0   0.316 mysql.time_zone_name
06-15T13:32:34      0      0        0          0       1       0   0.318 mysql.time_zone_transition
06-15T13:32:34      0      0        0          0       1       0   0.317 mysql.time_zone_transition_type
**06-15T13:32:34      0      1        6          1       1       0   0.319 mysql.user**

在slave库上pt-table-sync 修复数据:

slave库上操作:

[root@kusou-es11 ~]# /usr/local/percona-toolkit/bin/pt-table-sync h=192.168.0.39,u=ptsum,p=ptchecksums,P=3306 --databases=mysql --tables=user --replicate=percona.checksums --print
Access denied for user 'ptsum'@'192.168.0.%' to database 'mysql' [for Statement "LOCK TABLES `mysql`.`user` WRITE"] at line 6172 while doing mysql.user on 192.168.0.11

报错,提示没lock tables 权限

解决办法:登录master库重新授权,添加lock tables权限

grant  update,×××ert,select,create,drop,delete,index,execute,lock tables,super,process,replication slave on *.* to ptsum@'192.168.0.%' identified by 'ptchecksums'; flush privileges;

再次操作不在报错:

输出提示master库的mysql.user表和slave库的mysql.user表的数据不一致.slave库的mysql.user表多了一个用户qdtets@'192.168.0.%';需要删除这个用户,才能保证主库和从库数据的一致.

换句话说:

由于从库只是比主库多了一条数据,pt-table-sync将以主库以准,在主库执行一个删除操作的事件,然后slave应用此事件完成同步

[root@kusou-es11 ~]# /usr/local/percona-toolkit/bin/pt-table-sync h=192.168.0.39,u=ptsum,p=ptchecksums,P=3306 --databases=mysql --tables=user --replicate=percona.checksums --print
DELETE FROM `mysql`.`user` WHERE `host`='192.168.0.%' AND `user`='qdtets' LIMIT 1 /*percona-toolkit src_db:mysql src_tbl:user src_dsn:P=3306,h=192.168.0.39,p=...,u=ptsum dst_db:mysql dst_tbl:user dst_dsn:P=3306,h=192.168.0.11,p=...,u=ptsum lock:1 transaction:0 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:137319 user:root host:kusou-es11*/;

提示: 命令末尾的--print的指令是打印出需要修复数据的命令,不执行命令.

使用pt-table-checksum检测mysql的user表,DIFFS =1 事实证明master和slave的mysql user表数据确实不一致:

[root@kusou-es11 ~]# /usr/local/percona-toolkit/bin/pt-table-checksum  h=192.168.0.39,u=ptsum,p='ptchecksums',P=3306 --databases=mysql --tables=user  --no-check-binlog-format --nocheck-replication-filters
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
06-15T09:58:37      0      1        6          1       1       0   0.318 mysql.user

执行修复命令pt-table-sync 修复master和slave的mysql user表数据:

[root@kusou-es11 ~]#  /usr/local/percona-toolkit/bin/pt-table-sync h=192.168.0.39,u=ptsum,p=ptchecksums,P=3306 --databases=mysql --tables=user --replicate=percona.checksums --execute
[root@kusou-es11 ~]# 

提示: 命令末尾的 --execute 的指令是执行修复数据的指令,使master库mysql.user表和slave库的mysql.user表数据一致

slave库上检查master库的mysql.user表 和slave库的mysql.user 表数据是否一致:

[root@kusou-es11 ~]# /usr/local/percona-toolkit/bin/pt-table-checksum  h=192.168.0.39,u=ptsum,p='ptchecksums',P=3306 --databases=mysql --tables=user  --no-check-binlog-format --nocheck-replication-filters
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
06-15T09:46:52      0      0        6          0       1       0   0.319 mysql.user

可以看到DIFFS =0 ,说明数据已经修复完成

三、实例演示3

将master 上的所有数据同步到slave:

[root@kusou-es11 ~]# /usr/local/percona-toolkit/bin/pt-table-sync --execute  h=192.168.0.39,u=ptsum,p=ptchecksums,P=3306 --databases=mysql --tables=user h=192.168.0.11,u=ptsum,p='ptchecksums',P=3306 --no-check-slave  --print
DELETE FROM `mysql`.`user` WHERE `host`='10.0.0.1' AND `user`='dhrue' LIMIT 1 /*percona-toolkit src_db:mysql src_tbl:user src_dsn:P=3306,h=192.168.0.39,p=...,u=ptsum dst_db:mysql dst_tbl:user dst_dsn:P=3306,h=192.168.0.11,p=...,u=ptsum lock:0 transaction:0 changing_src:0 replicate:0 bidirectional:0 pid:12565 user:root host:kusou-es11*/;

将master 上的所有数据同步到slave1和slave2:


[root@kusou-es11 ~]# /usr/local/percona-toolkit/bin/pt-table-sync --execute  h=192.168.0.39,u=ptsum,p=ptchecksums,P=3306 --databases=mysql --tables=user h=192.168.0.11,u=ptsum,p='ptchecksums',P=3307    h=192.168.0.22,u=ptsum,p='ptchecksums',P=3308   --no-check-slave  --print

对pt-table-checksum和pt-table-sync这一组工具进行了最简单的测试,其实运行这一组命令不一定需要在主从结构的主库上进行,网段内的任何服务器都可以运行,前提就是安装好这套工具就好。

四、原理介绍

pt-table-checksum/pt-table-sync原理介绍可以参考如下博文: http://blog.sina.com.cn/s/blog_a1e9c7910102vnsd.html

五、校验和修复正确的指令

环境说明:下面演示的环境是一主一从,从库开启了复制过滤参数 线上修复主从库不一致数据的正确的指令:

提示:采用pt-table-sync修复数据库中不一致表数据前,必须提前对要修复的表进行数据一致性校验

5.1线上正确的校验命令:

校验具体的表:

time /usr/local/percona-toolkit/bin/pt-table-checksum h=10.0.0.4,u=ptsum,p='ptchecksums',P=3306 --ignore-databases sys,mysql,appDB2 --tables=appDB.lz_account_huludou --truncate-replicate-table  --replicate=appDB.ptchecksums --no-check-binlog-format --nocheck-replication-filters --recursion-method="processlist" 

** 校验排除sys,mysql,appDB2库的其他的库:**

 time /usr/local/percona-toolkit/bin/pt-table-checksum h=10.0.0.4,u=ptsum,p='ptchecksums',P=3306 --ignore-databases sys,mysql,appDB2  --truncate-replicate-table  --replicate=huluzhuan.ptchecksums --no-check-binlog-format --nocheck-replication-filters --recursion-method="processlist"   2>&1 | tee 2020-04-12-pt-checksum.log

** 一次可以校验一个库下的多个表数据:**

  time /usr/local/percona-toolkit/bin/pt-table-checksum h=10.0.0.2,u=lzptsum,p='ptchecksumsHulz',P=3306 --databases=appDB --tables=lz_account,lz_account_huludou,lz_account_lingqian --truncate-replicate-table  --replicate=appDB.pt0325checksums  --no-check-binlog-format --nocheck-replication-filters --recursion-method="processlist" 

** pt-table-checksum校验时相关参数介绍:**

h=10.0.0.4 指定主库的内网ip地址; 提前在主库上创建好pt-table-checksum 和pt-table-rsync指令用到的数据库的账户和密码:u=ptsum,p=ptchecksums P=3306 指定要校验数据库的端口; --ignore-databases sys,mysql,appDB2 忽略这些不需要校验的数据库,(默认performance_schema,information_schema这2个库是不被校验的); --tables=appDB.lz_account_huludou 指定要校验的表;可以逗号分隔来指定校验多个表 --truncate-replicate-table 清空上次校验表时存放在appDB.ptchecksums 表中的数据

--replicate=appDB.ptchecksums 本次校验表时生成的数据存放在表appDB.ptchecksums 中 --no-check-binlog-format 不检查binlog格式 --nocheck-replication-filters 不检查mysql中复制过滤的参数(校验时,一旦主从库中存在复制过滤参数,--check-replication-filters 加上此参数会导致检验失败) --recursion-method="processlist" 采用的校验的方法,默认就是这个方法

提示:由于线上的主库和从库之间开启了指定数据库同步的复制过滤参数,slave库只同步master库上的appDB,所以在校验appDB数据库时,要指定--replicate=appDB.ptchecksums 这样校验时主库生成的存放校验数据的表ptchecksums 才能同步到slave机器上,这样校验才不会报错。如果不指定的话,默认是把校验的数据存放在在master机器percona库的ptchecksums表中的。 由于slave机器开启复制过滤参数,导致主库上的percona库不会同步到slave库,导致校验提示找不到表而失败。

5.2、线上修复主从库不一致数据的正确指令

pt-table-sync h=10.0.0.4,u=ptsum,p=ptchecksums,P=3306 --databases=appDB --tables=appDB.lz_ad_experience  --replicate=appDB.ptchecksums  --charset=utf8  --transaction --print 
pt-table-sync h=10.0.0.4,u=ptsum,p=ptchecksums,P=3306 --databases=appDB --tables=appDB.lz_account  --replicate=appDB.ptchecksums  --charset=utf8  --transaction --execute

命令参数说明:

10.0.0.4 为主库的内网ip地址, u=ptsum,p=ptchecksums提前在主库上创建好pt-table-checksum 和pt-table-rsync指令用到的数据库的账户和密码 --databases= 指定要修复的数据库,可以逗号分隔来指定修复多个库 --tables=指定要修复的表;可以逗号分隔来指定校验多个表 --replicate=appDB.ptchecksums 依据appDB.ptchecksum表中的数据来修复 --charset=utf8 修复时一定要指定字符集 --transaction 针对修复innodb表时采用事务 --print 打印出修复时要执行的SQL语句 --execute 直接执行指令

5.3、校验整个库时统计出数据不一致的表

time /usr/local/percona-toolkit/bin/pt-table-checksum h=10.0.0.4,u=ptsum,p='ptchecksums',P=3306 --ignore-databases sys,mysql,appDB2  --truncate-replicate-table  --replicate=appdb.ptchecksums --no-check-binlog-format --nocheck-replication-filters --recursion-method="processlist"   2>&1 | tee 2020-04-12-pt-checksum.log

获取到哪些表的数据主从库是不一致的:

[root@localhost ~]# awk '($3 !~ /0/)' 2020-04-12-pt-checksum.log|awk '{print $3,$NF}' |awk '{if ($2 ~ "^appdb" )print $0}'
2 appdb.lz_account
1 appdb.lz_account_lingqian
1 appdb.lz_ad_experience
1 appdb.lz_ad_experience_detail_ans
1 appdb.lz_ad_illegal_user
1 appdb.lz_ad_user
1 appdb.lz_ad_user_record
1 appdb.lz_ad_voucher
1 appdb.lz_bubble
1 appdb.lz_task_sign
1 appdb.lz_user
1 appdb.lz_user_cashinfo_nodel
1 appdb.lz_user_hongbao
1 appdb.lz_user_recommend_income
 或者:awk '($3 !~ /0/)' 2020-04-12-pt-checksum.log|awk '{print $3,$NF}' |awk '{if ($2 ~ "^appdb" )print $0}' |awk '{print $NF}'|tee /tmp/tables.txt
posted @ 2019-06-15 18:52  勤奋的蓝猫  阅读(7)  评论(0编辑  收藏  举报  来源