mysql主从不同步报错Last_Errno 1197的解决方法

发布时间:2021-09-27 17:43 来源:亿速云 阅读:0 作者:chen 栏目: Mysql 欢迎投稿:712375056

本篇内容主要讲解“主从不同步报错Last_Errno 1197的解决方法”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“mysql主从不同步报错Last_Errno 1197的解决方法”吧!

今天mysql从库收到一份报错,从库死了,不能同步数据了,报错如下红色部分:

Last_Errno: 1197

Last_Error: Could not execute Write_rows event on table mbpay.ATTACHMENT_copy; Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again, Error_code: 1197; Writing one row to the row-based binary log failed, Error_code: 1534; handler error HA_ERR_RBR_LOGGING_FAILED; the event's master log fb-bin.001315, end_log_pos 2241781395

解决办法:根据你的机器的内存大小适当增大参数max_binlog_cache_size参数

查看现在的大小:

1)查看全局的参数大小:

mysql>  show GLOBAL  variables like 'max_binlog_cache_size';

+-----------------------+----------------------+

| Variable_name         | Value                |

+-----------------------+----------------------+

| max_binlog_cache_size | 18446744073709547520 |

+-----------------------+----------------------+

1 row in set (0.00 sec)

2)查看当前会话的参数的大小:

mysql>  show session  variables like 'max_binlog_cache_size';

+-----------------------+----------------------+

| Variable_name         | Value                |

+-----------------------+----------------------+

| max_binlog_cache_size | 18446744073709547520 |

+-----------------------+----------------------+

1 row in set (0.00 sec)

如果只是当前会话的小,只要

mysql> set  session max_binlog_cache_size=18446744073709547520;

Query OK, 0 rows affected (0.00 sec)

否则需要

mysql> set  global binlog_cache_size=18446744073709547520;

Query OK, 0 rows affected (0.00 sec)

下面具体分析问题出现的原因:

1)首先学习下mysql 写binlog的机制:

我们知道mysql的InnoDB存储引擎是支持事务的,实现事务需要依赖于日志技术,为了性能,日志编码采用二进制格式,记录二进制日志的时候,数据库首先把binlog写进binlog_cache中,然后再从cache中刷新到底层磁盘(也就是binlog 日志文件),由于cache中的数据没有持久化,于是面临安全的问题——因为系统宕机时,Cache中可能有残余的数据没来得及写入磁盘。因此Cache要权衡,要恰到好处:既减少磁盘I/O,满足性能要求;又保证Cache无残留,及时持久化,满足安全要求,也就是说binlog_cache的大小一定要控制好,太大可能会导致异常断电时,丢失过多binlog;当然太小的话可能会导致使用临时文件来填补cache的不足,导致io性能问题,binlog_cache_size和max_binlog_cache_size参数就是控制binlog_cache大小的;

2)binlog_cache_size和max_binlog_cache_size参数:

参数:binlog_cache_size :一个事务,在没有提交(uncommitted)的时候,产生的日志,记录到Cache中;等到事务提交(committed)需要提交的时候,则把日志持久化到磁盘。binlog_cache_size就是为每个session 分配的内存的大小,在事务过程中用来存储二进制日志的缓存;

binlog_cache_size设置太大的话,会比较消耗内存资源(Cache本质就是内存);                 binlog_cache_size 设置太小的话,如果用户提交一个“长事务(long_transaction)”,比如:批量导入数据。那么该事务必然会产生很多binlog,这样cache可能不够用(默认binlog_cache_size是32K),不够用的时候mysql会把uncommitted的部分写入临时文件(临时文件cache的效率必然没有内存cache高),等到committed的时候才会写入正式的持久化日志文件。

参数:max_binlog_cache_size :表示的是所有会话加在一起的binlog 能够使用的最大cache 内存大小,当我们执行多语句事务的时候 ,所有session的binlog使用的内存超max_binlog_cache_size的值时就会报错:“Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage”

那么既然cache不够的时候,会使用临时文件充当cache,怎么还会报错more than 'max_binlog_cache_size' 呢?原来使用临时文件充当cache是针对某个会话的,当这个会话使用binlog_cache的大小超过binlog_cache_size的值的时候,就会使用临时文件,当所有session的binlog使用的内存超max_binlog_cache_size的值时就会报错,所以超过max_binlog_cache_size的值的原因:1,max_binlog_cache_size这个值设置过小,2,当前会话数据量暴增;

3)如何判断当前binlog_cache_size设置的是否合理;

binlog_cache_size 设置的大小可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试;因为:

binlog_cache_use:使用二进制日志缓存(也就是binlog_cache)的事务数量;

binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来充当binlog cache保存的事务数量。

查看前面状态变量的大小:

mysql> show status like 'binlog_%';

+-----------------------+-----------+

| Variable_name         | Value     |

+-----------------------+-----------+

| Binlog_cache_disk_use | 0         |

| Binlog_cache_use      | 120402264 |

+-----------------------+-----------+

2 rows in set (0.00 sec)

运行情况Binlog_cache_use 表示binlog_cache内存方式被用上了多少次,Binlog_cache_disk_use表示binlog_cache临时文件方式被用上了多少次。Binlog_cache_disk_use现在等于0,表示内存cache是够用的,从来不需要使用到临时文件,如果Binlog_cache_disk_use不等于零,则说明当前会话的Binlog_cache_use设置的不够,需要增大。

4)底层binlog文件切换的条件:

我们知道binlog file 使用索引来循环文件,在以下条件将循环至下一个索引

1.mysql服务重启的时候

2.日志达到了最大日志长度max_binlog_size设置的值时;

3.日志被刷新: mysql> flush logs;

如下是我的binlog的目录,正在使用的是 mysql-bin.000182(也就是编号最大的),mysql-bin.index是用来控制binlog循环的文件;

[root@server02 mysql]# ll

-rw-rw----  1 mysql mysql       9556 7月  23 20:48 mysql-bin.000181

-rw-rw----  1 mysql mysql        120 7月  23 20:48 mysql-bin.000182

-rw-rw----  1 mysql mysql         64 7月  23 20:48 mysql-bin.index

5)重点说说主从同步的过程

mysql主从同步的过程的第一部分就是master记录二进制日志,在每个事务更新数据完成之前,master在二进制日志记录这些改变,MySQL将事务串行的写入二进制日志,即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后,master通知存储引擎提交事务,salve会在一定时间间隔内对master二进制日志进行探测其是否发生改变,如果发生改变,则开始一个I/OThread请求master二进制事件,同时主节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,之后slave的io线程去接收主库发送过来的binlog,然后写进本地binlog cahce中,(值得注意的是master的Binlog Dump进程读取master库的binlog cache中的binlog)然后刷新到底层磁盘的中继日志(reley log)文件中,最后slave的sql进程应用reley log重演变化,实现同步。

那么为什么主库没有报错,但是从库会报错呢?

按道理讲mysql5.6主库可以并行写,但是从库是串行复制(虽然支持多线程,但是是一个库一个线程)的,不可能由会话太多导致报错,只能一个原因就是从库的max_binlog_cache_size设置比主库的小,验证发现果然如此,这个报错是因为有一个大事务binlog写到从库的binlog cache的时候,由于超过了从库的max_binlog_cache_size,导致报错;

主从复制的过程(摘自网络):

  1. 当在从库slave执行change的操作之后,Slave 上面的IO线程连接上 Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

   2. Master 接收到来自 Slave 的 IO 线程的请求后,通过负责复制的Binlog Dump线程根据请求信息读取指定日志指定位置之后的日志信息,返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置;

  3. Slave 的 IO 线程接收到信息后,将接收到的日志内容依次写入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端,并将读取到的Master端的bin-log的文件名和位置记录到master- info文件中,以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发给我”,

   4. Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后,会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句,并在自身执行这些 Query。这样,实际上就是在 Master 端和 Slave 端执行了同样的 Query,所以两端的数据是完全一样的。

免责声明:本站发布的内容(图片、视频和文字)以原创、来自本网站内容采集于网络互联网转载等其它媒体和分享为主,内容观点不代表本网站立场,如侵犯了原作者的版权,请告知一经查实,将立刻删除涉嫌侵权内容,联系我们QQ:712375056,同时欢迎投稿传递力量。