麻了,一个操作把MySQL主从复制整崩了

这篇具有很好参考价值的文章主要介绍了麻了,一个操作把MySQL主从复制整崩了。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

前言

最近公司某项目上反馈mysql主从复制失败,被运维部门记了一次大过,影响到了项目的验收推进,那么究竟是什么原因导致的呢?而主从复制的原理又是什么呢?本文就对排查分析的过程做一个记录。

主从复制原理

我们先来简单了解下MySQL主从复制的原理。

麻了,一个操作把MySQL主从复制整崩了

  1. 主库master 服务器会将 SQL 记录通过 dump 线程写入到 二进制日志binary log 中;
  2. 从库slave 服务器开启一个 io thread 线程向服务器发送请求,向 主库master 请求 binary log。主库master 服务器在接收到请求之后,根据偏移量将新的 binary log 发送给 slave 服务器。
  3. 从库slave 服务器收到新的 binary log 之后,写入到自身的 relay log 中,这就是所谓的中继日志。
  4. 从库slave 服务器,单独开启一个 sql thread 读取 relay log 之后,写入到自身数据中,从而保证主从的数据一致。

以上是MySQL主从复制的简要原理,更多细节不展开讨论了,根据运维反馈,主从复制失败主要在IO线程获取二进制日志bin log超时,一看主数据库的binlog日志竟达到了4个G,正常情况下根据配置应该是不超过300M。

麻了,一个操作把MySQL主从复制整崩了

binlog写入机制

想要了解binlog为什么达到4个G,我们来看下binlog的写入机制。

binlog的写入时机也非常简单,事务执行过程中,先把日志写到 binlog cache ,事务提交的时候,再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开,无论这个事务多大,也要确保一次性写入,所以系统会给每个线程分配一个块内存作为binlog cache

麻了,一个操作把MySQL主从复制整崩了

  1. 上图的write,是指把日志写入到文件系统的page cache,并没有把数据持久化到磁盘,所以速度比较快
  2. 上图的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

  1. 查看binlog日志
./mysqlbinlog --no-defaults --base64-output=decode-rows -vv /mysqldata/mysql/binlog/mysql-bin.004816|more
  1. 以事务为单位统计binlog日志文件中占用的字节大小
./mysqlbinlog --no-defaults --base64-output=decode-rows -vv /mysqldata/mysql/binlog/mysql-bin.004816|grep GTID -B1|grep '^# at' | awk '{print $3}' | awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp, tmp, $1); tmp=$1}'|sort -n -r|more

麻了,一个操作把MySQL主从复制整崩了

生产中某个事务竟然占用4个G。

  1. 通过start-positionstop-position统计这个事务各个SQL占用字节大小
./mysqlbinlog --no-defaults --base64-output=decode-rows --start-position='xxxx' --stop-position='xxxxx' -vv /mysqldata/mysql/binlog/mysql-bin.004816 |grep '^# at'| awk '{print $3}' | awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp, tmp, $1); tmp=$1}'|sort -n -r|more

麻了,一个操作把MySQL主从复制整崩了

发现最大的一个SQL竟然占用了32M的大小,那超过10M的大概有多少个呢?

  1. 通过超过10M大小的数量
./mysqlbinlog --no-defaults --base64-output=decode-rows --start-position='xxxx' --stop-position='xxxxx' -vv /mysqldata/mysql/binlog/mysql-bin.004816|grep '^# at' | awk '{print $3}' | awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp, tmp, $1); tmp=$1}'|awk '$1>10000000 {print $0}'|wc -l

麻了,一个操作把MySQL主从复制整崩了

统计结果显示竟然有200多个,毛估一下,也有近4个G了

  1. 根据pos, 我们看下究竟是什么SQL导致的
./mysqlbinlog --no-defaults --base64-output=decode-rows --start-position='xxxx' --stop-position='xxxxx' -vv /mysqldata/mysql/binlog/mysql-bin.004816|grep '^# atxxxx' -C5| grep -v '###' | more

麻了,一个操作把MySQL主从复制整崩了

根据sql,分析了下,这个表正好有个blob字段,统计了下blob字段总合大概有3个G大小,然后我们业务上有个导入操作,这是一个非常大的事务,会频繁更新这表中记录的更新时间,导致生成binlog非常大。

问题: 明明只是简单的修改更新时间的语句,压根没有动blob字段,为什么生产的binlog这么大?因为生产的binlog采用的是row模式。

binlog的模式

binlog日志记录存在3种模式,而生产使用的是row模式,它最大的特点,是很精确,你更新表中某行的任何一个字段,会记录下整行的内容,这也就是为什么blob字段都被记录到binlog中,导致binlog非常大。此外,binlog还有statementmixed两种模式。

  1. STATEMENT模式 ,基于SQL语句的复制
  • 优点: 不需要记录每一行数据的变化,减少binlog日志量,节约IO,提高性能。
  • 缺点: 由于只记录语句,所以,在statement level下 已经发现了有不少情况会造成MySQL的复制出现问题,主要是修改数据的时候使用了某些定的函数或者功能的时候会出现。
  1. ROW模式,基于行的复制

5.1.5版本的MySQL才开始支持,不记录每条sql语句的上下文信息,仅记录哪条数据被修改了,修改成什么样了。

  • 优点: binlog中可以不记录执行的sql语句的上下文相关的信息,仅仅只需要记录那一条被修改。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定的情况下的存储过程或function,以及trigger的调用和触发无法被正确复制的问题
  • 缺点: 所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,会产生大量的日志内容。
  1. MIXED模式

从5.1.8版本开始,MySQL提供了Mixed格式,实际上就是StatementRow的结合。

Mixed模式下,一般的语句修改使用statment格式保存binlog。如一些函数,statement无法完成主从复制的操作,则采用row格式保存binlog

总结

最终分析下来,我们定位到原来是由于大事务+blob字段大致binlog非常大,最终我们采用了修改业务代码,将blob字段单独拆到一张表中解决。所以,在设计开发过程中,要尽量避免大事务,同时在数据库建模的时候特别考虑将blob字段独立成表。

欢迎关注个人公众号【JAVA旭阳】交流学习文章来源地址https://www.toymoban.com/news/detail-438131.html

到了这里,关于麻了,一个操作把MySQL主从复制整崩了的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【MySQL】主从异步复制配置

    您好,我是码农飞哥(wei158556),感谢您阅读本文,欢迎一键三连哦 。 💪🏻 1. Python基础专栏,基础知识一网打尽,9.9元买不了吃亏,买不了上当。 Python从入门到精通 😁 2. 毕业设计专栏,毕业季咱们不慌忙,几百款毕业设计等你选。 ❤️ 3. Python爬虫专栏,系统性的学习

    2024年02月03日
    浏览(39)
  • MySQL主从分离读写复制

    在高负载的生产环境里,把数据库进行读写分离,能显著提高系统的性能。下面对MySQL的进行读写分离。 试验环境 A机:IP:192.168.0.1 mysql版本:mysql-5.6.4,主数据服务器(只写操作) B机:IP:192.168.0.2 mysql版本:mysql-5.6.4,从数据服务器(只读操作) 步骤     1.修改主数据库服务器

    2024年02月09日
    浏览(49)
  • MySQL主从复制(三十五)

    提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 目录 前言 一、MySQL Replication概述 1.1 MySQL Replication优点: 二、MySQL复制类型 2.1 异步复制(Asynchronous repication) 2.2 全同步复制(Fully synchronous replication) 2.3半同步复制(Semisynchronous replication) 三、

    2024年02月15日
    浏览(37)
  • MySQL主从复制案例实现

    使用Sharding-JDBC实现读写分离: 1、导入maven坐标 2、 在配置文件中配置读写分离规则 3、在配置文件中配置允许bean定义覆盖配置项 可以看到已经实现了读写分离了。

    2024年02月03日
    浏览(60)
  • 6.Mysql主从复制

    读写分离概念: 读写分离,基本的原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATEDELETE),而从数据库处理SELECT查询操作。 数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。 读写分离的作用: 因为数据库的“写”( 写10000条数据可能要3分钟)操作

    2024年02月11日
    浏览(44)
  • mysql的主从复制

    主从复制的原理是通过基于日志的复制方式实现数据的同步。当主服务器上发生数据变更时,会将这些变更写入二进制日志(Binary Log)中。从服务器通过连接到主服务器,请求从主服务器获取二进制日志,并将这些日志应用到自己的数据库中。 1. 主服务器生成二进制日志:

    2024年02月15日
    浏览(32)
  • MySQL 主从复制

    1、概念介绍 主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。 MySQL支持一台主库同时向多台从库进行复制,从库同时也可以作为其他从服务器的主库,实现链状

    2024年01月22日
    浏览(43)
  • 【MySQL】主从复制

    MySQL是一款关系型数据库,被广泛应用于各种互联网应用中。随着业务的不断扩大,数据量不断增加,如何让MySQL具有更好的可用性和容灾能力成为了越来越重要的问题。本文将重点介绍MySQL主从复制和高可用性的实现,旨在为MySQL的使用者提供指导和帮助。 MySQL主从复制是指

    2024年02月20日
    浏览(42)
  • MySQL主从复制详解

    随着业务流量的增长,一台MySQL数据库服务器已经满足不了需求了,会负载过重,容易出现宕机的情况,导致数据的丢失。这个时候就需要实现数据库的负载均衡和读写分离,来减少单台MySQL数据库服务器的压力。我们可以通过使用MySQL内置的复制功能来搭建MySQL一主一从或一

    2024年02月10日
    浏览(39)
  • mysql主从复制及原理

    主要基于MySQL二进制日志 主要包括三个线程(2个I/O线程,1个SQL线程) 1、MySQL将数据变化记录到二进制日志中; 2、Slave将MySQL的二进制日志拷贝到Slave的中继日志中; 3、Slave将中继日志中的事件在做一次,将数据变化,反应到自身(Slave)的数据库 在主服务器(master)上启用

    2024年02月14日
    浏览(31)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包