如何恢复MySQL主从数据一致性

作者:iVictor 时间:2024-01-26 23:34:33 

最近被告知,MySQL主从数据库的数据不一致,猜测备库在同步过程中出现了问题,于是,登上备库,使用 mysql> show slave status\G查看,果然,备库在insert语句中因违反主键约束,导致备库停止了同步。现在的问题很明确,就是如何恢复主从库数据的一致性。

可选方案如下:

一、查看Master最新的Position,将其作为Slave复制的起点。

这种思路体现的是过去的不一致既往不咎,现在保持同步即可。看起来,这个思路和恢复主从库数据的一致性的初衷有所违背,但这种方法,简单,高效,在测试环境,对历史数据要求不高的场景中可使用。

二、必须严格的恢复主从库数据的一致性。

在这里,也有两种思路:

1. 备份主库数据,并在从库上恢复,在历史数据一致性的基础上开启同步,但这种方法比较麻烦,必须在主库上执行锁表操作,阻止客户端对于表数据的更新操作,而且在数据量大的情况下,备份也是个耗时的工程。其实,这种方法在实际生产环境中也很少用。

2. Skip掉相关错误

其实,这个说活不是很严谨,准备的说,是跳过相关的事务。在我今天这种情况下,就是skip掉因违反主键约束而失败的insert语句。

如何跳过相关事务

一、停止slave服务

二、SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

三、开启slave服务。

这里跳过的是一个事务。当然,也可以跳过多个事务,但要谨慎,毕竟,你并不知道跳过的是什么事务。

建议:可反复执行上述步骤,仔细查看导致从库不能同步的语句。有的时候,阻止从库的事务太多,这种方法就显得略为低效。

可分析主库日志的事务,来确定SQL_SLAVE_SKIP_COUNTER的合适值。具体步骤如下:

1、在备库中执行show slave status\G,确认以下两个参数

如何恢复MySQL主从数据一致性

根据上述两个参数的值,在主库中查看当前阻碍从库复制的事务以及之后的事务。

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000217' from 673146776;

这个是查看日志文件mysql-bin.000217中事务ID为673146776后的所有事务。

当然,SHOW BINLOG EVENTS的用法还是相当灵活的,下述方式均可。

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000217' from 673146776\G

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000217' from 673146776 limit 10;

也可在主机环境下通过mysqlbinlog命令查看

# mysqlbinlog mysql-bin.000217 --start-position=673146776

如何查询语句的执行情况

在从库跳过相关事务,重新启动Slave后,Slave_IO_Running,Slave_SQL_Running两项均显示“YES”,但Seconds_Behind_Master并没有马上下降,反而缓慢上升。

这时候,通过show processlist语句查看线程的执行情况,发现第一条语句执行时间太长,“State”列显示“Sending data”。关于“Sending data”的含义,官方说明如下:

如何恢复MySQL主从数据一致性

可见,该语句涉及了大量的磁盘读。

为了进一步分析该语句的耗时分布,可设置profiling变量。步骤如下:

一、在查询开始之前,设置set profiling=on;

二、在语句执行完毕后,通过show profiles查看语句的Query_ID。

三、通过show profile for queryQuery_ID 查看语句的具体执行情况。

最后也发现,该语句在Sending data阶段耗时过久。

总结:

1. 在执行stop slave的时候,stop slave命令被hang住了,在网上查询了相关资料,可能与Slave中有长SQL或Locked的SQL执行有关,在这里,除show processlist外,最好不要执行show slave status以及slave stop等slave相关命令。那么如何解决该问题呢?等待锁定SlaveSQL的线程结束,或者重启数据库。我选择了后者。

2. 在重启备库的过程中,还有段小插曲,在执行start slave命令的时候,报如下错误:ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository。网上很多资料都是推荐重新配置主从集群,这样又回到了开头的方案选择部分了。奇怪的时,我关闭了从库,重新启动,又好了。而两次启动命令唯一的差别就是前一次启动使用的是mysqld,后一次启动使用的是mysqld_safe,而且多带了一个--user参数。

标签:MySQL,主从数据,一致性,恢复
0
投稿

猜你喜欢

  • 关于SQL Server数据库中转储设备分析

    2009-01-21 14:55:00
  • Design IT. (8),一匹“更快的马”

    2009-02-11 12:19:00
  • pytorch+sklearn实现数据加载的流程

    2022-05-15 14:44:27
  • Python实现滑块拼图验证码详解

    2022-04-28 23:22:26
  • Pycharm2020.1安装无法启动问题即设置中文插件的方法

    2021-02-03 19:28:29
  • Python的SQLalchemy模块连接与操作MySQL的基础示例

    2024-01-20 07:37:13
  • sql 2005不允许进行远程连接可能会导致此失败的解决方法

    2024-01-25 17:34:59
  • python celery分布式任务队列的使用详解

    2021-04-22 18:17:56
  • Python linecache.getline()读取文件中特定一行的脚本

    2023-03-09 13:18:05
  • python神经网络slim常用函数训练保存模型

    2023-07-14 14:22:23
  • [Oracle] CPU/PSU补丁安装详细教程

    2024-01-27 22:17:52
  • Oracle临时表空间删除和重建实现过程

    2023-07-18 09:26:04
  • 返回SQL执行时间的存储过程

    2024-01-16 15:22:06
  • vue项目无法删除的问题及解决

    2024-05-02 17:08:36
  • 基于PyQt5制作一个windows通知管理器

    2022-10-18 19:28:24
  • python3.5绘制随机漫步图

    2022-08-12 14:16:13
  • 一篇文章彻底弄懂Python字符编码

    2021-06-06 18:51:33
  • tensorflow中next_batch的具体使用

    2023-04-21 05:34:02
  • 简单了解Python3里的一些新特性

    2022-09-22 21:32:23
  • SQL Server主键与外键设置以及相关理解

    2024-01-21 10:45:05
  • asp之家 网络编程 m.aspxhome.com