一、Binlog 的基本概念与记录内容
Binlog 是 MySQL 的二进制日志，以事件形式记录所有对数据库的修改操作，包括：
- DDL 操作：如
CREATE、ALTER、DROP等表结构变更。 - DML 操作：如
INSERT、UPDATE、DELETE等数据修改。 - 事务信息：事务的开始、提交、回滚状态及执行时间 。
不记录的内容：纯查询语句（如 SELECT、SHOW），因其不改变数据库状态 。
二、Binlog 的三种格式及其样式
1. STATEMENT 格式
-
记录方式：保存原始的 SQL 语句。
-
示例：
UPDATE users SET name='Alice' WHERE id=1; -
特点：
- 优点：日志量小，性能高，适合批量操作 。
- 缺点：依赖上下文（如时间函数
NOW()、UUID()），可能导致主从复制不一致 。 - 适用场景：简单 SQL 操作且主从环境一致的情况 。
2. ROW 格式
-
记录方式：保存每行数据的具体变更（前镜像和后镜像）。
-
示例：
### 更新操作的前后数据 UPDATE `test`.`users` SET@1=1 (id),@2='Bob' (原name),@3=25 (原age) WHERE@1=1 (id),@2='Alice' (新name),@3=25 (新age); -
特点：
- 优点：精确记录行变更，确保主从数据严格一致 。
- 缺点：日志量大（如全表更新时），可能影响磁盘 I/O 。
- 适用场景：需要强一致性的场景（如金融系统）或涉及非确定性函数的操作 。
3. MIXED 格式
-
记录方式：混合模式，根据 SQL 类型自动选择 STATEMENT 或 ROW。
- 默认使用 STATEMENT，但遇到非确定性函数（如
RAND()、NOW()）时切换为 ROW 。
- 默认使用 STATEMENT，但遇到非确定性函数（如
-
示例：
-- 使用 NOW() 函数时，自动转为 ROW 格式 INSERT INTO logs (message, created_at) VALUES ('test', NOW()); -
特点：
- 优点：平衡性能与一致性，减少手动调整需求 。
- 缺点：复杂场景下可能无法完全规避复制问题 。
- 适用场景：通用场景，尤其适合动态 SQL 操作 。
三、Binlog 记录的具体示例
1. STATEMENT 格式的 Binlog 内容
# 时间戳和事件位置
# at 1234
#240312 10:00:00 server id 1 end_log_pos 1300
SET TIMESTAMP=1710223200;
UPDATE users SET balance=balance-100 WHERE id=1;
2. ROW 格式的 Binlog 内容
# 行变更详情（Base64 编码）
### UPDATE `test`.`users`
### WHERE
### @1=1 (id)
### @2=500 (原balance)
### SET
### @2=400 (新balance)
3. MIXED 格式的 Binlog 内容
# 自动选择模式示例
# Statement 模式记录表结构变更
ALTER TABLE users ADD COLUMN email VARCHAR(255);# Row 模式记录含非确定性函数的操作
INSERT INTO orders (user_id, amount, created_at) VALUES (1, 100, NOW());
四、如何查看 Binlog 内容
1. 通过 MySQL 命令行工具
-
查看日志列表：
SHOW BINARY LOGS; -
查看指定日志事件：
SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 1234 LIMIT 10;
mysqlbinlog__109">2. 使用 mysqlbinlog 工具
-
解析日志文件：
mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000001- 参数说明：
-
--base64-output=decode-rows：解码 ROW 格式的日志。 -
-vv：显示详细变更信息 。 -
按时间过滤：
mysqlbinlog --start-datetime="2025-03-12 10:00:00" mysql-bin.000001
五、格式选择建议
| 场景 | 推荐格式 | 理由 |
|---|---|---|
| 主从复制（强一致性需求） | ROW | 避免函数或存储过程导致的主从不一致 |
| 批量数据操作 | STATEMENT | 减少日志量，提升性能 |
| 通用业务 | MIXED | 自动平衡性能与一致性，减少人工干预 |
六、关键总结
- Binlog 样式核心差异：
- STATEMENT：记录 SQL 语句，简洁但依赖上下文。
- ROW：记录行变更，精确但日志量大。
- MIXED：动态选择，兼顾性能与一致性 。
- 查看工具：优先使用
mysqlbinlog解码 ROW 格式，结合过滤参数提高可读性 。 - 实践建议：根据业务需求选择格式，ROW 适合强一致性场景，STATEMENT 适合批量操作，MIXED 作为折中方案 。
