Mysql主从同步校验与恢复从库丢失的数据-工具盒子

前天刚把Mysql从库的同步开关（启动I/O线程和启动SQL线程）恢复正常，但是遗留问题还需要处理一下，也就是如何校验主从库同步与恢复从库丢失的数据。

当然为了防止有人在从库写入数据，我也对从库用户的权限做了限制，开放的从库账户是没有写入权限的，开发们使用的都是这个账户。未开放的从库账户是有写入权限的，只有数据库管理员才能使用这个账户。

即使这样，还是避免不了类似于前天的突发状况。在CPU飙升后，从库的异步复制线程当掉了，待到发现时，已经有一部分数据未能及时同步到从库。问题既然出现了，就想办法解决吧。

一、从库丢失的数据如何找回？

从库丢失的数据，也就是从库同步开关异常关闭到从库开关正常的那一段时间，数据未能正常从主库同步到从库的这部分数据。网上的数据同步方案，无非就是两种：全量同步和增量同步。

1、全量同步，简单粗暴

先把主库锁表（只读不能操作）、备份，接着把从库同步停了并删除，然后把主库数据复制到从库，最后再恢复主库、从库的状态。全量同步的缺点也显而易见，数据量大的时候数据导入耗时会比较长；主库、从库的状态都停了，耽误使用。

2、增量同步，操作起来比较麻烦

增量同步，先验证从库缺失的数据，然后把差异数据同步到从库中。增量同步的优点显而易见，只同步从库没有的数据、不一样的数据，比较节约时间，还可以一个表一个表的同步，很适合已经上线的项目。

增量同步，可以采用MySQL自带的checksum、mysqldiff、pt-table-checksum等工具。增量同步本次操作需要用到的工具简单了解下。

2.1、pt–table-checksum

pt–table-checksum，用来检测主从数据库中数据的一致性。pt–table-checksum在主库上运行，对同步的表进行checksum，记录下来。然后对比主从中各个表的checksum是否一致，从而判断数据是否一致。

2.2、pt-table-sync

pt-table-sync，用来修复多个实例之间数据的不一致，它可以让主从的数据修复到最终一致，也可以通过应用双写或多写的多个不相关的数据库实例修复到一致。同时它还内部集成了pt-table-checksum的校验功能，可以一边校验一边修复，也可以基于pt-table-checksum的计算结果来进行修复。

pt–table-checksum、pt-table-sync都是来自percona-toolkit的系列工具。

二、安装增量同步需要的工具

1、安装 percona-toolkit 所需的依赖包

yum install perl perl-devel perl-Time-HiRes perl-DBI perl-DBD-MySQL

中途有询问是否继续安装：y

2、下载工具包 percona-toolkit

# 下载工具包wget http://www.percona.com/get/percona-toolkit.tar.gz

3、解压 percona-toolkit.tar.gz

# 解压
tar zxvf percona-toolkit.tar.gz

4、进入percona-toolkit 目录下，执行安装。

# 进入percona-toolkit目录
cd percona-toolkit-3.3.1
# 执行安装
perl Makefile.PL
make && make install

从图中可以看到 percona-toolkit下的一系列工具都被安装了。

三、验证主从库数据是否一致

1、创建验证用户

在Master主库上，创建验证用户。验证用户，用于校验主从是否一致。验证用户的权限有SELECT,PROCESS,SUPER,REPLICATION SLAVE四个权限。

GRANT SELECT,PROCESS,SUPER,REPLICATION SLAVE ON *.* TO '验证用户名'@'%' IDENTIFIED BY '验证用户密码';

这一步也不是必须的，也可以使用主库现有的具有这些权限的用户。

2、执行数据验证，查看主从库数据是否一致。

# 验证数据
/usr/local/bin/pt-table-checksum h='127.0.0.1',u='checksums',p='',P=3306 -d rsdun-circle --nocheck-replication-filters --replicate=test.checksums --no-check-binlog-format

h：主库地址；
u：验证用户；
p：验证用户密码；
P：主库端口号；
-d：指定要检查数据库；
--nocheck-replication-filters：不马上检查赋值结果；
--replicate：将查到的数据不一致的结果保存在某个库的某个表；
--nocheck-binlog-format：不检查日志格式，默认检查。

执行后报错，据说是少了一个Digest-MD5包。

 # 安装 Digest-MD5
 yum -y install perl-Digest-MD5

再次运行执行数据验证：

执行结果还是有错误，缺少checksums表。因为在校验中--replicate=test.checksums，指定test库的checksums表，test库存在，checksums表不存在，那就在test库中创建checksums表。

表创建成功后，再次执行数据是否同步的验证，结果如下图所示：

TS：完成检查的时间；
ERRORS：检查时候发生错误和警告的数量；
DIFFS：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only仅仅显示数据不一致的表信息；
ROWS：表的行数；
CHUNKS：被划分到表中的块的数目；
SKIPPED：由于错误或警告或过大，则跳过块的数目；
TIME：执行的时间；
TABLE：被检查的表名。

3、回到Slave从库查看验证结果；

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

执行结果如果为空，则没有需要同步的数据；执行结果如果不为空，则有需要同步的数据。从上图中可以发现，还是有很多数据不同步的。

三、同步数据

1、先打印出要修复的sql语句

#先利用print命令，打印出修改的语句但不执行，最好不要直接excute执行
/usr/local/bin/pt-table-sync h=127.0.0.1,P=3306,u=root,p=''  h=127.0.0.1,P=3307,u=root,p='' --charset=utf8 --databases=dbName --no-check-slave --transaction --verbose --print

h、P、u、p：第一组为主库的数据库信息，第二组为从库的数据库信息；
--databases：要同步的数据库，多个逗号分隔；
--charset：设置数据库编码；
--no-check-slave：不检查目标服务器是否是从库服务器，默认检查；
--transaction：指锁表操作(LOCK TABLES)不会执行，取代的方式是通过事务的开始和提交来进行锁定操作；
--verbose，-v 打印更详细的操作信息；
--print：预览要进行的数据修复；
--excute：执行数据修复。

执行结果：

2、数据同步

#把主库和从库的数据差异，同步到从库
/usr/local/bin/pt-table-sync h=127.0.0.1,P=3306,u=root,p=''  h=127.0.0.1,P=3307,u=root,p='' --charset=utf8 --databases=dbName --no-check-slave --transaction --verbose --excute

从图中可以看到，有数据同步到了从库。

3、再次执行验证

/usr/local/bin/pt-table-checksum h='127.0.0.1',u='checksums',p='',P=3306 -d rsdun-circle --nocheck-replication-filters --replicate=test.checksums --no-check-binlog-format

4、最后再去从库看

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

当从从库的checksums表中查到的结果为空时，主从库的数据也就同步成功了。