mysql主从数据一致性校验及纠错工具

2016-07-12 10:22:17来源:oschina作者:蓝狐乐队人点击

目录


1、概述


2、percona-tooldit工具的安装


3、新建用户


4、pt-table-checksum使用


5、pt-table-sync使用


6、个人总结


1、概述


假如你是一位运维人员,假如你生产环境上部署了mysql系统,再假如你线上的mysql是基于主从复制的架构,那恭喜你,它将可能会带给你主从数据不一致的"恶运"。


由于mysql复制架构原生特性,主从服务器上的数据不可能做”同步“复制,所以延时是必然会有的,即使是不那么繁忙的服务器上,在业务不繁忙的时间里,从库能追上主库的进度,也可能会因为从服务器崩溃、非法关机、程序bug等因素导致在主库上写入的数据与从库上写入的数据不一致的问题。而当这种情况发生时,mysql内部是没有相应的机制来检测主从数据一致性的,对用户而言,你是不知道主从数据已经不一致了。


所以需要一种工具来解决这样的问题,而percona-toolkit工具集中的pt-table-checksum工具就是能在几乎不影响mysql性能的前提下高效的,能检测主从数据不一致的工具。当数据不一致真正产生后,percona-tools工具集中也提供了pt-table-sync工具来修复不一致的数据,这样可免去重新部署从服务器的麻烦。


然而在真正的生产环境上,这两个工具还是有一定的局限性,准确的说应该是mysql这种异步复制的架构导致了工具在使用上的局限性,因为从库会慢于主库,所以在校验主库上的表与校验从库上的表时往往数据是不一致的,这个不致是由于从库的延迟而导致的,所以这两个工具最好运用在以下场景:


a)、从服务器提升为主服务器时,在新的主服务器上线时需要与旧的主服务器进行数据一致性检查


b)、数据迁移后,应该进行数据一致性检查


c)、从库被误操作导致数据更新后,应该进行一致性检查


d)、计划内的数据一致性检查


以下的演示是基于http://zhaochj.blog.51cto.com/368705/1635982一文中搭建的主从复制环境。


2、percona-toolkit工具的安装


先安装所依赖的包及percona-toolkit:


1
2

[root@master~]yum-y
install
perlperl-devellibaiolibaio-develperl-Time-HiResperl-DBD-MySQLperl-IO-Socket-SSL


[root@master~]rpm-ivhpercona-toolkit-2.2.13-1.noarch.rpm


3、新建用户


创建一个非root权限的用户来专门进行数据一致检测等工作,pt-table-checksum与pt-table-sync需要连接到从库中进行相应的数据查看、数据修改等操作,所以在主库上创建一个这样的用户:


1
2
3
4
5
6
7
8

mysql>GRANT
select
,insert,update,delete,create,process,super,replicationslaveON*.*TOmonitor@
'192.168.0.%'
IDENTIFIEDBY
'111111'
;


QueryOK,0rowsaffected(0.01sec)


mysql>GRANT
select
,insert,update,delete,create,process,super,replicationslaveON*.*TOmonitor@
'127.0.0.1'
IDENTIFIEDBY
'111111'
;


QueryOK,0rowsaffected(0.00sec)


mysql>FLUSHPRIVILEGES;


QueryOK,0rowsaffected(0.00sec)


用户新建好后请测试是否能正常接入主库和备库(因为这个用户也需要连接主库,所以创建了上边两个用户)。这个用户所需要的权限真不少,没办法,这是由于percona-toolkit中那两个工具的工作原理所需要的权限,如果为了省事,也可直接给予ALL的权限。


4、pt-table-checksum使用


在mydb1库中的tb1表作为测试,在主库上查看tb1的内容:


1
2
3
4
5
6
7
8
9
10
11

mysql>SELECT*FROMmydb1.tb1;


+----+-------+------+


|
id
|name|age|


+----+-------+------+


|1|tom|12|


|2|jem|23|


|3|jason|29|


|4|aaa|30|


|5|b|69|


+----+-------+------+


5rows
in
set
(0.01sec)


在从库上查看mydb1.tb1的内容:


1
2
3
4
5
6
7
8
9
10
11

mysql>
select
*frommydb1.tb1;


+----+-------+------+


|
id
|name|age|


+----+-------+------+


|1|tom|12|


|2|jem|23|


|3|jason|29|


|4|aaa|30|


|5|b|69|


+----+-------+------+


5rows
in
set
(0.00sec)


此时主从的数据都是一致的,用pt-table-checksum工具测试一下看输出的是什么结果:


1
2
3

[root@master~]
#pt-table-checksum--nocheck-replication-filters--replicate=mydb1.checksums--databases=mydb1h=127.0.0.1,u=monitor,p=111111


Replicaslavehasbinlog_formatROW
which
couldcausept-table-checksumto
break
replication.Please
read
"Replicasusingrow-basedreplication"
in
theLIMITATIONSsectionofthetool'sdocumentation.Ifyouunderstandtherisks,specify--no-check-binlog-
format
todisablethischeck.


#报错了,因为我的mysql环境的二进制日志是基于行的,即‘binlog_format=ROW’,如果是基于行的复制环境,percona官方是不建议使用pt-table-checksum工具来进行数据的一致性检查的,但它又提供了一个选项来跳过此检查。


各常用选项意义:


--nocheck-replication-filters:不检查复制过虑,我们用--databases来指定需要检查的数据库


--replicate:把校验的信息写入指定的表中


--no-check-binlog-format:不检查二进制日志文件格式


--replicate-check-only:只显示有不一致数据的信息


--databases:指定校验的数据库,多个用逗号隔开


--tables:指定校验的表,多个用逗号隔开


h:主机,指主服务器IP


u:帐号


p:密码



加上“--no-check-binlog-format”选项后再来测试一次:


1
2
3
4
5

[root@master~]
#pt-table-checksum--nocheck-replication-filters--replicate=mydb1.checksums--no-check-binlog-format--databases=mydb1--h=127.0.0.1,u=monitor,p=111111

TSERRORSDIFFSROWSCHUNKSSKIPPEDTIMETABLE


04-21T18:00:59005100.280mydb1.tb1


04-21T18:00:59002100.331mydb1.tb2


#注意观察“DIFFS”那一列,如果数据有不一致的这里不是“0”值。


运行上边的命令后可能会报“no slaves were found”类似的错误,这里因为无法连接从服务器所导致。当运行上边的指令后,pt-table-checksum连接支主mysql后会采取递归的方法去自动查找主的从服务器有哪些,先是运行“show processlist;”,然后是运行“show slave hosts”来查找,如果遇到不能连接从服务器的错误时,可以在从服务器的my.cnf加入“report_host=从服务器IP”来主动告诉主服务器它就主的从,并在运行的pt-table-checksum命令中加上“--recursion-method=hosts”选项,这样在主服务器可以用“show slave hosts”指令就可查看到从服务器IP地址。



现在我们人为的使主从数据不一致,在从服务器上把tb1表中id号为5的age那一列改为20:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql>updatemydb1.tb1
set
age=20where
id
=5;


QueryOK,1rowaffected(0.01sec)


Rowsmatched:1Changed:1Warnings:0


mysql>
select
*frommydb1.tb1;


+----+-------+------+


|
id
|name|age|


+----+-------+------+


|1|tom|12|


|2|jem|23|


|3|jason|29|


|4|aaa|30|


|5|b|20|


+----+-------+------+


5rows
in
set
(0.00sec)


这样主从数据就不一致了,我们再主服务器上运行pt-table-checksum工具来测试一下:


1
2
3
4
5

[root@master~]
#pt-table-checksum--nocheck-replication-filters--replicate=mydb1.checksums--no-check-binlog-format--recursion-method=hosts--databases=mydb1h=127.0.0.1,u=monitor,p=111111

TSERRORSDIFFSROWSCHUNKSSKIPPEDTIMETABLE


04-21T18:27:00015100.307mydb1.tb1


04-21T18:27:01002100.048mydb1.tb2


#tb1这个表的"DIFFS"的值变为了“1”。


接下来该pt-table-sync工具上场了。


5、pt-table-sync使用


可以用“--print”选项来看一下主从上到底是哪里不一致了:


1
2
3
4

[root@master~]
#pt-table-sync--replicate=mydb1.checksumsh=127.0.0.1,u=monitor,p=111111h=192.168.0.202,u=monitor,p=111111--charset=utf8--print


REPLACEINTO`mydb1`.`tb1`(`
id
`,`name`,`age`)VALUES(
'5'
,
'b'
,
'69'
)/*percona-toolkitsrc_db:mydb1src_tbl:tb1src_dsn:A=utf8,h=127.0.0.1,p=...,u=monitordst_db:mydb1dst_tbl:tb1dst_dsn:A=utf8,h=192.168.0.202,p=...,u=monitorlock:1transaction:1changing_src:mydb1.checksumsreplicate:mydb1.checksumsbidirectional:0pid:3205user:roothost:master*/;


#上边的输出信息表示从库上id=5那行的age的值应该是69。


#命令中有两组“h=,u=,p=”,第一组指定的是主服务器,第二组指向从服务器。


各个常用选项的意义:


--replicate= :表示基于pt-table-checksum工具生成的checksums表来修复有问题的数据


--databases=:表示执行同步的数据库,多个用逗号隔开


--tables=:表示执行同步的数据表,多个用逗号隔开


h=:服务器主机名


u=:帐号


p=:密码


--print:只打印,但不执行命令


--execute:执行命令



确认数据真不一致后那就把“--print”选项换成“--execute”来执行替换语句:


1

[root@master~]
#pt-table-sync--replicate=mydb1.checksumsh=127.0.0.1,u=monitor,p=111111h=192.168.0.202,u=monitor,p=111111--charset=utf8--execute


从库上的数据手动纠正后再运行pt-table-checksum工具来看一下这两个表的数据是否已经一致了:


1
2
3
4

[root@master~]
#pt-table-checksum--replicate=mydb1.checksums--nocheck-replication-filters--no-check-binlog-format--databases=mydb1h=127.0.0.1,u=monitor,p=111111

TSERRORSDIFFSROWSCHUNKSSKIPPEDTIMETABLE


04-21T21:42:31005100.309mydb1.tb1


04-21T21:42:32002100.304mydb1.tb2


这样数据就被成功纠正。


6、个人总结


这两个工具一般都是结合起来使用,弥补了mysql没有数据一致性校验的机制,让运维人员在主从复制架构中更能维护得更好。基于percona官方的说明在pt-table-checksum工具中最好让复制是基于语句的复制,而基于语句和基于行的复制各有各的优缺点,如果考虑到在后期的维护中会常用到pt-table-checksum工具,个人认为还是该把binlog_format设置为statement,或者mixed。


最后要说的是,如果在生产环境上真的产生了主备数据不一致,而不是延迟导致的,那在利用这些工具对数据操作时切记记得对源数据要进行备份,不管源数据是完好的,还是有些数据已被损坏,你在做数据修复工作前一定要把源数据做一个备份,在数据恢复这样一个高压的环境,谁能保证你做的操作都是规范且正确的?如果操作失误,你起码还有回滚的机会。

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台