【工具】检查主从一致性 pt-table-checksum
2020-12-29 本文已影响0人
醉红尘丶
pt-table-checksum
-
--[no]check-replication-filters：是否检查复制的过滤器，默认是yes，建议启用不检查模式。 -
--databases | -d：指定需要被检查的数据库，多个库之间可以用逗号分隔。 -
--[no]check-binlog-format：是否检查binlog文件的格式，默认值yes。建议开启不检查。因为在默认的row格式下会出错。 -
--replicate：把checksum的信息写入到指定表中。 -
--replicate-check-only：只显示不同步，有差异的信息 -
--create-replicate-table：如果此选项指定的数据库和表不存在，则会自动创建它们。默认库创建名称为 percona
环境
MySQl版本：5.7.27
已配置主从，主 101，从 102
数据准备【主101】
-- 创建库
create database abc;
create database check_test;
-- 创建使用的用户
create user 'check_user'@'%' identified by 123456;
grant SELECT,PROCESS, SUPER, REPLICATION SLAVE,Replication client ON *.* TO 'check_user'@'%';
grant all on check_test.* to 'check_user'@'%';
-- 创建表
create table tb1(id int auto_increment primary key,a varchar(10));
create table tb2(id int auto_increment primary key,a varchar(10));
-- 写入测试数据
insert into tb1(a) values(1),(2),(3),(4),(5);
insert into tb2(a) values(1),(2),(3);
mysql> select * from tb1;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+----+------+
5 rows in set (0.00 sec)
mysql> select * from abc.tb2;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
3 rows in set (0.00 sec)
至此，主从库中均有如上数据。当前数据是一致的。
检查主从一致性【主库操作】
当前检查结果是一致的，检查的是【全库】，如果要检查多个表的话加上--tables=tb1,tb2即可
pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --replicate=check_test.checksums --databases=abc --host=192.168.66.101 --port=3306 --user=check_user --password=123456
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
12-29T16:25:29 0 0 5 0 1 0 0.031 abc.tb1
12-29T16:25:29 0 0 3 0 1 0 0.047 abc.tb2
TS ：完成检查的时间。
ERRORS ：检查时候发生错误和警告的数量。
DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS ：表的行数。
CHUNKS ：被划分到表中的块的数目。
SKIPPED ：由于错误或警告或过大，则跳过块的数目。
TIME ：执行的时间。
TABLE ：被检查的表名。
--replicate=check_test.checksums 结果写到 check_test 库下的 checksums 表中
--host： 需要是主库的用户名密码
使主从数据不一致
在从库上做一些操作，导致主从数据不一致
-- 从库上删除一条数据，当前主库存在5条，从库存在4条
delete from abc.tb1 where id = 5;
检查主从一致性【主库操作】
检查结果 abc.tb1 的 DIFFS 为 1，主从有一行数据不一致。
pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --replicate=check_test.checksums --databases=abc --host=192.168.66.101 --port=3306 --user=check_user --password=123456
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
12-29T16:49:15 0 1 5 1 1 0 0.016 abc.tb1
12-29T16:49:15 0 0 3 0 1 0 0.022 abc.tb2
原理
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| argument |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| check_user@192.168.66.101 on using TCP/IP |
| SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout' |
| SET SESSION innodb_lock_wait_timeout=1 |
| SHOW VARIABLES LIKE 'wait\_timeout' |
| SET SESSION wait_timeout=10000 |
| SELECT @@SQL_MODE |
| SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/ |
| SELECT VERSION() |
| SHOW VARIABLES LIKE 'character_set_server' |
| SELECT @@server_id /*!50038 , @@hostname*/ |
| SELECT @@SQL_MODE |
| SET SQL_MODE=',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' |
| SHOW VARIABLES LIKE 'version%' |
| SHOW ENGINES |
| SHOW VARIABLES LIKE 'innodb_version' |
| SELECT @@binlog_format |
| /*!50108 SET @@binlog_format := 'STATEMENT'*/ |
| SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |
| SHOW /*!40103 GLOBAL*/ VARIABLES |
| SELECT VERSION() |
| SHOW ENGINES |
| SHOW VARIABLES LIKE 'wsrep_on' |
| SELECT @@SERVER_ID |
| SHOW GRANTS FOR CURRENT_USER() |
| SHOW FULL PROCESSLIST |
| SHOW VARIABLES LIKE 'wsrep_on' |
| SELECT @@SERVER_ID |
| SHOW VARIABLES LIKE 'wsrep_on' |
| SELECT @@SERVER_ID |
| SHOW DATABASES LIKE 'check_test' |
| CREATE DATABASE IF NOT EXISTS `check_test` /* pt-table-checksum */ |
| USE `check_test` |
| SHOW TABLES FROM `check_test` LIKE 'checksums' |
| CREATE TABLE IF NOT EXISTS `check_test`.`checksums` (
db CHAR(64) NOT NULL,
tbl CHAR(64) NOT NULL,
chunk INT NOT NULL,
chunk_time FLOAT NULL,
chunk_index VARCHAR(200) NULL,
lower_boundary TEXT NULL,
upper_boundary TEXT NULL,
this_crc CHAR(40) NOT NULL,
this_cnt INT NOT NULL,
master_crc CHAR(40) NULL,
master_cnt INT NULL,
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| SHOW GLOBAL STATUS LIKE 'Threads_running' |
| SELECT CONCAT(@@hostname, @@port) |
| SELECT CRC32('test-string') |
| SELECT CRC32('a') |
| SELECT CRC32('a') |
| SHOW VARIABLES LIKE 'wsrep_on' |
| SHOW DATABASES |
| SHOW /*!50002 FULL*/ TABLES FROM `abc` |
| /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ |
| USE `abc` |
| SHOW CREATE TABLE `abc`.`tb1` |
| /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ |
| EXPLAIN SELECT * FROM `abc`.`tb1` WHERE 1=1 |
| USE `check_test` |
| DELETE FROM `check_test`.`checksums` WHERE db = 'abc' AND tbl = 'tb1' |
| USE `abc` |
| EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, convert(`a` using utf8mb4), CONCAT(ISNULL(`a`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `abc`.`tb1` /*explain checksum table*/ |
| REPLACE INTO `check_test`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'abc', 'tb1', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, convert(`a` using utf8mb4), CONCAT(ISNULL(`a`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `abc`.`tb1` /*checksum table*/ |
| SHOW WARNINGS |
| SELECT this_crc, this_cnt FROM `check_test`.`checksums` WHERE db = 'abc' AND tbl = 'tb1' AND chunk = '1' |
| UPDATE `check_test`.`checksums` SET chunk_time = '0.002779', master_crc = '553dae91', master_cnt = '5' WHERE db = 'abc' AND tbl = 'tb1' AND chunk = '1' |
| SHOW GLOBAL STATUS LIKE 'Threads_running' |
| SHOW VARIABLES LIKE 'wsrep_on' |
| SHOW MASTER STATUS |
| /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ |
| USE `abc` |
| SHOW CREATE TABLE `abc`.`tb2` |
| /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ |
| EXPLAIN SELECT * FROM `abc`.`tb2` WHERE 1=1 |
| USE `check_test` |
| DELETE FROM `check_test`.`checksums` WHERE db = 'abc' AND tbl = 'tb2' |
| USE `abc` |
| EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, convert(`a` using utf8mb4), CONCAT(ISNULL(`a`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `abc`.`tb2` /*explain checksum table*/ |
| REPLACE INTO `check_test`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'abc', 'tb2', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, convert(`a` using utf8mb4), CONCAT(ISNULL(`a`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `abc`.`tb2` /*checksum table*/ |
| SHOW WARNINGS |
| SELECT this_crc, this_cnt FROM `check_test`.`checksums` WHERE db = 'abc' AND tbl = 'tb2' AND chunk = '1' |
| UPDATE `check_test`.`checksums` SET chunk_time = '0.002415', master_crc = '699fed16', master_cnt = '3' WHERE db = 'abc' AND tbl = 'tb2' AND chunk = '1' |
| SHOW GLOBAL STATUS LIKE 'Threads_running' |
| SHOW VARIABLES LIKE 'wsrep_on' |
| SHOW MASTER STATUS | |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
77 rows in set (0.00 sec)
