Mysql主从同步percona-toolkit数据一致性监测

引言

在mysql工作中接触最多的就是mysql replication,mysql在复制方面还是会有一些常规问题,比如主库宕机或者从库宕机有可能会导致复制中断,通常需要进行人为修复,或者很多时候需要把一个从库提升为主库,但对从库和主库的数据一致性不能保证一样。这种情况下就需要使用percona-toolkit工具的pt-table-checksum组件来检查主从数据的一致性;如果发现不一致的数据,可以通过pt-table-sync修复;还可以通过pt-heartbeat监控主从复制延迟。当然如果数据量小,slave只是当做一个备份使用,那么出现数据不一致完全可以重做,或者通过其他方法解决。如果数据量非常大,重做就是非常蛋碎的一件事情了。比如说,线上数据库做了主从同步环境,数据库在进行了迁移后,需要对mysql迁移(Replication)后的数据一致性进行校验,但又不能对生产环境使用造成影响,pt-table-checksum成为了绝佳也是唯一的检查工具。

Percona-toolkit介绍

percona-toolkit是一组高级命令行工具的集合,用来执行各种通过手工执行非常复杂和麻烦的mysql和系统任务,这些任务包括:

1
2
3
4
5
6
1)检查master和slave数据的一致性
2)有效地对记录进行归档
3)查找重复的索引
4)对服务器信息进行汇总
5)分析来自日志和tcpdump的查询
6)当系统出问题的时候收集重要的系统信息

Percona-toolkit组件

percona-toolkit工具中最主要的三个组件分别是:

1
2
3
1)pt-table-checksum 负责监测mysql主从数据一致性
2)pt-table-sync 负责当主从数据不一致时修复数据,让它们保存数据的一致性
3)pt-heartbeat 负责监控mysql主从同步延迟

Percona-toolkit官网下载

1
https://www.percona.com/downloads/percona-toolkit/LATEST/

Percona-toolkit源码安装(建议主库和从库都安装)

1、安装依赖
1
yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL
2、解包
1
tar -zxvf percona-toolkit-3.0.5.tar.gz
3、配置
1
2
cd percona-toolkit-3.0.5
perl Makefile.PL

POWwY6.png

报错:BEGIN failed–compilation aborted at Makefile.PL line 1.

缺少依赖,我们来安装一下之后再试

1
yum -y install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
4、安装
1
make && make install

pt-table-checksum检测数据是否一致

pt-table-checksum 是 Percona-Toolkit的组件之一,用于检测MySQL主、从库的数据是否一致。其原理是在主库执行基于statement的sql语句来生成主库数据块的checksum,把相同的sql语句传递到从库执行,并在从库上计算相同数据块的checksum,最后,比较主从库上相同数据块的checksum值,由此判断主从数据是否一致。检测过程根据唯一索引将表按row切分为块(chunk),以为单位计算,可以避免锁表。检测时会自动判断复制延迟、 master的负载, 超过阀值后会自动将检测暂停,减小对线上服务的影响。

pt-table-checksum 默认情况下可以应对绝大部分场景,官方说,即使上千个库、上万亿的行,它依然可以很好的工作,这源自于设计很简单,一次检查一个表,不需要太多的内存和多余的操作;必要时,pt-table-checksum 会根据服务器负载动态改变 chunk 大小,减少从库的延迟。

为了减少对数据库的干预,pt-table-checksum还会自动侦测并连接到从库,当然如果失败,可以指定–recursion-method选项来告诉从库在哪里。它的易用性还体现在,复制若有延迟,在从库 checksum 会暂停直到赶上主库的计算时间点(也通过选项–设定一个可容忍的延迟最大值,超过这个值也认为不一致)。

为了保证主数据库服务的安全,该工具实现了许多保护措施:

1
2
3
1)自动设置 innodb_lock_wait_timeout 为1s,避免引起
2)默认当数据库有25个以上的并发查询时,pt-table-checksum会暂停。可以设置 --max-load 选项来设置这个阀值
3)当用 Ctrl+C 停止任务后,工具会正常的完成当前 chunk 检测,下次使用 --resume 选项启动可以恢复继续下一个 chunk

主从数据一致检查(本例中192.168.10.27是主库,192.168.10.29是从库)

1、主库上授权
1
2
mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON *.* TO 'check'@'192.168.10.27' identified by '123456';
mysql> flush privileges;
2、从库上授权
1
2
mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'check'@'192.168.10.27' IDENTIFIED  BY '123456';
mysql> flush privileges;
3、开始检测
1
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=nongkaige.checksums --create-replicate-table --databases=nongkaige h=192.168.10.27,u=check,p=123456,P=3306

POWDSO.png

常用参数说明:

1
2
3
4
5
6
7
8
9
10
--nocheck-replication-filters :不检查复制过滤器,建议启用。后面可以用--databases来指定需要检查的数据库。
--no-check-binlog-format : 不检查复制的binlog模式,要是binlog模式是ROW,则会报错。
--replicate-check-only :只显示不同步的信息。
--replicate= :把checksum的信息写入到指定表中,建议直接写到被检查的数据库当中。
--databases= :指定需要被检查的数据库,多个则用逗号隔开。
--tables= :指定需要被检查的表,多个用逗号隔开
h= :Master的地址
u= :用户名
p=:密码
P= :端口

注意:第一次运行的时候需要添加–create-replicate-table参数,后续再运行时就不需要加了

检测结果参数说明:

1
2
3
4
5
6
7
8
TS :完成检查的时间。
ERRORS :检查时候发生错误和警告的数量。
DIFFS :0表示一致,1表示不一致。当指定--no-replicate-check时,会一直为0,当指定--replicate-check-only会显示不同的信息。
ROWS :表的行数。
CHUNKS :被划分到表中的块的数目。
SKIPPED :由于错误或警告或过大,则跳过块的数目。
TIME :执行的时间。
TABLE :被检查的表名。
4、模拟主从数据不一致,再检测查看结果

在从库插入一条数据,而主库上则没有该条数据:

从库:

POhuGV.png

主库:

POhMxU.png

再来检测一次:

POhlMF.png

pt-table-sync修复数据不一致

如果通过pt-table-checksum 检查找到了不一致的数据表,那么如何同步数据呢?即如何修复MySQL主从不一致的数据,让他们保持一致性呢?这时候可以利用另外一个工具pt-table-sync。

使用方法:

pt-table-sync: 高效的同步MySQL表之间的数据,他可以做单向和双向同步的表数据。他可以同步单个表,也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。

在上面的检测数据时发现主从不一致,现在需要修复,修复命令如下:

先是master的ip、用户、密码,然后是slave的ip、用户、密码,直接在master主库上执行修复操作,通–execute参数

1
pt-table-sync --replicate=nongkaige.checksums h=192.168.10.27,u=check,p=123456 h=192.168.10.29,u=check,p=123456 --execute

POhGZ9.png

报错了Can’t make changes on the master because no unique index exists,大概意思是表中没有唯一索引或者主键,我们去创建一下即可。

1
2
mysql> use nongkaige;
mysql> alter table nong add primary key(id);

增加完主键之后我们再执行一遍:(没有任何输出)

1
pt-table-sync --replicate=nongkaige.checksums h=192.168.10.27,u=check,p=123456 h=192.168.10.29,u=check,p=123456 –execute

命令执行完成之后,我们再检测一下数据库一致性:

1
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=nongkaige.checksums --databases=nongkaige h=192.168.10.27,u=check,p=123456,P=3306

POhYI1.png

如果觉得不放心,可以去数据库中检查一遍:

主库:

POhdxO.png

从库:

POhBse.png

常用参数说明:

1
2
3
4
5
6
7
8
9
--replicate= :指定通过pt-table-checksum得到的表,这2个工具差不多都会一直用。
--databases= : 指定执行同步的数据库。
--tables= :指定执行同步的表,多个用逗号隔开。
--sync-to-master :指定一个DSN,即从的IP,他会通过show processlist或show slave status 去自动的找主。
h= :服务器地址,命令里有2个ip,第一次出现的是Master的地址,第2次是Slave的地址。
u= :帐号。
p= :密码。
--print :打印出不一致的数据,但不执行修复命令。
--execute :直接执行修复命令。

建议:

通过(–print)打印出来了修复数据的sql语句,可以手动的在slave从库上执行,让他们数据保持一致性,修复数据的时候,最好还是用–print打印出来的好,这样就可以知道哪些数据有问题,可以人为的干预下。不然直接执行了,出现问题之后更不好处理。总之还是在处理之前做好数据的备份工作。

编写脚本

为了确保主从数据的一致性,可以编写监控脚本,定时检查。当检查到主从数据不一致时,强制修复数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
vim /root/pt_nongkaige.sh

#!/bin/bash
source /etc/profile
NUM=$(pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=nongkaige.checksums --databases=nongkaige h=192.168.10.27,u=check,p=123456,P=3306 | awk '{print $3}' | sed -n '2p')
if [ $NUM -eq 1 ];then
/usr/local/bin/pt-table-sync --replicate=nongkaige.checksums h=192.168.10.27,u=check,p=123456 h=192.168.10.29,u=check,p=123456 --print
/usr/local/bin/pt-table-sync --replicate=nongkaige.checksums h=192.168.10.27,u=check,p=123456 h=192.168.10.29,u=check,p=123456 --execute
else
echo "data is ok"
fi

chmod +x /root/pt_nongkaige.sh

我们先去从数据库插入一条数据,使主从数据不一致,然后再来执行脚本查看结果

POhsZd.png

这时候主从数据已经不一致了,这时候,就可以使用上边写的脚本来试试了

1
sh /root/pt_nongkaige.sh

POhgit.png

显示这么多内容是因为我使用了–print参数,脚本第二句才是真正的执行了,接下来我们查看一下数据库是否恢复了一致

主库:

POhRRf.png

从库:

POhWz8.png

现在数据已经恢复一致了,那我们执行脚本看看是什么效果

1
sh /root/pt_nongkaige.sh

POhvyF.png

计划任务5分钟执行一次检测和修复

1
2
3
4
5
crontab -e

*/5 * * * * /root/pt_nongkaige.sh > /dev/null 2>&1

service crond restart

注意:这里说明一个问题,浪费了我很长时间才解决的,上边的脚本中,我在前面加了一句source /etc/profile,为什么要加这句,因为我发现,没加这句命令之前,我这计划任务怎么执行的结果和手动执行的脚本结果都是不一样,结果不一样,那脚本就不会生效,百度了一下,需要在脚本前面加上source /etc/profile这条命令,否则脚本执行得出的结果不一样,千万记住!!

说明

percona-toolkit还有很多其它组件命令,都非常的实用,本文只写了这两个,还有一个pt-heartbeat监控mysql主从复制延迟也是很实用,总之,笔者就不写这么多了,具体使用方法可以百度百科或者google找一下

博主QQ:1012405802
技术交流QQ群:830339411
版权声明:网站内容有原创和转载,如有侵权,请联系删除,谢谢!!
感谢打赏,93bok因你们而精彩!!(支付宝支持花呗)
0%