MySQL Binlog的样式

原创于 2025-03-12 13:02:41 发布

· 679 阅读

12 ·

版权

文章标签：

#mysql #oracle #数据库

MySQL 专栏收录该内容

7 篇文章

订阅专栏

一、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 。

示例：

-- 使用 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;

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 作为折中方案。