MySQL Binlog更新语句WHERE子句含全表列 仅用主键查询仍出现该问题

问题根因 #

你观察到的Binlog行为由MySQL的行模式Binlog镜像配置直接决定：

• 你当前数据库的binlog_format参数配置为ROW（行模式，现在生产环境的默认配置），行模式下Binlog会记录每行数据的变更前后值，而非你提交的原始SQL语句。
• 额外控制行记录细节的参数binlog_row_image默认值为FULL，该模式下会强制记录：
  • WHERE部分：整行所有列的变更前旧值，不管你实际更新时用了什么过滤条件
  • SET部分：整行所有列的变更后新值，不管你实际修改了多少列
    这就是为什么你只用到主键作为更新条件，Binlog里还是会写入所有表列的原因。

数据恢复慢的原因 #

binlog_row_image=FULL模式下会产生远大于实际需要的Binlog体积：从你贴的示例就能看出，你实际只修改了3个字段（第9、10、21列），但Binlog里记录了27列的新旧值，冗余占比超过80%。
从Binlog恢复数据时需要先解析日志、再逐行匹配重放，日志体积越大、冗余字段越多，整个过程的耗时就越长。

优化方案 #

你可以通过修改binlog_row_image参数为MINIMAL解决这个问题：

• MINIMAL模式下仅会记录必要字段：WHERE部分只记录主键/唯一键值，SET部分只记录实际被修改的列，通常可以让Binlog体积缩小50%~90%，大幅提升数据恢复、主从同步的效率。
• 配置命令（全局生效，新连接适用，无需重启数据库，MySQL 5.6及以上版本支持）：

SET GLOBAL binlog_row_image = 'MINIMAL';

• 注意事项：
  • 如果你有用到依赖全字段镜像的第三方工具（比如部分Binlog闪回工具、数据同步中间件），需要先确认工具是否支持MINIMAL格式的Binlog
  • 主从架构下如果从库也需要写Binlog，建议主从库配置保持一致

内容的提问来源于stack exchange，提问作者user1070759