mysql delete数据时报错Multi-statement transaction required more than ‘max_binlog_cache

本文链接：https://blog.youkuaiyun.com/eagle89/article/details/134944722

问题描述：mysql delete数据时报错Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage，如下所示：
数据库：5.7.21
1、异常重现
mysql> delete from history_record where hist_date >= '2023-02-25' and hist_date < '2023-03-08';
ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
2、异常分析
引发此问题是因为主库执行大事务，且从库开启了并行复制，因此需要更大的max_binlog_cache_size来处理innodb事务.
--主节点参数
mysql> show variables like '%max_binlog_cache%';
+-----------------------+----------------------+
| Variable_name | Value |
+-----------------------+----------------------+
| max_binlog_cache_size | 2147483648 |
+-----------------------+----------------------+
1 row in set (0.00 sec)
说明：执行过程中有注意到存在也仅有一个大于2g的binlog日志文件产生，判断跟此有关系.
--从节点参数
mysql> show variables like '%parallel%';
+------------------------+---------------+
| Variable_name | Value |
+------------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
| slave_parallel_workers | 4 |
+------------------------+---------------+
2 rows in set (0.01 sec)

mysql> select count(*) from history_record where hist_date >= '2023-03-01' and hist_date < '2023-03-08';
+----------+
| count(*) |
+----------+
| 1846832 |
+----------+
1 row in set (0.53 sec)

mysql> delete from history_record where hist_date >= '2023-02-25' and hist_date < '2023-03-01';
Query OK, 1277750 rows affected (25.24 sec)

mysql> delete from history_record where hist_date >= '2023-03-01' and hist_date < '2023-03-08';
Query OK, 1846832 rows affected (54.66 sec)

说明：如上若是，成功删除2023-02-25至2023-03-08的数据.
提示：
该异常还可调整max_binlog_cache_size参数，将其调整到4g，语句如下：
mysql> set global max_binlog_cache_size=4*1024*1024*1024;
注意：
a、主节点与从节点均需进行调整；
b、动态修改参数后，配置文件也需要修改，以免重启后参数被还原；
c、max_binlog_cache_size与binlog_cache_size和binlog_cache_size参数有关，需根据实际情况调整.

参数说明：
binlog_cache_disk_use表示binlog_cache_size设置的内存不足导致缓存二进制日志用到临时文件的次数；
binlog_cache_use表示使用binlog_cache_size缓存的次数；
当binlog_cache_disk_use值比较大时，可以考虑适当调高binlog_cache_size对应的值；
binlog_cache_disk_use若等于0，表示不需要使用临时文件，内存cache足够。若没有long_transaction，64M是偏大的;
max_binlog_cache_size表示binlog能够使用的最大cache内存大小；
当执行多语句事务时，所有session使用的内存超过max_binlog_cache_size值时，就会报错“Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage”；
注意：
a、当该值设置太大，会比较消耗内存资源；设置太小又会使用导临时文件，即disk.
b、主从设置不一致会导致主节点写binlog成功，从节点恢复relog时同样记录binlog，但主备cache设置不同，所以同样会导致以上问题.