一条更新操作引起的MySQL主从复制异常

康壮 2017-04-10 11:04:04

作者介绍

康壮大连楼兰科技股份数据库运维组负责人,主要负责集团内部MySQL、MongoDB、Redis数据库运维管理工作。曾在第三方数据库运维公司任职技术部经理,负责政府行业的Oracle数据库运维工作,具有Oracle OCP、OCM、MySQL OCP等相关认证、对数据库架构设计、故障处理、优化有深入的理解。

 

一、环境描述
 

 

生产环境异地机房主从数据库,数据量过百G,数据库版本社区版本5.6.25。

 

二、问题描述
 

 

同事根据开发提供的SQL在Master节点执行了一个大表的的全表更新操作,导致从节点Slave IO线程中断。

 

三、问题分析
 

 

1)相关参数

 

my.cnf中有两个参数设置:

expire_logs_days = 7        #binlog保留时间7天

max_binlog_size = 1G      #binlog大小

 

2)表大小,执行SQL

 

Table: v_clda   5.8G

Sql: update v_clda set uploadtime =now(); 主库执行成功

 

3)主库,大事物产生的binlog

 


 

4)异地从库报错

 



Slave 已经无法同步数据。

 

一个事物只能写入一个binlog日志中,默认情况下,binlog日志达到设定值后(max_binlog_size),会自动生成一个新的日志文件,也会根据过期参数(expire_logs_days)设置自动删除binlog日志。如果生成了一个超大的binlog日志,很可能是由于大事物引起的。

 

四、问题处理
 

 

尝试从启slave线程,多次尝试后失败。

 

尝试跳过事物,具体方法如下:

 

从节点执行(基于GTID)

 


 

从节点执行更新操作,同步数据

 


 

五、解决方案
 

 

在执行大事物前关闭 set session sql_log_bin=0; (默认是开启的),尤其是异地机房,网络带宽有限,而且VPN通道不是十分稳定的情况下。不允许它生成大量binlog日志。

 

如果像本例中,已经执行了,而且生成了大量的binlog,最终导致复制异常,可以考虑使用跳过事物的方法来解决这个问题。

 

最笨的方法就是重新搭建主从,由于数据量比较大,还是异地不可取。

 

根本解决方法还是要拆分大事物,进行批量提交操作。贺春旸老师的MySQL管理之道一书中第四章4.4节有具体的解决方法。

 

参考改为用存储过程,每删除10000条事务就提交一次,循环操作直至删除完毕。经过优化,行锁的范围变小了,性能也就变好了。相关代码如下:

 


 

存储过程上线后,观察一段时间,同步复制时,Slave复制正常,问题解决。

最新评论
访客 2017年06月23日

为什么小表驱动大表比较快呢?

访客 2017年06月20日

学习

访客 2017年06月16日

水平太差,完全没看懂

访客 2017年06月14日

可以可视化吗?信息是挺全的

访客 2017年06月13日

学习了

活动预告