麻了,一个操作把 MySQL 主从复制整崩了
前言
最近公司某项目上反馈 mysql 主从复制失败,被运维部门记了一次大过,影响到了项目的验收推进,那么究竟是什么原因导致的呢?而主从复制的原理又是什么呢?本文就对排查分析的过程做一个记录。
主从复制原理
我们先来简单了解下 MySQL 主从复制的原理。
主库
master
服务器会将 SQL 记录通过dump
线程写入到 二进制日志binary log
中;从库
slave
服务器开启一个io thread
线程向服务器发送请求,向 主库master
请求binary log
。主库master
服务器在接收到请求之后,根据偏移量将新的binary log
发送给slave
服务器。从库
slave
服务器收到新的binary log
之后,写入到自身的relay log
中,这就是所谓的中继日志。从库
slave
服务器,单独开启一个sql thread
读取relay log
之后,写入到自身数据中,从而保证主从的数据一致。
以上是 MySQL 主从复制的简要原理,更多细节不展开讨论了,根据运维反馈,主从复制失败主要在 IO 线程获取二进制日志bin log
超时,一看主数据库的binlog
日志竟达到了 4 个 G,正常情况下根据配置应该是不超过 300M。
binlog 写入机制
想要了解binlog
为什么达到 4 个 G,我们来看下 binlog 的写入机制。
binlog
的写入时机也非常简单,事务执行过程中,先把日志写到 binlog cache
,事务提交的时候,再把binlog cache
写到binlog
文件中。因为一个事务的binlog
不能被拆开,无论这个事务多大,也要确保一次性写入,所以系统会给每个线程分配一个块内存作为binlog cache
。
上图的
write
,是指把日志写入到文件系统的page cache
,并没有把数据持久化到磁盘,所以速度比较快上图的
fsync
,才是将数据持久化到磁盘的操作, 生成binlog
日志中
生产上 MySQL 中binlog
中的配置max_binlog_size
为 250M, 而max_binlog_size
是用来控制单个二进制日志大小,当前日志文件大小超过此变量时,执行切换动作。,该设置并不能严格控制 Binlog 的大小,尤其是binlog
比较靠近最大值而又遇到一个比较大事务时,为了保证事务的完整性,可能不做切换日志的动作,只能将该事务的所有 $QL 都记录进当前日志,直到事务结束。一般情况下可采取默认值。
所以说怀疑是不是遇到了大事务,因而我们需要看看 binlog 中的内容具体是哪个事务导致的。
查看 binlog 日志
我们可以使用mysqlbinlog
这个工具来查看下 binlog 中的内容,具体用法参考官网:https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html
。
查看
binlog
日志
以事务为单位统计
binlog
日志文件中占用的字节大小
生产中某个事务竟然占用 4 个 G。
通过
start-position
和stop-position
统计这个事务各个 SQL 占用字节大小
发现最大的一个 SQL 竟然占用了 32M 的大小,那超过 10M 的大概有多少个呢?
通过超过 10M 大小的数量
统计结果显示竟然有 200 多个,毛估一下,也有近 4 个 G 了
根据 pos, 我们看下究竟是什么 SQL 导致的
根据 sql,分析了下,这个表正好有个blob
字段,统计了下 blob 字段总合大概有 3 个 G 大小,然后我们业务上有个导入操作,这是一个非常大的事务,会频繁更新这表中记录的更新时间,导致生成binlog
非常大。
问题: 明明只是简单的修改更新时间的语句,压根没有动blob
字段,为什么生产的binlog
这么大?因为生产的 binlog 采用的是 row 模式。
binlog 的模式
binlog
日志记录存在 3 种模式,而生产使用的是row
模式,它最大的特点,是很精确,你更新表中某行的任何一个字段,会记录下整行的内容,这也就是为什么blob
字段都被记录到binlog
中,导致binlog
非常大。此外,binlog
还有statement
和mixed
两种模式。
STATEMENT 模式 ,基于 SQL 语句的复制
优点: 不需要记录每一行数据的变化,减少
binlog
日志量,节约 IO,提高性能。缺点: 由于只记录语句,所以,在
statement leve
l 下 已经发现了有不少情况会造成 MySQL 的复制出现问题,主要是修改数据的时候使用了某些定的函数或者功能的时候会出现。
ROW 模式,基于行的复制
5.1.5 版本的 MySQL 才开始支持,不记录每条 sql 语句的上下文信息,仅记录哪条数据被修改了,修改成什么样了。
优点:
binlog
中可以不记录执行的 sql 语句的上下文相关的信息,仅仅只需要记录那一条被修改。所以rowlevel
的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定的情况下的存储过程或function
,以及trigger
的调用和触发无法被正确复制的问题缺点: 所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,会产生大量的日志内容。
MIXED 模式
从 5.1.8 版本开始,MySQL 提供了Mixed
格式,实际上就是Statement
与Row
的结合。
在Mixed
模式下,一般的语句修改使用statment
格式保存binlog
。如一些函数,statement
无法完成主从复制的操作,则采用 row格式
保存binlog
。
总结
最终分析下来,我们定位到原来是由于大事务+blob 字段大致 binlog 非常大,最终我们采用了修改业务代码,将 blob 字段单独拆到一张表中解决。所以,在设计开发过程中,要尽量避免大事务,同时在数据库建模的时候特别考虑将 blob 字段独立成表。
欢迎关注个人公众号【JAVA 旭阳】交流学习
版权声明: 本文为 InfoQ 作者【JAVA旭阳】的原创文章。
原文链接:【http://xie.infoq.cn/article/7d82b41a3e3706be3e3039bdf】。文章转载请联系作者。
评论