pt-table-checksum 和 pt-table-sync 分别检验 master-slave 的数据不一致并修复
1 本次测试环境
- [root@172-16-3-190 we_ops_admin]# cat /etc/redhat-release
- CentOS release 6.8 (Final)
- [root@172-16-3-190 we_ops_admin]# /opt/app/mysql_3309/bin/mysqladmin --version
- /opt/app/mysql_3309/bin/mysqladmin Ver 8.42 Distrib 5.6.20-68.0, for Linux on x86_64
- [root@172-16-3-190 we_ops_admin]# pt-table-checksum --version
- pt-table-checksum 3.0.4
- master1:172.16.3.190 basedir:/opt/app/mysql_3309/ datadir:/opt/app/mysql_3309/data port:3309
- slave1:172.16.3.189 basedir:/opt/app/mysql_3309/ datadir:/opt/app/mysql_3309/data port:3309
- master&slave:binlog_format=mixed
- 2
---- 测试表 aa 结构
- CREATE TABLE `aa` (
- `aa` varchar(1) DEFAULT '',
- `bb` varchar(1) DEFAULT NULL,
- `id` int(11) NOT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
----master 上表数据
- mysql> select * from aa;
- +------+------+----+
- | aa | bb | id |
- +------+------+----+
- | 1 | 1 | 1 |
- | 2 | 2 | 2 |
- | 5 | 2 | 5 |
- +------+------+----+
- 3 rows in set (0.00 sec)
----slave 上表数据
- mysql> select * from aa;
- +------+------+----+
- | aa | bb | id |
- +------+------+----+
- | 2 | 2 | 2 |
- | 4 | 4 | 4 |
- | 5 | 5 | 5 |
- +------+------+----+
- 3 rows in set (0.00 sec)
3 开始检测差异
1) 创建一个用户, 可以访问 master 和 slave,master 上执行如下的创建用户命令
构造 master-slave 的差异环境, slave 同步 master 数据后, 人为修改 slave 数据使得不一致
- grant all privileges on *.* to 'checksums'@'172.16.%.%' identified by 'checksums'
- Query OK, 0 rows affected (0.00 sec)
2)pt-table-checksum 检测差异, 并写入差异到 checksums 表中, master 上执行
- [root@172-16-3-190 we_ops_admin]# /usr/bin/pt-table-checksum --create-replicate-table --replicate=ceshi.checksums --nocheck-replication-filters --nocheck-binlog-format --recursion-method=processlist --databases=ceshi --user=checksums --password=checksums -h172.16.3.190 --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309
- TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
- 01-30T10:26:44 0 0 3 1 0 0.042 ceshi.aa
pt-table-checksum 3.0.4 的 bug, 功能缺乏, binlog_format 格式非 statement 格式检测不出来差异
DIFFS=0 表示没有差异数据实际上主从数据不一致, 我们已经加入了参数 --nocheck-binlog-format, 这里却没有检测出来为什么没有检测出来呢?
pt-table-checksum 针对的 binlog_format=statement 的格式, 根据 pt-table-checksum 的原理, 它在执行的时候, 没有将会话级别的 binlog_format=statement 设置成功, 那我们只能手动将动态参数 binlog_format 设置为 statement 模式
只有在 statement 格式下才能进行, 因为两边要计算 CRC32, 计算完后再把主上的 master_crcmaster_cnt 更新到从库, 最后在从库对比 master 和 this 相关列, 也就是说从库不会去计算所谓的 CRC32, 它直接完整 copy 主库的 checksums 的所有内容 pt-table-checksum 3.0.4 在执行时缺少 SET@@binlog_format='STATEMENT', 建议不要使用
有一种很挫的方法, 仅仅是为了看差异结果 (生产环境勿用), 执行 pt-table-checksum 前, 在主上 set global binlog_format='STATEMENT'
master 上执行
- mysql> set @@global.binlog_format=statement;
- Query OK, 0 rows affected (0.00 sec)
slave 上执行
- mysql> set @@global.binlog_format=statement;
- Query OK, 0 rows affected (0.00 sec)
master 上再次执行, 发现 DIFFS 的值终于为 1, 表示已经检测到 master-slave 数据的不一致了
- [root@172-16-3-190 we_ops_admin]# /usr/bin/pt-table-checksum --create-replicate-table --replicate=ceshi.checksums --nocheck-replication-filters --nocheck-binlog-format --recursion-method=processlist --databases=ceshi --user=checksums --password=checksums -h172.16.3.190 --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309
- TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
- 01-30T11:02:15 0 1 3 1 0 0.026 ceshi.aa
4pt-table-sync 修复 master-slave 数据不一致, master 和 slave 都可以进行修复命令的执行
1)master 上执行,--print 打印出修复的 sql 语句参数 --sync-to-master 参数在 master 上执行必须有, 否则打印不出差异 sql
- [root@172-16-3-190 we_ops_admin]# pt-table-sync --sync-to-master --replicate=ceshi.checksums -h172.16.3.190 --user=checksums --password=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 h=172.16.3.189,u=checksums,p=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 --print
- DELETE FROM `ceshi`.`aa` WHERE `id`='4' LIMIT 1 /*percona-toolkit src_db:ceshi src_tbl:aa src_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.190,p=...,u=checksums dst_db:ceshi dst_tbl:aa dst_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.189,p=...,u=checksums lock:1 transaction:1 changing_src:ceshi.checksums replicate:ceshi.checksums bidirectional:0 pid:13528 user:root host:172-16-3-190*/;
- REPLACE INTO `ceshi`.`aa`(`aa`, `bb`, `id`) VALUES ('1', '1', '1') /*percona-toolkit src_db:ceshi src_tbl:aa src_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.190,p=...,u=checksums dst_db:ceshi dst_tbl:aa dst_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.189,p=...,u=checksums lock:1 transaction:1 changing_src:ceshi.checksums replicate:ceshi.checksums bidirectional:0 pid:13528 user:root host:172-16-3-190*/;
- REPLACE INTO `ceshi`.`aa`(`aa`, `bb`, `id`) VALUES ('5', '2', '5') /*percona-toolkit src_db:ceshi src_tbl:aa src_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.190,p=...,u=checksums dst_db:ceshi dst_tbl:aa dst_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.189,p=...,u=checksums lock:1 transaction:1 changing_src:ceshi.checksums replicate:ceshi.checksums bidirectional:0 pid:13528 user:root host:172-16-3-190*/;
slave 上再次构造差异并执行修复命令
---slave 上执行
- mysql> update aa set id = 4 where aa = 5;
- Query OK, 1 row affected (0.00 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
----master 上执行检测
- [root@172-16-3-190 we_ops_admin]# /usr/bin/pt-table-checksum --create-replicate-table --replicate=ceshi.checksums --nocheck-replication-filters --nocheck-binlog-format --recursion-method=processlist --databases=ceshi --user=checksums --password=checksums -h172.16.3.190 --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309
- # A software update is available:
- TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
- 01-30T15:12:27 0 1 3 1 0 0.025 ceshi.aa
----slave 上执行数据修复
- [root@172-16-3-189 we_ops_admin]# pt-table-sync --sync-to-master --replicate=ceshi.checksums -h172.16.3.190 --user=checksums --password=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 h=172.16.3.189,u=checksums,p=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 --print
- DELETE FROM `ceshi`.`aa` WHERE `id`='4' LIMIT 1 /*percona-toolkit src_db:ceshi src_tbl:aa src_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.190,p=...,u=checksums dst_db:ceshi dst_tbl:aa dst_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.189,p=...,u=checksums lock:1 transaction:1 changing_src:ceshi.checksums replicate:ceshi.checksums bidirectional:0 pid:23321 user:root host:172-16-3-189*/;
- REPLACE INTO `ceshi`.`aa`(`aa`, `bb`, `id`) VALUES ('5', '2', '5') /*percona-toolkit src_db:ceshi src_tbl:aa src_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.190,p=...,u=checksums dst_db:ceshi dst_tbl:aa dst_dsn:P=3309,S=/opt/app/mysql_3309/tmp/mysql.sock,h=172.16.3.189,p=...,u=checksums lock:1 transaction:1 changing_src:ceshi.checksums replicate:ceshi.checksums bidirectional:0 pid:23321 user:root host:172-16-3-189*/;
- [root@172-16-3-189 we_ops_admin]# pt-table-sync --sync-to-master --replicate=ceshi.checksums -h172.16.3.190 --user=checksums --password=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 h=172.16.3.189,u=checksums,p=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 --execute
- [root@172-16-3-189 we_ops_admin]# 3309.sh
- Warning: Using a password on the command line interface can be insecure.
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 223
- Server version: 5.6.20-68.0-log Percona Server (GPL), Release 68.0, Revision 656
- Copyright (c) 2009-2014 Percona LLC and/or its affiliates
- Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql> select * from aa;
- ERROR 1046 (3D000): No database selected
- mysql> use ceshi;
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> select * from aa;
- +------+------+----+
- | aa | bb | id |
- +------+------+----+
- | 1 | 1 | 1 |
- | 2 | 2 | 2 |
- | 5 | 2 | 5 |
- +------+------+----+
- 3 rows in set (0.00 sec)
2)--execute 执行修复语句
1 [root@172-16-3-190 we_ops_admin]# pt-table-sync --sync-to-master --replicate=ceshi.checksums -h172.16.3.190 --user=checksums --password=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 h=172.16.3.189,u=checksums,p=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 --execute
3) 验证 master 和 slave 数据不一致性是否修复, 经检验数据一致
----master 上表 aa 数据
- mysql> select * from ceshi.aa;
- +------+------+----+
- | aa | bb | id |
- +------+------+----+
- | 1 | 1 | 1 |
- | 2 | 2 | 2 |
- | 5 | 2 | 5 |
- +------+------+----+
- 3 rows in set (0.00 sec)
----slave 上表 aa 数据
- mysql> select * from ceshi.aa;
- +------+------+----+
- | aa | bb | id |
- +------+------+----+
- | 1 | 1 | 1 |
- | 2 | 2 | 2 |
- | 5 | 2 | 5 |
- +------+------+----+
- 3 rows in set (0.00 sec)
再次利用工具运行, 检测 master-slave 数据一致性
- [root@172-16-3-190 we_ops_admin]# /usr/bin/pt-table-checksum --create-replicate-table --replicate=ceshi.checksums --nocheck-replication-filters --nocheck-binlog-format --recursion-method=processlist --databases=ceshi --user=checksums --password=checksums -h172.16.3.190 --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309
- TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
- 01-30T14:50:43 0 0 3 1 0 0.038 ceshi.aa
来源: https://www.cnblogs.com/liyingxiao/p/8619190.html