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

本篇内容主要讲解“主从不同步报错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，所以两端的数据是完全一样的。