binlog的格式

分点详细说明

1. STATEMENT（SBR）

• 工作原理：记录的是实际执行的SQL语句本身。比如一条UPDATE user SET balance = 100 WHERE id = 1;，binlog里就记录这条SQL。
• 优点：
  • 日志文件小：尤其是批量更新大量数据时，一条SQL就搞定，日志量很小。
  • 可读性强：因为记录的是SQL，出问题时方便人工阅读和理解。
• 缺点（非常致命，因此不推荐在生产环境使用）：
  • 确定性bug的温床：某些SQL在主从库上执行结果可能不一致！
    • 例如1：UPDATE table SET order_id = UUID(); 这条SQL在主从库执行会生成不同的UUID。
    • 例如2：含有LIMIT但没有ORDER BY的语句，执行计划可能不同，导致更新/删除的行不一致。
    • 例如3：使用了用户自定义函数（UDF）或存储过程，结果可能依赖库上的特定状态。
  • 锁更多：为了保证从库重放时的顺序一致性，在STATEMENT格式下需要更多的行锁。

2. ROW（RBR）

• 工作原理：记录的是每一行数据是如何被修改的。它不是记录SQL，而是记录“这条记录修改前是什么样（before image），修改后是什么样（after image）”。
  • 例如，UPDATE user SET balance = 100 WHERE id = 1;，如果这条语句更改了一行，ROW格式的binlog会记录类似这样的信息：(id=1, balance=50) -> (id=1, balance=100)。
• 优点：
  • 绝对的数据一致性：这是它最大的优点。从库只需要无条件地应用这些行变化，无需重新执行SQL，从根本上避免了STATEMENT格式的不确定性问题。
  • 减少锁竞争：对从库的并发复制更友好，所需的锁更少。
  • 更安全的闪回（Flashback）：通过解析ROW格式的binlog，可以逆向生成用于数据恢复的SQL（INSERT/UPDATE/DELETE），工具更成熟（如binlog2sql）。
• 缺点：
  • 日志文件大：这是它最主要的代价。尤其是批量更新（比如UPDATE table SET status=1 WHERE id < 1000000;），STATEMENT只记录一条SQL，而ROW会记录100万条行的变更信息，日志量巨大。
  • 可读性差：你无法直接mysqlbinlog工具查看并看到原始的SQL，看到的是一堆行的编码数据。需要加-v或--verbose参数才能解码显示。

3. MIXED（混合模式）

• 工作原理：默认使用STATEMENT格式来记录，但当MySQL判断某条SQL可能引起主从不一致时（比如包含了UUID(), RAND()等非确定性函数），它会自动将这条SQL切换为ROW格式来记录。
• 意图：试图在STATEMENT的日志大小和ROW的数据安全之间取得平衡。
• 生产环境为什么不首选它：因为它本质上还是依赖MySQL的“判断”。你无法100%确定所有有风险的SQL都能被准确识别并切换。为了追求极致的可靠性，我们宁愿牺牲一些日志空间，也要选择确定性更高的ROW格式。

生产环境推荐与最佳实践

核心推荐：使用 ROW 格式。

理由： 在现代运维中，数据的一致性和安全性远比磁盘空间成本重要。磁盘是廉价的，而数据错误和主从不一致带来的业务损失和修复成本是巨大的。

启用 ROW 格式后的配套优化措施：

1. 设置 binlog_row_image = MINIMAL

   • 这是ROW格式下最重要的优化选项。默认是FULL，即记录一行的所有列（无论是否修改）。设置为MINIMAL后，只记录被修改的列以及唯一标识该行的列（通常是主键）。这能显著减少binlog的体积。

2. 使用 binlog_expire_logs_seconds 合理设置日志过期时间

   • 因为ROW格式日志量大，需要更积极地清理过期日志。这个参数控制binlog文件保留的秒数。例如设置为604800（7天）。

3. 监控磁盘空间

   • 确保存放binlog的磁盘分区有足够的空间，并设置监控告警。

4. 配置项示例
   在你的MySQL配置文件（如/etc/my.cnf）的[mysqld]段中加入：

   [mysqld]
   # 核心设置：启用ROW格式
   binlog_format = ROW
   # 重要优化：只记录必要的行数据
   binlog_row_image = MINIMAL
   # 设置binlog保留时间（例如7天）
   binlog_expire_logs_seconds = 604800
   # 为binlog文件设置清晰的前缀，便于管理
   log_bin = /var/lib/mysql/mysql-bin

总结：
对于任何要求数据强一致性的生产环境（尤其是使用了微服务、多种编程语言、复杂业务逻辑的场景），ROW 格式是唯一稳健的选择。它提供了最可靠的数据复制基础，避免了无数潜在的“幽灵”问题。请将节省日志空间的考虑，通过binlog_row_image = MINIMAL和设置合理的过期策略来解决，而不是退回到有风险的STATEMENT或MIXED格式。