mysql pttablechecksum_使用pt-table-checksum检测MySQL主从数据一致性

1.环境:

主库 10.101.67.15

从库 10.101.67.13 10.101.67.14 10.10.32.20

2.安装checksum

安装依赖

yum install perl perl-devel perl-Time-HiRes perl-DBI perl-DBD-MySQL perl-Digest-MD5

安装工具

tar zxf percona-toolkit.tar.gz

cd percona-toolkit-version

perl Makefile.PL

make && make install

3. 主库创建数据库以及用户账号

3.1 创建数据库

Create database pt CHARACTER SET utf8;

3.2 创建用户checksum并授权

GRANT ALL ON *.* TO 'checksum'@'10.101.67.15' IDENTIFIED BY 'checksum';

flush privileges;

3.3 创建测试数据表

use pt;

CREATE TABLE IF NOT EXISTS 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;

进行checksum

4.1 准备数据

手动更改13库数据

628950fdb328ee492595ed44a2bf0673.png

更改14库表数据

fcb1eae0b01de076ea6cb45778aa4d77.png

更改20库表数据

4e11b90a78de9112b3cd87c89f706c37.png

4.2 进行checksum

在主库所在机器编写脚本

checksum.sh

*******************************************************************

#!/bin/bash

date >> /root/db/checksum.log

pt-table-checksum --nocheck-binlog-format --nocheck-plan

--nocheck-replication-filters --replicate=pt.checksums --set-vars

innodb_lock_wait_timeout=120 --databases UAR_STATISTIC -u'checksum' -p'checksum'

-h'10.101.67.15' >> /root/db/checksum.log

date >> /root/db/checksum.log

执行: nohup sh checksum.sh &

4.3 检查checksum.log, 查看结果

Thu Aug 2 09:50:59 CST 2018

Checking if all tables can be checksummed ...

Starting checksum ...

TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE

08-02T09:51:00 0 1 48955 0 5 0 0.738 UAR_STATISTIC.app_activity_user_at_appv_day

08-02T09:51:00 0 1 66733 0 1 0 0.481

UAR_STATISTIC.app_activity_user_at_channel_appv_day

08-02T09:51:01 0 1 53902 0 1 0 0.404

UAR_STATISTIC.app_activity_user_at_channel_day

08-02T09:51:01 0 0 36672 0 1 0 0.312 UAR_STATISTIC.app_activity_user_at_day

08-02T09:51:04 0 0 162417 0 1 0 3.144

UAR_STATISTIC.app_at_appv_country_operator_day

08-02T09:51:08 0 0 235261 0 5 0 4.057 UAR_STATISTIC.app_at_appv_country_prov_day

08-02T09:51:09 0 0 17961 0 1 0 0.379 UAR_STATISTIC.app_at_appv_day

.....

Thu Aug 2 10:26:10 CST 2018

检查15G SQL数据总共执行花费了36分钟, 检查出不同的行有三行

校验结果核查

登录三台机器的从库, 在各从库执行如下命令, 可以看出哪个数据库的哪张表不一致

USE pt;

SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks

FROM checksums

WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR

ISNULL(master_crc) <> ISNULL(this_crc) ) AND db = 'UAR_STATISTIC'

GROUP BY db, tbl;

5.1 机器13

82dbf0a616fd755a3763c0c2d8ed067f.png

5.2 机器14

2be06597c9da01ce617d81cdfd38b958.png

5.3 机器20

a53ea976516702b215b093dac77b36ab.png

修复不一致

6.1 打印修复语句

找到不一致的从库以及表执行如下命令,

.打印出sql语句，人工干预到Slave库执行(推荐)

6.1.1 机器13

pt-table-sync --print --sync-to-master

h=10.101.67.13,P=3306,u=checksum,p=checksum --databases=UAR_STATISTIC

--replicate=pt.checksums;

执行结果

[root@node3 db]# pt-table-sync --print --sync-to-master

h=10.101.67.13,P=3306,u=checksum,p=checksum --databases=UAR_STATISTIC

--replicate=pt.checksums;

输出结果:

REPLACE INTO `UAR_STATISTIC`.`app_activity_user_at_appv_day`(`at`,

`appv`, `day`, `activity_user`, `total_user`) VALUES ('', 'v19.36',

'20170429', NULL, '1') /*percona-toolkit src_db:UAR_STATISTIC

src_tbl:app_activity_user_at_appv_day

src_dsn:P=3306,h=10.101.67.15,p=...,u=checksum dst_db:UAR_STATISTIC

dst_tbl:app_activity_user_at_appv_day

dst_dsn:P=3306,h=10.101.67.13,p=...,u=checksum lock:1 transaction:1

changing_src:pt.checksums replicate:pt.checksums bidirectional:0 pid:24190

user:root host:node3*/;

在13数据库执行提示sql

REPLACE INTO `UAR_STATISTIC`.`app_activity_user_at_appv_day`(`at`,

`appv`, `day`, `activity_user`, `total_user`) VALUES ('', 'v19.36',

'20170429', NULL, '1')

即可修复数据不一致问题

6.1.2 机器14和20

同理可以执行如下语句, 打印出修复sql, 在各从库MySQL执行, 从而修复

pt-table-sync --print --sync-to-master

h=10.101.67.14,P=3306,u=checksum,p=checksum --databases=UAR_STATISTIC

--replicate=pt.checksums;

pt-table-sync --print --sync-to-master

h=10.10.32.20,P=3306,u=checksum,p=checksum --databases=UAR_STATISTIC

--replicate=pt.checksums;

6.2 自动修复

在上边三条语句里添加 --execute 选项 ,将自动修复

pt-table-sync --print --sync-to-master

h=10.10.32.20,P=3306,u=checksum,p=checksum –-execute --databases=UAR_STATISTIC

--replicate=pt.checksums;

6.3 重新校验

手动修复后, 重新校验, 查看是否还有不一致的数据

7. 校验原理以及风险

它通过在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。这种校验是分表进行的，在每个表内部又是分块进行的，而且pt工具本身提供了非常多的限流选项，检测时会自动判断复制延迟、

master的负载， 超过阀值后会自动将检测暂停，减小对线上服务的影响。

7.1 数据块checksum值的计算

如果一行一行的计算checksum再去和从库比较，那么效率会非常低下。pt工具选择智能分析表上的索引，然后把表的数据split成一个个chunk，计算的时候也是以chunk为单位。因此引入了聚合函数BIT_XOR()。它的功能可以理解为把这个chunk内的所有行的数据拼接起来，再计算crc32的值，就得到这个chunk的checksum值。sql语句如下：

这其中还有count(*)，用来计算chunk包含的行数。每一次对chunk进行checksum后，pt工具都会对耗时进行统计分析，并智能调整下一个chunk的大小，避免chunk太大对线上造成影响，同时也要避免chunk太小而效率低下。

7.2 一致性如何保证

当pt工具在计算主库上某chunk的checksum时，主库可能还在更新，同时从库可能延迟使得relay-log中还有与这个chunk数据相关的更新，那该怎么保证主库与从库计算的是”同一份”数据？答案是加for

update当前读锁，这保证了主库的某个chunk内部数据的一致性。否则，1000个人chekcusm同样的1000行数据，可能得到1000个不同的结果，你无法避开mvcc的干扰！获得for

update锁后，pt工具开始计算chunk的checksum值，并把计算结果保存到pt工具自建的结果表中(采用replace

into select的方式)，然后释放锁。该语句最终会传递到从库并执行相同的计算逻辑。

7.3 风险

pt-table-checksum 不是对一个表仅作一个校验和，因为如果表特别大，将会对DB造成很大的负载，影响正常业务。一个表一个校验和就退化到mysql自带命令CHECKSUM

TABLE了，不仅需要锁表，而且不准确。pt-table-checksum将表按用户设置的块大小，将表分成若干份，然后对每个块计算一个校验和。这样即使表特别大，分块后也只会锁住部分记录，对DB的负载压力也大大降低。由于多个表校验可以并发，可以大大提高校验效率，通过参数-max-load可以防止load过大。

pt-table-checksum基本能满足我们的日常需求，但是它还有一些需要完善的地方，首先，仅仅支持表粒度的并发，当检查一个大表时，需要耗费大量的时间，另外多表并行执行时，并行度也不能通过参数的设置，而是通过--max-load间接设置。其次，通过分块生成校验和虽然加快了校验速度，但1000行算一个4字节的校验值(默认是一个块1000行)，产生冲突的可能性很大，即使pt-table-checksum设计的校验和算法很复杂。

附录: 参考博客: