binlog、redo log、undo log 内容案例

一、Binlog

MySQL 的二进制日志(Binlog)是记录数据库变更操作的核心机制，主要用于主从复制和数据恢复。Binlog 有三种记录格式：STATEMENT、ROW 和 MIXED。它们的核心区别在于 记录数据变更的方式，以下是详细说明和示例：

1.1、Binlog 的三种格式

1.2、Binlog 格式详解及示例

1. STATEMENT 格式

• 记录内容：直接记录执行的 SQL 语句。

示例场景：

• UPDATE users SET balance = balance - 100 WHERE id = 1;

Binlog 记录：

# 直接记录原始 SQL
UPDATE `test`.`users` SET `balance` = `balance` - 100 WHERE `id` = 1;

风险示例：

-- 使用 NOW() 会导致主从时间不一致
INSERT INTO logs (msg, time) VALUES ('event', NOW());

主库执行时记录的是 NOW() 的时间值，但从库重放时会使用自己的当前时间，导致数据不一致。

2. ROW 格式

• 记录内容：记录每行数据变更前后的完整值(像数据快照)。

示例场景：UPDATE users SET balance = 200 WHERE id = 1;

Binlog 记录(简化表示)：

# 记录变更前后的行数据
Table: test.users
Before: {id=1, balance=100}
After:  {id=1, balance=200}

• 特点：

• 即使 SQL 中有不确定性函数(如 RAND())，也能保证主从一致。

• 批量操作时日志量爆炸(如 UPDATE users SET status=1; 更新百万行)。

3. MIXED 格式

• 记录逻辑：默认使用 STATEMENT，当 SQL 可能引发主从不一致时，自动切换为 ROW。

• 触发条件：

• 使用 UUID(), RAND(), NOW() 等非确定性函数。

• 涉及触发器或存储过程。

• 使用 LOAD_FILE() 等依赖环境的函数。

示例场景

-- 包含 RAND() 函数
UPDATE orders SET discount = RAND() * 10 WHERE id = 5;

Binlog 记录：

# 自动切换为 ROW 格式，记录实际变更值
Table: test.orders
Before: {id=5, discount=0}
After:  {id=5, discount=7.3}  -- RAND() 结果被固化

1.3、如何查看和设置 Binlog 格式？

1. 查看当前格式

SHOW VARIABLES LIKE 'binlog_format';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

2. 动态修改格式(需重启生效)

-- 修改为 ROW 格式(临时生效，重启后失效)
SET GLOBAL binlog_format = 'ROW';

-- 永久生效需修改配置文件 my.cnf
[mysqld]
binlog_format = ROW

1.4、Binlog 格式的日志文件内容对比

1. STATEMENT 格式的 Binlog 事件

# at 123
#220101 12:00:00 server id 1
SET TIMESTAMP=1641000000;
INSERT INTO users (name) VALUES ('Alice');

2. ROW 格式的 Binlog 事件

# at 456
#220101 12:00:00 server id 1
Table_map: `test`.`users` mapped to number 15
Write_rows: table id 15 flags: STMT_END_F
BINLOG '
...base64编码的行数据...
'/*!*/;

1.5、如何选择 Binlog 格式？

1.6、注意事项

1. 主从一致性：ROW 格式最安全，但需权衡存储和网络开销。
2. 数据恢复：ROW 格式的 Binlog 可用于精准的数据回滚(需工具解析行数据)。
3. 版本兼容性：MySQL 5.1+ 支持 MIXED 格式，建议使用较新版本。

二、Redo Log(重做日志)

作用

• 崩溃恢复：确保事务的持久性(Durability)，即使系统崩溃，未写入磁盘的事务修改也能通过 Redo Log 恢复。
• 物理日志：记录的是物理层面的数据页修改(如数据页的偏移量和修改后的字节)。

记录内容

每个 Redo Log 条目包含以下核心信息：

1. 事务ID：关联到具体事务。
2. 修改类型：如插入(INSERT)、更新(UPDATE)、删除(DELETE)。
3. 数据页信息：

• 表空间 ID(Tablespace ID)
• 数据页号(Page Number)
• 页内偏移量(Offset)

1. 修改后的数据：写入数据页的具体内容(二进制形式)。

示例

假设执行一条更新操作：

UPDATE users SET balance = 200 WHERE id = 1;

对应的 Redo Log 条目可能如下(简化表示)：

{
  "LSN": 123456,          -- 日志序列号(Log Sequence Number)
  "Transaction ID": 789,
  "Type": "UPDATE",
  "Tablespace": "users.ibd",
  "Page": 5,
  "Offset": 1024,
  "New Data": "id=1,balance=200"  -- 物理修改后的数据(二进制形式)
}

特点

• 以 顺序追加 方式写入文件(如 ib_logfile0、ib_logfile1)。
• 写入单位是 数据页的物理修改，与 SQL 语句无关。
• 通过 innodb_flush_log_at_trx_commit 控制刷盘策略。

三、Undo Log(回滚日志)

作用

• 事务回滚：支持事务的原子性(Atomicity)，撤销未提交事务的修改。
• MVCC 多版本控制：为读操作提供一致性视图，避免脏读。
• 逻辑日志：记录的是逻辑操作的反向操作，生成逆向回滚 sql(如 INSERT 对应 DELETE)。

记录内容

每个 Undo Log 条目包含以下核心信息：

1. 事务ID：标识生成该日志的事务。
2. 回滚指针(Roll Pointer)：指向更早版本的 Undo Log(用于构建多版本链)。
3. 修改前的数据：事务修改前的旧数据(逻辑格式)。
4. 操作类型：如 TRX_UNDO_INSERT、TRX_UNDO_UPDATE。

示例

继续以上面的 UPDATE 操作为例：

UPDATE users SET balance = 200 WHERE id = 1; -- 原 balance = 100

对应的 Undo Log 条目可能如下(简化表示)：

{
  "Transaction ID": 789,
  "Roll Pointer": NULL,      -- 若为首次修改，指向空
  "Type": "TRX_UNDO_UPDATE",
  "Old Data": "id=1,balance=100",  -- 修改前的数据
  "Undo SQL": "UPDATE users SET balance = 100 WHERE id = 1;"  -- 逻辑回滚操作
}

特点

• 存储在 回滚段(Rollback Segment) 中，与表空间关联。
• 形成多版本链，支持 MVCC 的 Read View。
• 事务提交后，Undo Log 不会立即删除(可能被其他读事务依赖)。

三种日志的写入顺序

1. 事务开始(BEGIN)。

2. 生成 Undo Log(记录修改前的数据)。

3. 修改内存中的数据页。

4. 用户手动提交事务(COMMIT)

5. 系统内部执行两阶段提交

   • 阶段一(Prepare)：

     1. 写 Redo Log(Prepare 状态)。

     2. Redo Log 根据 innodb_flush_log_at_trx_commit 参数决定是否刷盘。

   • 阶段二(Commit)：

     1. 写 Binlog(逻辑操作记录)。

     2. Binlog 根据 sync_binlog 参数决定是否刷盘。

     3. 写 Redo Log(Commit 状态)。

6. 返回提交成功

   • 客户端收到“Query OK”响应，事务正式完成。