MySQL主从复制Error 1197故障排查：max_binlog_cache_size配置不匹配解决方案

一、问题概述

一套 MySQL 从库在执行主从复制时出现报错，具体错误信息如下：

[ERROR][MY-816584][Repl) Slave SQL for channel '': Worker 1 failed executing transaction ‘ba29b661-5a98-11ed-blfc-08505698cce4:237961657’ at master log mysql-bin.802883, end log pos 548199875; Could not execute Write rows event on table_xx.xx; 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 mysql-bin.082883, end_log_pos 548199875, Error_code : MY-001197

错误代码 1197 明确指出，问题源于事务所需的二进制日志缓存空间超过了 max_binlog_cache_size 参数的设定值。

二、问题分析

根据 MySQL 官方文档说明，max_binlog_cache_size 参数用于控制单个事务可以使用的最大二进制日志缓存大小（以字节为单位）。

当从库应用（回放）主库的大型事务时，如果该事务对缓存的需求量超过了此参数的设定上限，服务器就会抛出上述错误。这通常意味着当前的 max_binlog_cache_size 值设置过小，无法处理业务中产生的复杂或数据量较大的事务。该参数支持动态修改，调整后无需重启 MySQL 服务，对业务影响较小。

相关参数说明：

• max_binlog_cache_size：主要针对事务性语句（如 INSERT、UPDATE、DELETE）的二进制日志缓存大小。
• max_binlog_stmt_cache_size：针对非事务性语句（如 SELECT）的缓存大小。若遇到非事务语句的类似缓存错误，也需要同步检查并调整此参数。

最佳实践建议：参数值的设置需要权衡。设置过小会导致大型事务执行失败；设置过大则可能造成不必要的内存浪费。建议通过监控 Binlog_cache_disk_use（使用了临时文件缓存的事务数）等状态变量，观察系统运行情况，从而找到一个平衡点进行优化。

三、解决方案

解决此问题的核心思路是适当增加 max_binlog_cache_size 的值。以下是详细的操作步骤。

1. 检查当前参数值

首先，我们需要分别检查主库和从库上该参数的当前设置，以确认是否存在配置不一致的情况。

登录主库查询：

mysql> SHOW VARIABLES LIKE 'max_binlog_cache_size';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| max_binlog_cache_size | 1073741824           |
+-----------------------+----------------------+
1 row in set (0.02 sec)

（示例显示主库值约为 1GB）

登录从库查询：

mysql> SHOW VARIABLES LIKE 'max_binlog_cache_size';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| max_binlog_cache_size | 536870912            |
+-----------------------+----------------------+
1 row in set (0.02 sec)

（示例显示从库值约为 512MB，明显小于主库）

通过对比发现，从库的 max_binlog_cache_size 值小于主库。当主库执行一个需要约 1GB 缓存的事务时，从库因为限制更小而无法成功回放，从而触发了错误 1197。

2. 动态调整参数值

确认问题后，可以在从库上动态调整该参数，无需重启MySQL服务。通常，为了确保主从能够处理同样规模的事务，建议将从库的值设置为与主库一致或根据从库实际负载调整。

在从库执行：

SET GLOBAL max_binlog_cache_size = 1073741824;

重要提示：

• 设置的值必须是 4096 字节的倍数。
• 为了保证 MySQL 服务重启后配置依然生效，必须同时修改配置文件 my.cnf（或 my.ini）中的 max_binlog_cache_size 参数。

3. 重启从库 SQL 线程

动态修改参数后，需要重启从库的 SQL 线程以使新的参数值生效。

STOP SLAVE SQL_THREAD;
START SLAVE SQL_THREAD;

或者使用简写：

STOP SLAVE;
START SLAVE;

4. 验证修复结果

执行以下命令检查从库复制状态，确认错误已消除：

SHOW SLAVE STATUS\G

在输出结果中，重点关注：

• Slave_SQL_Running：应为 Yes。
• Last_SQL_Errno：应为 0。
• Last_SQL_Error：应为空。

如果这些指标正常，则表明 Error 1197 问题已得到解决，主从复制恢复正常。

四、总结与建议

1. 配置一致性：在 主从复制 架构中，务必确保主库和从库的关键参数（如 max_binlog_cache_size）设置一致或从库配置不低于主库，以避免因配置不匹配引发的复制中断。特别是在处理包含大量数据修改的高并发事务时，这一点尤为重要。
2. 生效时机：SET GLOBAL 命令修改的参数值对新建立的会话立即生效，但已存在的复制SQL线程可能仍在使用旧值。因此，修改参数后重启SQL线程是必不可少的步骤。
3. 长期优化：如果该错误反复出现，建议深入分析业务中产生的大型事务模式，从SQL语句优化或业务逻辑拆分方面进行根本治理，而不是无限制地调高缓存参数。

参考文档：

• MySQL 8.0 Official Documentation: https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_max_binlog_cache_size

希望本篇针对 MySQL 主从复制错误 1197 的案例分析能为您带来帮助。在实际运维中遇到棘手的数据库问题，欢迎到 云栈社区 与广大开发者一起交流探讨。