mysql主从数据一致性校验和修复

1. 安装

1yum install -y perl-IO-Socket-SSL.noarch perl-Digest-MD5
2yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
3yum install percona-toolkit -y

2. 创建校验用户并授权限

GRANT REPLICATION SLAVE,PROCESS,SUPER, SELECT ON *.* TO `checksum_user`@'%' IDENTIFIED BY 'checksum_xxxxxx'; GRANT ALL PRIVILEGES ON percona.* TO `checksum_user`@'%';

3. 校验

全库校验

pt-table-checksum --replicate=percona.checksums --no-check-binlog-format h=127.0.0.1,u=checksum_user,p=checksum_xxxxxx
不校验mysql和test库

pt-table-checksum --replicate=percona.checksums --no-check-binlog-format --ignore-databases mysql,test h=127.0.0.1,u=checksum_user,p=checksum_xxxxxx
只校验db1,db2两个库

pt-table-checksum --replicate=percona.checksums --no-check-binlog-format --databases db1,db2 h=127.0.0.1,u=checksum_user,p=checksum_xxxxxx
只校验db1.t1,db2.t2两张表

pt-table-checksum --replicate=percona.checksums --no-check-binlog-format --tables db1.t1,db2.t2 h=127.0.0.1,u=checksum_user,p=checksum_xxxxxx
使用dsn表校验

使用dns表进行校验的好处是，当校验多个slave不标准3306端口，或者使用不同的校验账号密码，或者只想校验其中一个slave，可以使用这种方法。通过--recursion-method参数指定dns，recursion-method选项有以下几种

METHOD USES

=========== =============================================

processlist SHOW PROCESSLIST

hosts SHOW SLAVE HOSTS

cluster SHOW STATUS LIKE 'wsrep\_incoming\_addresses'

dsn=DSN DSNs from a table

none Do not find slaves

首先需要在主库创建一张dsn表，我在percona库下创建,并输入从库连接信息，id和parent_id字段可随意

在新版本的pt-table-ckecksum不用自己添加checksums表，在pt-table-checksum 表时，会在master库上默认生成percona库和表checksums

1mysql> create database percona;
2mysql> use percona;
3mysql> CREATE TABLE `dsns` (
4`id` int(11) NOT NULL AUTO_INCREMENT,
5`parent_id` int(11) DEFAULT NULL,
6`dsn` varchar(255) NOT NULL,
7PRIMARY KEY (`id`)
8);
9mysql> SELECT * FROM dsns;
10+----+-----------+------------------------------------------------------------+
11| id | parent_id | dsn |
12+----+-----------+------------------------------------------------------------+
13| 1 | 1 | h=192.168.0.134,u=checksum_user,p=checksum_xxxxxx,P=3306 |
14| 2 | 2 | h=192.168.0.132,u=checksum_user,p=checksum_xxxxxx,P=3307 |
15+----+-----------+------------------------------------------------------------+# 开始验证shell> pt-table-checksum --replicate percona.checksums --no-check-binlog-format h='master_host',u=checksum_user,p=checksum_xxxxxx --recursion-method dsn=h='dsn_host',u='dsn_user',p='dsn_pass',P='dsn_port',D=percona,t=dsns

4. 列出与主库数据不一致的表

可以在主库上通过--replicate-check-only 参数仅打印数据不一致的表，不会再进行校验

pt-table-checksum --replicate=percona.checksums --replicate-check-only --no-check-binlog-format --ignore-databases mysql h=localhost,u=checksum_user,p=checksum_xxxxxx

也可以在每台从库slave执行下面SQL语句查找哪些表数据和主库不一致

1SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
2FROM percona.checksums
3WHERE (
4master_cnt <> this_cnt
5OR master_crc <> this_crc
6OR ISNULL(master_crc) <> ISNULL(this_crc))
7GROUP BY db, tbl;

5. 输出结果说明

1TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
210-20T08:36:50 0 0 200 1 0 0.005 db1.tbl1
310-20T08:36:50 0 0 603 7 0 0.035 db1.tbl2
410-20T08:36:50 0 0 16 1 0 0.003 db2.tbl3
510-20T08:36:50 0 0 600 6 0 0.024 db2.tbl4
6TS：工具完成校验和表时的时间戳
7ERRORS：校验表时发生的错误和警告的数量
8DIFFS：一个或多个副本上与主节点不同的块的数量，大于0说明这些表数据和主库不一致
9ROWS：从表中选择和校验的行数
10CHUNKS：表被分成的块的数量
11SKIPPED：校验出错跳过块的数量
12TIME：校验表时花费的时间
13TABLE：已进行校验和的数据库和表

6. 加--replicate-check-only输出结果说明

如果指定了--replicate-check-only，则只会打印检测到的副本上的校验和差异。
输出不同：每个副本一个段落，每行一个校验和差异，值之间用空格分隔.

1Differences on h=127.0.0.1,P=12346
2TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
3db1.tbl1 1 0 1 PRIMARY 1 100
4db1.tbl1 6 0 1 PRIMARY 501 600
5Differences on h=127.0.0.1,P=12347
6TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
7db1.tbl1 1 0 1 PRIMARY 1 100
8db2.tbl2 9 5 0 PRIMARY 101 200
9每段第一行列出有差异的从库
10TABLE ：与主库数据不一致的表。
11CHUNK ：与主库数据不一致的表的块编号。
12CNT_DIFF ：从库上的块行数减去主库上的块行数。
13CRC_DIFF ：如果是1表示从库块上的块的CRC与主库块上的CRC不致，否则为0
14CHUNK_INDEX ：用于分块表的索引。
15LOWER_BOUNDARY ：定义块的下边界的索引值。
16UPPER_BOUNDARY：定义块的上边界的索引值。

7. 常用参数

1--replicate: 将校验结果记录到percona.checksums表中，如果表不存在，默认会自动创建
2--databases：指定需要校验的库，多个库用逗号分隔
3--ignore-databases: 不需要校验的库，多个库用逗号分隔
4--tables：指定需要校验的表，多个表用逗号分隔，不同库的表可以写成db1.t1,db2.t1
5--ignore-tables：不需要校验的表，多个表用逗号分隔
6--no-check-binlog-format：不检查binlog 格式

数据修复

检测数据不一致的情况使用pt-table-sync进行修复：

pt-table-sync --print --replicate percona.checksums --databases db12 h=172.16.1.168,u=checksum_user,p=checksum_xxxxxx h=172.16.1.131,u=checksum_user,p=checksum_xxxxxx

--replicate ：指定通过pt-table-checksum得到的表。
--databases ：指定执行同步的数据库，多个用逗号隔开。
--tables= ：指定执行同步的表，多个用逗号隔开。
--sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主
h=127.0.0.1 ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址
u=root ：帐号
p=123456 ：密码
--print ：打印修复的命令，但不执行
--execute ：执行命令

mysql主从数据一致性校验和修复

1. 安装

2. 创建校验用户并授权限

3. 校验

全库校验

不校验mysql和test库

只校验db1,db2两个库

只校验db1.t1,db2.t2两张表

使用dsn表校验

4. 列出与主库数据不一致的表

5. 输出结果说明

数据修复

评论区

相关推荐