51工具盒子

依楼听风雨
笑看云卷云舒,淡观潮起潮落

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表中查到的结果为空时,主从库的数据也就同步成功了。

最 后 总 结

Mysql主从同步校验 与 恢复从库丢失的数据用到了两个工具:pt–table-checksum 、pt-table-sync ,它们都是percona-toolkit 系列下的工具。pt-table-sync中涉及到很多参数,还需要在使用时注意,这里也仅列出了使用的参数。

在使用前,需要先安装percona-toolkit 工具以及相关的插件,还需要建立一个表用于存储主从库数据不一致的结果。然后才能执行主从库数据是否一致的验证,验证做好了便可开始预览需要同步的数据,最后在同步增量数据。

本文运行的数据库环境是:一个主库,一个从库,主从库都在一台服务器上。主库负责读写,从库只负责读。所以同步出现问题时,只会出现主库数据没有被从库同步的情况。不会出现主库没有,从库却有的数据。参考本文时,请先对数据库做备份。

官网参考:https://docs.percona.com/percona-toolkit/pt-table-sync.html
参考链接:https://blog.51cto.com/suifu/1836551
参数参考:https://www.cnblogs.com/dbabd/p/10653408.html
全量同步参考:https://codedefault.com/p/mysql-distributed



作者:程就人生
链接:https://www.jianshu.com/p/171cfa2b58ee
来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。







赞(9)
未经允许不得转载:工具盒子 » Mysql主从同步校验 与 恢复从库丢失的数据