pt-table-checksum和pt-table-sync分别检验master-slave的数据不一致并修复。
1、本次测试环境
1 [root@172-16-3-190 we_ops_admin]# cat /etc/redhat-release 2 CentOS release 6.8 (Final) 3 [root@172-16-3-190 we_ops_admin]# /opt/app/mysql_3309/bin/mysqladmin --version 4 /opt/app/mysql_3309/bin/mysqladmin Ver 8.42 Distrib 5.6.20-68.0, for Linux on x86_64 5 [root@172-16-3-190 we_ops_admin]# pt-table-checksum --version 6 pt-table-checksum 3.0.4 7 8 master1:172.16.3.190 basedir:/opt/app/mysql_3309/ datadir:/opt/app/mysql_3309/data port:3309 9 slave1:172.16.3.189 basedir:/opt/app/mysql_3309/ datadir:/opt/app/mysql_3309/data port:3309 10 master&slave:binlog_format=mixed
2、
1 ----测试表aa结构 2 CREATE TABLE `aa` ( 3 `aa` varchar(1) DEFAULT '', 4 `bb` varchar(1) DEFAULT NULL, 5 `id` int(11) NOT NULL, 6 PRIMARY KEY (`id`) 7 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 8 9 ----master上表数据 10 mysql> select * from aa; 11 +------+------+----+ 12 | aa | bb | id | 13 +------+------+----+ 14 | 1 | 1 | 1 | 15 | 2 | 2 | 2 | 16 | 5 | 2 | 5 | 17 +------+------+----+ 18 3 rows in set (0.00 sec) 19 20 ----slave上表数据 21 mysql> select * from aa; 22 +------+------+----+ 23 | aa | bb | id | 24 +------+------+----+ 25 | 2 | 2 | 2 | 26 | 4 | 4 | 4 | 27 | 5 | 5 | 5 | 28 +------+------+----+ 29 3 rows in set (0.00 sec)
构造master-slave的差异环境,slave同步master数据后,人为修改slave数据使得不一致。
1 grant all privileges on *.* to 'checksums'@'172.16.%.%' identified by 'checksums' 2 Query OK, 0 rows affected (0.00 sec)
2)pt-table-checksum检测差异,并写入差异到checksums表中,master上执行
1 [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 2 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 3 01-30T10:26:44 0 0 3 1 0 0.042 ceshi.aa
1 master上执行 2 mysql> set @@global.binlog_format=statement; 3 Query OK, 0 rows affected (0.00 sec) 4 5 slave上执行 6 mysql> set @@global.binlog_format=statement; 7 Query OK, 0 rows affected (0.00 sec)
master上再次执行,发现DIFFS的值终于为1,表示已经检测到master-slave数据的不一致了
1 [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 2 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 3 01-30T11:02:15 0 1 3 1 0 0.026 ceshi.aa
[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上再次构造差异并执行修复命令
1 ---slave上执行 2 mysql> update aa set id = 4 where aa = 5; 3 Query OK, 1 row affected (0.00 sec) 4 Rows matched: 1 Changed: 1 Warnings: 0 5 6 ----master上执行检测 7 [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 8 9 # A software update is available: 10 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 11 01-30T15:12:27 0 1 3 1 0 0.025 ceshi.aa 12 13 ----slave上执行数据修复 14 [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 15 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*/; 16 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*/; 17 18 [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 19 20 [root@172-16-3-189 we_ops_admin]# 3309.sh 21 Warning: Using a password on the command line interface can be insecure. 22 Welcome to the MySQL monitor. Commands end with ; or \g. 23 Your MySQL connection id is 223 24 Server version: 5.6.20-68.0-log Percona Server (GPL), Release 68.0, Revision 656 25 26 Copyright (c) 2009-2014 Percona LLC and/or its affiliates 27 Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. 28 29 Oracle is a registered trademark of Oracle Corporation and/or its 30 affiliates. Other names may be trademarks of their respective 31 owners. 32 33 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 34 35 mysql> select * from aa; 36 ERROR 1046 (3D000): No database selected 37 mysql> use ceshi; 38 Reading table information for completion of table and column names 39 You can turn off this feature to get a quicker startup with -A 40 41 Database changed 42 mysql> select * from aa; 43 +------+------+----+ 44 | aa | bb | id | 45 +------+------+----+ 46 | 1 | 1 | 1 | 47 | 2 | 2 | 2 | 48 | 5 | 2 | 5 | 49 +------+------+----+ 50 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数据不一致性是否修复,经检验数据一致
1 ----master上表aa数据 2 mysql> select * from ceshi.aa; 3 +------+------+----+ 4 | aa | bb | id | 5 +------+------+----+ 6 | 1 | 1 | 1 | 7 | 2 | 2 | 2 | 8 | 5 | 2 | 5 | 9 +------+------+----+ 10 3 rows in set (0.00 sec) 11 12 ----slave上表aa数据 13 mysql> select * from ceshi.aa; 14 +------+------+----+ 15 | aa | bb | id | 16 +------+------+----+ 17 | 1 | 1 | 1 | 18 | 2 | 2 | 2 | 19 | 5 | 2 | 5 | 20 +------+------+----+ 21 3 rows in set (0.00 sec)
再次利用工具运行,检测master-slave数据一致性
1 [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 2 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 3 01-30T14:50:43 0 0 3 1 0 0.038 ceshi.aa
如对本文有疑问, 点击进行留言回复!!
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火,普罗米修斯的崛起
网友评论