使用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
安装工具
wget http://www.percona.com/get/percona-toolkit.tar.gz
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设计的校验和算法很复杂。
附录: 参考博客:
https://blog.csdn.net/melody_mr/article/details/45224249
https://www.cnblogs.com/cchust/p/3521650.html
