现在的位置: 首页 > 关系型数据库 > MySQL数据库 > MySQL故障 > 正文

mysql主从一致性校验修复工具[详解]:pt-table-checksum 和 pt-table-sync

时间:2015年01月12日 | 分类:MySQL故障 | 评论:0 条 | 浏览:9,488 次

在实际DB维护过程中,我们可能会遇到因为数据不一致而引起的主从中断,此时,我们应该如何处理呢!

percona提供了一个很好的工具:percona-toolkit中的 pt-table-checksum 和 pt-table-sync

percona-toolkit的安装这里就不做演示了,各位自行安装

一.检查主从数据的一致性情况:

pt-table-checksum [OPTIONS] [DSN]。

pt-table-checksum:在主上通过执行校验的查询对复制的一致性进行检查,对比主从的校验值,从而产生结果。

DSN指向的是主的地址,该工具的退出状态不为零,如果发现有任何差别,或者如果出现任何警告或错误,不指定任何参数,会直接对本地的所有数据库的表进行检查。

如pt-table-checksum u=root,p=123456

注意事项如下:

(1).测试需要一个既能登录主库,也能登录从库,而且还能同步数据库的账号;

(2).只能指定一个host,必须为主库的IP;

(3).在检查时会向表加S锁;

(4).运行之前需要从库的同步IO和SQL进程是YES状态。

mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO ‘checksums’@’x.x.x.x’

IDENTIFIED BY ‘xxxx’;

(5).如果在配置主从时,忽略复制mysql库时,需要在 主上 和 从上 都执行上面的授权语句

[root@mysql-master1 ~]# pt-table-checksum h='172.16.10.53',u='checksums',p='checksums',P=3306 -d test_0109 --nocheck-replication-filters --replicate=test_checksum.checksums --nocheck-binlog-format --nocheck-plan --recursion-method=hosts

Waiting to check replicas for differences: 0% 00:00 remain

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

01-12T14:39:51 0 0 0 1 0 0.024 test_0109.broker

01-12T14:39:51 0 0 4 1 0 0.010 test_0109.check_conistent

01-12T14:39:51 0 0 0 1 0 0.012 test_0109.partner_alert_rule

01-12T14:39:51 0 1 0 1 0 0.266 test_0109.proc_test

01-12T14:39:51 0 1 529 1 0 0.021 test_0109.project

参数说明:

TS :完成检查的时间。

ERRORS :检查时候发生错误和警告的数量。

DIFFS :0表示一致,1表示不一致。当指定–no-replicate-check时,会一直为0,当指定–replicate-check-only会显示不同的信息。

ROWS :表的行数。

CHUNKS :被划分到表中的块的数目。

SKIPPED :由于错误或警告或过大,则跳过块的数目。

TIME :执行的时间。

TABLE :被检查的表名。

参数意义:

--nocheck-replication-filters :不检查复制过滤器,建议启用。后面可以用–databases来指定需要检查的数据库。

--no-check-binlog-format : 不检查复制的binlog模式,要是binlog模式是ROW,则会报错。

--replicate-check-only :只显示不同步的信息。

--replicate= :把checksum的信息写入到指定表中,建议直接写到被检查的数据库当中。

--databases= :指定需要被检查的数据库,多个则用逗号隔开。

--tables= :指定需要被检查的表,多个用逗号隔开

--recursion-method=: 主机信息

h=172.16.10.53 :Master的地址

u=checksums :用户名

p=checksums :密码

P=3306 :端口

通过DIFFS是1可以看出主从的表数据不一致。通过查看从库上的 test_checksum.checksums 表可以看到主从库的检验信息。
mysql> select * from test_checksum.checksums;
+-----------+--------------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
+-----------+--------------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| test_0109 | broker | 1 | 0.005901 | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 2015-01-12 14:39:51 |
| test_0109 | check_conistent | 1 | 0.000484 | NULL | NULL | NULL | 709a8dc | 4 | 709a8dc | 4 | 2015-01-12 14:39:51 |
| test_0109 | partner_alert_rule | 1 | 0.000825 | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 2015-01-12 14:39:51 |
| test_0109 | proc_test | 1 | 0.000439 | NULL | NULL | NULL | 652c8c22 | 99 | 0 | 0 | 2015-01-12 14:39:51 |
| test_0109 | project | 1 | 0.006852 | NULL | NULL | NULL | 0 | 0 | 862da9de | 529 | 2015-01-12 14:39:51 |
+-----------+--------------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+

通过上面的 this_crc <> master_crc 更能清楚的看出他们的不一致了,通过chunk知道是这个张表的哪个块上的记录出现不一致。要是主的binlog模式是Row 则会报错:

Replica db2 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.

指定–replicate-check-only参数会在前一次pt-table-checksum检验的数据之上比较(不会再执行计算),显示出数据不一致的SLAVE主机名

二.修复主从数据不一致的情况:

通过上面的工具,我们检测出主从之间数据不一致的情况,那此时我们应该如何处理呢?

percona提供了pt-table-sync 用于修复主从数据的一致性

pt-table-sync [OPTIONS] DSN [DSN]。

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

继续上面的复制环境,主和从的表数据不一致,需要修复

pt-table-sync --print --replicate=test_checksum.checksums h=172.16.10.53,u=checksums,p=checksums,P=3306 h=172.16.10.55,u=checksums,p=checksums,P=3306

// 先MASTER的IP,再SLAVE的IP

// 以上是打印出同步语句,如果不一致数据较多,不需要打印出这些语句;

参数意义参考:

--replicate= :指定通过pt-table-checksum得到的表,这2个工具差不多都会一直用。

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

--tables= :指定执行同步的表,多个用逗号隔开。

--sync-to-master :指定一个DSN,即从的IP,他会通过show processlist或show slave status 去自动的找主。

h=127.0.0.1 :服务器地址,命令里有2个ip,第一次出现的是M的地址,第2次是Slave的地址。

u=root :帐号。

p=123456 :密码。

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

--execute :执行命令。
执行数据修复:

pt-table-sync --execute --replicate=test_checksum.checksums h=172.16.10.53,u=checksums,p=checksums,P=3306 h=172.16.10.55,u=checksums,p=checksums,P=3306

此时,再检测主从数据一致性,发现主从数据已经一致了:

[root@mysql-master1 ~]# pt-table-checksum h='172.16.10.53',u='checksums',p='checksums',P=3306 -d test_0109 --nocheck-replication-filters --replicate=test_checksum.checksums --nocheck-binlog-format --nocheck-plan --recursion-method=hosts

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

01-12T14:54:16 0 0 0 1 0 0.018 test_0109.broker

01-12T14:54:16 0 0 4 1 0 0.008 test_0109.check_conistent

01-12T14:54:16 0 0 0 1 0 0.008 test_0109.partner_alert_rule

01-12T14:54:16 0 0 0 1 0 0.016 test_0109.proc_test

01-12T14:54:16 0 0 529 1 0 0.273 test_0109.project

附:
以下错误处理方式:

(1).要是表中没有唯一索引或则主键则会报错:
Can’t make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.

(2).要是从库有的数据,而主库没有,那这个数据怎么处理?
会给出删除SLAVE多余数据,和修复SLAVE缺失数据的SQL语句。

(3).该工具执行检查表动作,检查连接的帐号需要有很高的权限,在一般权限上需要加SELECT, PROCESS, SUPER, REPLICATION SLAVE等权限。
pt-table-checksm 配合pt-table-sync使用,在执行pt-table-sync数据同步之前,一定要执行pt-table-checksm命令检查。

(4).手动执行pt-table-checksum时会出现Diffs cannot be detected because no slaves were found. Please read the –recursion-method documentation for information.

从字面意思上看是,主库找不到从数据库。只需要在从库配置文件/具体目录/my.cnf中添加

report_host=slave_ip

report_port=slave_port

即可。

也可以在pt-table-checksum –recursion-method=hosts

默认是通过show processlist 找到host的值或show slave hosts 找到host的值。

×