前语

最近公司某项目上反应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-position和stop-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还有statement和mixed两种形式。

  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格局，实际上便是Statement与Row的结合。

在Mixed形式下，一般的句子修正运用statment格局保存binlog。如一些函数，statement无法完成主从仿制的操作，则选用row格局保存binlog。

总结

终究剖析下来，咱们定位到原来是因为大事务+blob字段大致binlog十分大，终究咱们选用了修正事务代码，将blob字段单独拆到一张表中解决。所以，在规划开发过程中，要尽量防止大事务，同时在数据库建模的时分特别考虑将blob字段独立成表。

欢迎重视个人大众号【JAVA旭阳】交流学习