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,所以两端的数据是完全一样的。
体外话:mysql5.7之前,虽然从mysql5.6开始支持多线程复制,但是是对于每一个库一个复制线程,这样的话,如果某个实例只有一个库或者有多个库但是业务居中在某一个库上,那么实际上多线程可能会更慢,因为多线程调度器需要分配哪个线程给这个库,当然如果这个实例有多个库,并且业务分布在多个库上,这样开启多线程是有效果的。mysql5.7增强了这个多线程复制功能,基于logical_clock的复制方式,真正做到了表级别的多线程,真正实现了并行复制。
本站部分文章源于互联网,本着传播知识、有益学习和研究的目的进行的转载,为网友免费提供。如有著作权人或出版方提出异议,本站将立即删除。如果您对文章转载有任何疑问请告之我们,以便我们及时纠正。

PS:推荐一个微信公众号: askHarries 或者qq群:474807195,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化这些成为架构师必备的知识体系。还能领取免费的学习资源,目前受益良多

转载请注明原文出处:Harries Blog™ » mysql主从不同步报错Last_Errno 1197

赞 (0)
分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址