Heim > Datenbank > MySQL-Tutorial > mysql主从数据一致性校验及纠错工具

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

WBOY
Freigeben: 2016-06-07 14:56:03
Original
1318 Leute haben es durchsucht

目录 1、概述 2、percona-tooldit工具的安装 3、新建用户 4、pt-table-checksum使用 5、pt-table-sync使用 6、个人总结 1、概述 假如你是一位运维人员,假如你生产环境上部署了mysql系统,再假如你线上的mysql是基于主从复制的架构,那恭喜你,它将可能会带

目录

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:

[root@master ~] yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL perl-IO-Socket-SSL
[root@master ~] rpm -ivh percona-toolkit-2.2.13-1.noarch.rpm
Nach dem Login kopieren

3、新建用户

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

mysql> GRANT select,insert,update,delete,create,process,super,replication slave ON *.* TO monitor@'192.168.0.%' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT select,insert,update,delete,create,process,super,replication slave ON *.* TO monitor@'127.0.0.1' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Nach dem Login kopieren

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

4、pt-table-checksum使用

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

mysql> SELECT * FROM mydb1.tb1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   12 |
|  2 | jem   |   23 |
|  3 | jason |   29 |
|  4 | aaa   |   30 |
|  5 | b     |   69 |
+----+-------+------+
5 rows in set (0.01 sec)
Nach dem Login kopieren

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

mysql> select * from mydb1.tb1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   12 |
|  2 | jem   |   23 |
|  3 | jason |   29 |
|  4 | aaa   |   30 |
|  5 | b     |   69 |
+----+-------+------+
5 rows in set (0.00 sec)
Nach dem Login kopieren

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

[root@master ~]# pt-table-checksum  --nocheck-replication-filters --replicate=mydb1.checksums --databases=mydb1 h=127.0.0.1,u=monitor,p=111111
Replica slave has binlog_format ROW which could cause pt-table-checksum to break replication.  Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation.  If you understand the risks, specify --no-check-binlog-format to disable this check.
#报错了,因为我的mysql环境的二进制日志是基于行的,即‘binlog_format=ROW’,如果是基于行的复制环境,percona官方是不建议使用pt-table-checksum工具来进行数据的一致性检查的,但它又提供了一个选项来跳过此检查。
Nach dem Login kopieren

各常用选项意义:

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

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

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

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

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

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

h:主机,指主服务器IP

u:帐号

p:密码


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

[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
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
04-21T18:00:59      0      0        5       1       0   0.280 mydb1.tb1
04-21T18:00:59      0      0        2       1       0   0.331 mydb1.tb2
#注意观察“DIFFS”那一列,如果数据有不一致的这里不是“0”值。
Nach dem Login kopieren

运行上边的命令后可能会报“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:

mysql> update mydb1.tb1 set age=20 where id=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mydb1.tb1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   12 |
|  2 | jem   |   23 |
|  3 | jason |   29 |
|  4 | aaa   |   30 |
|  5 | b     |   20 |
+----+-------+------+
5 rows in set (0.00 sec)
Nach dem Login kopieren

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

[root@master ~]# pt-table-checksum  --nocheck-replication-filters --replicate=mydb1.checksums --no-check-binlog-format --recursion-method=hosts --databases=mydb1 h=127.0.0.1,u=monitor,p=111111
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
04-21T18:27:00      0      1        5       1       0   0.307 mydb1.tb1
04-21T18:27:01      0      0        2       1       0   0.048 mydb1.tb2
#tb1这个表的"DIFFS"的值变为了“1”。
Nach dem Login kopieren

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

5、pt-table-sync使用

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

[root@master ~]# pt-table-sync --replicate=mydb1.checksums h=127.0.0.1,u=monitor,p=111111 h=192.168.0.202,u=monitor,p=111111 --charset=utf8 --print
REPLACE INTO `mydb1`.`tb1`(`id`, `name`, `age`) VALUES ('5', 'b', '69') /*percona-toolkit src_db:mydb1 src_tbl:tb1 src_dsn:A=utf8,h=127.0.0.1,p=...,u=monitor dst_db:mydb1 dst_tbl:tb1 dst_dsn:A=utf8,h=192.168.0.202,p=...,u=monitor lock:1 transaction:1 changing_src:mydb1.checksums replicate:mydb1.checksums bidirectional:0 pid:3205 user:root host:master*/;
#上边的输出信息表示从库上id=5那行的age的值应该是69。
#命令中有两组“h=  ,u=   ,p=   ”,第一组指定的是主服务器,第二组指向从服务器。
Nach dem Login kopieren

各个常用选项的意义:

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

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

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

h= :服务器主机名

u= :帐号

p= :密码

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

--execute:执行命令


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

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

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

[root@master ~]# pt-table-checksum --replicate=mydb1.checksums --nocheck-replication-filters --no-check-binlog-format --databases=mydb1 h=127.0.0.1,u=monitor,p=111111
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
04-21T21:42:31      0      0        5       1       0   0.309 mydb1.tb1
04-21T21:42:32      0      0        2       1       0   0.304 mydb1.tb2
Nach dem Login kopieren

这样数据就被成功纠正。

6、个人总结

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

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


Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage