MySQL Binlog 格式对比

# MySQL Binlog 格式对比

Binlog 是 MySQL 复制和数据恢复的核心。三种格式各有优劣，选错了会影响数据一致性。

三种格式

-- 查看当前格式SHOWVARIABLESLIKE'binlog_format';-- 三种值：-- STATEMENT（SBR）-- ROW（RBR）-- MIXED（MBR）

STATEMENT 格式（SBR）

-- 记录原始 SQL 语句-- Binlog 内容：INSERTINTOuser(id,name)VALUES(1,'Tom');UPDATEaccountSETbalance=balance+100WHEREid=1;

优点

- 日志量小（只存 SQL） - 节省磁盘空间 - 主从延迟相对小

缺点

-- 不确定函数在主从结果不一致INSERTINTOlog(created_at)VALUES(NOW());-- 主库 NOW() = 2024-01-01 12:00:00-- 从库延迟 5 秒执行，NOW() = 2024-01-01 12:00:05-- 主从数据不一致！-- 其他问题：-- LIMIT 不带 ORDER BY-- 存储过程/触发器-- UUID() 等不确定函数

ROW 格式（RBR）

-- 记录行变化（修改前 + 修改后）-- Binlog 内容：-- ### UPDATE `account`-- ### WHERE-- ### @1=1 (id)-- ### @2=500 (balance 修改前)-- ### SET-- ### @1=1-- ### @2=600 (balance 修改后)

优点

- 数据一致性最好（记录实际变化） - 不会受不确定函数影响 - 安全可靠

缺点

- 日志量大（大事务尤其明显） -- DELETE 100万条，ROW 格式要记录 100 万行的变化 -- STATEMENT 只记录一条 SQL - 无法从 binlog 直接看到 SQL（需要 mysqlbinlog --base64-output=DECODE-ROWS -v）

MIXED 格式（MBR）

MySQL 自动选择： - 一般情况用 STATEMENT（日志小） - 遇到不确定函数自动切换 ROW 是 STATEMENT 和 ROW 的折中方案

自动切换到 ROW 的情况

-- 包含 NOW()、UUID() 等函数-- 包含 LIMIT 没有 ORDER BY-- 使用存储过程/触发器/自定义函数-- UPDATE 中使用了不确定表达式

如何选择？

结论：新项目一律用 ROW，数据一致性最重要。

查看 Binlog 内容

# STATEMENT 格式mysqlbinlog /var/lib/mysql/mysql-bin.000001# ROW 格式（需要解码）mysqlbinlog --base64-output=DECODE-ROWS-v/var/lib/mysql/mysql-bin.000001# 按时间范围mysqlbinlog --start-datetime='2024-01-01 00:00:00'--stop-datetime='2024-01-02 00:00:00'/var/lib/mysql/mysql-bin.000001# 按数据库过滤mysqlbinlog--database=mydb /var/lib/mysql/mysql-bin.000001

Binlog 清理

-- 自动清理：保留 7 天SETGLOBALexpire_logs_days=7;-- 手动清理PURGEBINARYLOGS BEFORE'2024-01-01 00:00:00';PURGEBINARYLOGSTO'mysql-bin.000010';-- 查看当前 binlogSHOWBINARYLOGS;

小结

相关阅读：

• [MySQL 主从复制原理]
• [MySQL GTID 模式详解]
• [MySQL 数据备份与恢复]