MySQL主从服务器数据一致性的核对与修复

赞助

如果你觉得我写得还行,欢迎付费支持。

我上一次遇到MySQL主从服务器数据一致性问题,想想是几年前的事情了,还依稀记得当时惊慌失措的情景,好在最后借助Maatkit解决了问题。几年后,当我再次面对同样的问题时,Maatkit已经不复存在,转而成为了Percona Toolkit的一部分,不变的是我依旧手忙脚乱,所以还是记录一下吧,保不准啥时候又会遇到这个问题。

如果你在MySQL从服务器上遇到类似下面的错误信息,那么恭喜你中招了:

mysql> SHOW SLAVE STATUS\G

Last_Errno: 1062
Last_Error: Error 'Duplicate entry '...' for key ...' on query.

为啥会出现唯一索引键值重复?最大的可能是错误的对从服务器做了写操作!出现此类错误的时候,很多人会用sql_slave_skip_counter操作跳过错误,甚至有人写了脚本,如果有多个错误,就循环多次执行sql_slave_skip_counter:

mysql> SET GLOBAL sql_slave_skip_counter = 1;
mysql> START SLAVE;

可惜,即便sql_slave_skip_counter操作能够暂时让主从恢复工作,但多半数据一致性已经被破坏的更严重了,早晚有一天被掩盖的问题会再次爆发出来。

Percona Toolkit里的pt-table-checksumpt-table-sync可以搞定此类问题。它们的安装很简单,可以依照自己的操作系统选择下载rpm或者deb软件包来安装,当然也可以使用源代码来安装,不过要注意的是,必须确保系统已经安装了依赖的Perl软件包:

shell> perl -MCPAN -e 'install DBI'
shell> perl -MCPAN -e 'install DBD::mysql'
shell> perl -MCPAN -e 'install Term::ReadKey'

顺便说一下,我在安装某些Perl模块的时候,出现类似下面的错误提示:

Can’t locate object method “install” via package “…”

如果你也遇到了类似的问题,可以进入到Perl命令行安装:

shell> perl -MCPAN -e shell
cpan> install ...

安装Percona Toolkit的剩余步骤就是Perl软件的固定打法了:

shell> perl Makefile.PL
shell> make
shell> make install

补充:Percona Toolkit里的pt-slave-restart可以替代sql_slave_skip_counter:

shell> pt-slave-restart
    --host=<HOST>
    --port=<PORT>
    --user=<USER>
    --password=<PASSWORD>
    --error-numbers=1062

前戏进行到这里应该可以了,下面让我们直捣黄龙,看看如何解决问题:

MySQL主从服务器数据一致性的核对

通过在主服务器上运行pt-table-checksum,它会通过一系列的MySQL函数计算每个表的散列值,利用主从复制关系,把同样的计算过程在从服务器上重放,从而就拿到了主从服务器各自的散列值,只要比较散列值是否相同就OK了。

这里面有两点需要说明:

  • 计算表的散列值时,pt-table-checksum并不是直接计算整个表的散列值,而是分块计算,这样就避免了造成从服务器长时间的延迟。
  • 因为通过MySQL函数计算散列的过程需要在从服务器上重放,所以主从复制的格式必须是基于STATEMENT的,不能是基于ROW的。

实际操作时的命令大致如下:

shell> pt-table-checksum \
    --replicate=percona.checksums \
    --host=<MASTER_HOST> \
    --user=<MASTER_USER> \
    --password=<MASTER_PASSWORD>

说明:replicate选项指定了结果保存到哪个库和表中,如果你愿意,可以手动查询:

SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
FROM percona.checksums
WHERE (
    master_cnt <> this_cnt
    OR master_crc <> this_crc
    OR ISNULL(master_crc) <> ISNULL(this_crc))
GROUP BY db, tbl;

BTW:多数情况下,只要比较「master_crc <> this_crc」就可以了。

MySQL主从服务器数据一致性的修复

通过在主服务器上运行pt-table-sync,它会重建数据,数据通过复制从主服务器同步到从服务器,从而修复了一致性,在操作过程中,可以利用pt-table-checksum的结果。

shell> pt-table-sync \
    --execute \
    --replicate=percona.checksums \
    --charset=<CHARSET> \
    --host=<MASTER_HOST> \
    --user=<MASTER_USER> \
    --password=<MASTER_PASSWORD>

说明:因为pt-table-sync会重建数据,所以有一定的风险,最好提前备份好数据。如果仍然不放心,可以使用它提供的「print」选项,它会打印出相应的SQL,你可以审查一下到底执行了那些操作,然后通过手动执行来完成同步。

在使用Percona Toolkit的时候,细心的网友会发现:在传递参数的时候有两种方式:

  • –host=<HOST> –user=<USER> –password=<PASSWORD>
  • h=<HOST>,u=<USER>,p=<PASSWORD>

前一种是选项的形式,后一种是DSN的形式。如果使用了选项的形式,系统会在内部自动转换成DSN的形式,一般不同的信息用DSN的形式,公用的信息则用选项的形式。比如说:我要传递多个DSN主机信息,它们的主机名不同,但用户名和密码都相同,此时我会用选项的形式来传递用户名和密码,而通过DSN的形式来传递主机名。

本文例子中,我们为了方便,在运行Percona Toolkit命令的时候直接键入了密码等敏感信息,这在很多时候是不安全的,比如说别人可以通过查看命令历史拿到密码。还好我们有「ask-pass」选项可以解决此类问题,实际上我们还可以更进一步,直接把密码等敏感信息保存到配置文件中,最容易想到的配置文件是「~/.my.cnf」,此外,还有几个更官方的配置文件可供选择,我们可以在源代码里看到它们的踪影:

default_files => [
    "/etc/percona-toolkit/percona-toolkit.conf",
    "/etc/percona-toolkit/$program_name.conf",
    "$home/.percona-toolkit.conf",
    "$home/.$program_name.conf",
]

俗话说:不怕贼偷,就怕贼惦记着。看待问题的态度亦是如此:不怕出问题,就怕问题潜伏在暗处窥视着你,而你却一无所知。大家没事儿的时候多查查主从一致性吧。

MySQL主从服务器数据一致性的核对与修复》上有8条评论

  1. Pingback引用通告: MySQL主从服务器数据一致性的核对与修复 at 越野e族-Tech

  2. 首先,先顶一个。
    小弟是新手,对于本篇有以下几个疑虑,望能够解答一二。
    1.当master-slave 出现此问题时候,根据老王的文章来看,老王似乎相对于skip掉error更倾向于用percona-tools进行修复,但是在修复过程中需要重建数据,这势必使得DB得停止对外服务。
    个人感觉,是否通过在定期的常规维护过程中进行次操作,期间遇到的此类问题直接skip掉,虽然这样有一定的可能在期间产生问题时体现出数据不一致的弊病,但是比起出现该类问题就做修复应该更为可取些(个人观念)
    ps:当然,主从彻底分离,避免从库的写操作是解决此类问题的根本解决途径。

    2.重建数据的过程中,若所需重建的表是一个十分庞大的表,那么这个过程会不会是一个十分漫长,且资源消耗极大的过程呢。

    • 在使用PT工具修复数据的时候,不用停止服务,同时PT在修复过程中会参考系统负载等情况做出适当调整,当然,修复本身必然是一个高成本的过程。对于跳过错误之类的操作,除非不得已,否则不要使用

  3. 我们用过这种工具,但是有时候不好用。会锁表。而且有时候是random的,checksum就不一样的。误报而已

  4. 不知道大神是什么工作,既能接触到mysql数据库方面的东西,也能接触到技术架构方面的东西,难道是架构师???

  5. Pingback引用通告: [mysql]一次主从数据不一致的问题解决过程 | 你好数据库

  6. Pingback引用通告: [mysql]一次主从数据不一致的问题解决过程 - 数据库 - 阿里欧歌

发表评论

电子邮件地址不会被公开。 必填项已用*标注