pt-table-checksum和pt-table-sync分別檢驗master-slave的數據不一致並修複。 1、本次測試環境 2、 3、開始檢測差異 1)創建一個用戶,可以訪問master和slave,master上執行如下的創建用戶命令 構造master-slave的差異環境,slave同 ...
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)3、開始檢測差異 1)創建一個用戶,可以訪問master和slave,master上執行如下的創建用戶命令
構造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.aapt-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_crc、master_cnt更新到從庫,最後在從庫對比master和this相關列,也就是說從庫不會去計算所謂的CRC32,它直接完整copy主庫的checksums的所有內容。pt-table-checksum 3.0.4在執行時缺少SET@@binlog_format='STATEMENT',建議不要使用。 有一種很挫的方法,僅僅是為了看差異結果(生產環境勿用),執行pt-table-checksum前,在主上 set global binlog_format='STATEMENT'。
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.aa4、pt-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上再次構造差異並執行修複命令
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