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

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

目录

1.概观

2.percona-tooldit工具的安装

3.创建新用户

4.pt表校验和的使用

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

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)

新用户创建后，请测试其是否可以正常访问主库和备用库(因为该用户也需要连接主库，所以上面两个用户已经创建)。这个用户需要很多权限，没办法。这是由于percona-toolkit中那两个工具的工作原理所要求的权限。如果是为了省事，也可以直接给所有权限。

4.pt表校验和的使用

以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)

从库中查看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)

此时，主从的数据是一致的。用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工具来进行数据的一致性检查的，但它又提供了一个选项来跳过此检查。

常用选项的含义:

-nocheck-replication-filters:我们使用-databases来指定要检查的数据库，而不是check-nocheck-replication-filters。

-replicate:将验证过的信息写入指定的表中。

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

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

-databases:指定要检查的数据库，多个数据库用逗号分隔。

-tables:指定要检查的表，多个表用逗号分隔。

H:host是指主服务器的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”值。

运行上述命令后，您可能会报告类似“没有找到从服务器”的错误，这是由于无法连接到从服务器而导致的。运行以上指令后，pt-table-checksum会在连接mysql后，通过递归自动找出哪些从服务器在主服务器中。首先，运行“显示进程列表”，然后运行“showslavehosts”找出答案。如果遇到无法连接从服务器的错误，可以在从服务器的my.cnf中添加“report_host=slaveserverIP”主动告诉主服务器是主服务器的从服务器，在运行的pt-table-checksum命令中添加选项“-recursion-method=hosts=hosts”，这样就可以使用”。

现在我们人为的让主从数据不一致，在从服务器上把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)

所以主从数据不一致。让我们在主服务器上运行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”。

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

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=   ”，第一组指定的是主服务器，第二组指向从服务器。

每个常见选项的含义:

-replicate=:表示根据pt-table-checksum工具生成的校验和表修复有问题的数据。

-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

手动更正库中的数据，然后运行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

通过这种方式，成功地修正了数据。

6.个人总结

这两种工具一般结合使用，弥补了mysql缺乏数据一致性检查机制的不足，使运维人员在主从复制架构下维护得更好。根据percona的官方说明，在pt-table-checksum工具中，最好是基于语句进行复制，而基于语句和基于行的复制各有优缺点。如果考虑到后期维护会经常用到pt-table-checksum工具，个人认为应该将binlog_format设置为statement或者mixed。

最后，如果主数据和备份数据的不一致真的发生在生产环境中，而不是延迟，那么在使用这些工具 *** 作数据时，记得备份源数据。无论源数据是完整的还是某些数据已经损坏，在进行数据修复工作之前，都必须对源数据进行备份。在数据恢复这样的高压环境下，谁能保证你的 *** 作是规范的，正确的？犯了错，至少还有机会回滚。