跳至主要內容

MySQL三大日志(binlog、redo log和undo log)详解

JavaJuice数据库MySQL约 5468 字大约 18 分钟

MySQL 日志

MySQL 日志概述

MySQL 提供了多种类型的日志来帮助数据库管理员和开发人员进行故障排查、性能优化和数据恢复。主要的 MySQL 日志类型包括：

1. 错误日志（Error Log）

  • 记录 MySQL 服务器的启动、关闭和运行过程中出现的错误、警告等信息。
  • 关键场景：数据库异常、崩溃分析、权限问题排查等。
  • 配置参数：
    [mysqld]
log_error = /var/log/mysql/error.log

2. 查询日志（General Query Log）

  • 记录所有的 SQL 语句，包括 SELECT、INSERT、UPDATE、DELETE 等。
  • 关键场景：调试 SQL 语句、审计用户操作。
  • 开启方式：
    [mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log
  • 查询是否开启：
    SHOW VARIABLES LIKE 'general_log';

3. 慢查询日志（Slow Query Log）

  • 记录执行时间超过 long_query_time（默认 10 秒）的 SQL 语句，用于优化查询性能。
  • 关键场景：SQL 性能优化、查询分析。
  • 开启方式：
    [mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
  • 查询是否开启：
    SHOW VARIABLES LIKE 'slow_query_log';

4. 二进制日志（Binary Log，Binlog）

  • 记录所有对数据库造成变更的 SQL 语句（如 INSERT、UPDATE、DELETE），但不记录 SELECT 语句。
  • 关键场景：数据恢复、主从复制、增量备份。
  • 配置参数：
    [mysqld]
log_bin = mysql-bin
binlog_format = ROW  # 支持 ROW、STATEMENT、MIXED
  • 查询当前 Binlog：
    SHOW BINARY LOGS;

5. 中继日志（Relay Log）

  • 仅用于 MySQL 复制（Replication），从库用于存储主库传递的 Binlog 日志。
  • 关键场景：主从复制。

6. InnoDB 事务日志（Redo Log & Undo Log）

  • Redo Log（重做日志）：记录事务提交前的更改，用于崩溃恢复。
  • Undo Log（回滚日志）：用于事务回滚和 MVCC（多版本并发控制）。
总结
日志类型作用适用场景
错误日志记录 MySQL 运行错误服务器维护、崩溃分析
查询日志记录所有 SQL调试 SQL、审计
慢查询日志记录执行慢的 SQLSQL 性能优化
二进制日志记录数据变更 SQL数据恢复、主从复制
中继日志存储主库的 BinlogMySQL 复制
事务日志Redo 和 Undo 日志事务管理、崩溃恢复

你可以根据项目需求选择合适的日志进行监控和优化！😊

今天就来聊聊 redo log（重做日志）、binlog（归档日志）、两阶段提交、undo log（回滚日志）。

redo log（重做日志）

1. redo log 的作用

redo log 是 InnoDB 存储引擎 独有的一种物理日志，主要用于 数据持久化，即使 MySQL 崩溃或宕机，也可以通过 redo log 恢复已提交的数据，保证数据库的一致性。

MySQL 在更新数据时，并不会立即把数据写入磁盘，而是先更新内存中的数据页（Buffer Pool），然后记录 redo log，再按照特定策略把 redo log 持久化到磁盘，确保数据不会丢失。

2. redo log 的工作流程

2.1 数据查询

  • MySQL 以 页（Page） 为单位管理数据，单个数据页的大小一般为 16KB。
  • 当查询一条记录时，MySQL 会先在 Buffer Pool（内存缓存池） 查找：
    • 若数据已在 Buffer Pool，直接返回；
    • 若数据不在 Buffer Pool，则从磁盘加载相应的数据页到内存。
  • 这样可以减少磁盘 I/O 操作，提高查询效率。

2.2 数据更新

数据更新时，MySQL 遵循 WAL（Write-Ahead Logging，预写日志） 机制：

  1. 修改 Buffer Pool 中的数据页（不会立即写入磁盘）。
  2. 记录 redo log（写入 redo log buffer）。
  3. 根据特定策略刷盘，将 redo log buffer 刷入 redo log 文件。
  4. 数据页在某个时机（Checkpoint 或后台线程）才会真正写入磁盘。

这种方式避免了频繁的随机 I/O，减少磁盘写入压力，提高性能。

3. redo log 刷盘时机

MySQL 并不会立即将 redo log buffer 直接写入磁盘，而是采用 特定时机 进行刷盘，以提高写入效率。主要的刷盘时机有：

3.1 事务提交

  • 默认情况下，每次事务提交都会触发 redo log buffer 刷盘。
  • 受参数 innodb_flush_log_at_trx_commit 控制。

3.2 log buffer 空间不足

  • redo log buffer 的大小是有限的，默认 16MB（可通过 innodb_log_buffer_size 配置）。
  • 当 redo log buffer 占满 50% 时，就会触发写入 redo log 文件。

3.3 事务日志缓冲区满

  • 如果事务的 redo log 过大，可能会导致 log buffer 被填满，从而强制刷盘。

3.4 Checkpoint（检查点）

  • InnoDB 定期执行 检查点（Checkpoint），将脏页（Dirty Pages）和 redo log 一起刷新到磁盘，确保数据一致性。

3.5 后台线程刷盘

  • InnoDB 通过 后台线程 每隔 1 秒将 redo log buffer 写入 redo log 文件，减少 I/O 开销。

3.6 MySQL 正常关闭

  • 当 MySQL 关闭 时，会将所有 redo log buffer 刷入磁盘，保证所有事务日志持久化。

4. innodb_flush_log_at_trx_commit 刷盘策略

innodb_flush_log_at_trx_commit 控制 事务提交时 redo log 的刷盘行为，该参数有三种取值：

值行为持久性性能
0事务提交时不立即刷盘，后台每秒一次可能丢失 1 秒数据最高
1（默认）事务提交时立即刷盘最安全，不丢数据最低
2事务提交时仅写入操作系统缓存，每秒 fsync 刷盘可能丢失 1 秒数据适中

4.1 innodb_flush_log_at_trx_commit = 0

  • 事务提交时不刷盘，仅将 redo log buffer 写入 page cache（文件系统缓存）。
  • 后台线程每秒一次 fsync，写入 redo log 文件。
  • 宕机可能丢失 1 秒内的事务数据，但性能最高。

4.2 innodb_flush_log_at_trx_commit = 1（默认）

  • 事务提交时立即刷盘，fsync 写入 redo log 文件。
  • 最安全，不丢失已提交事务，但磁盘 I/O 最多，性能最低。

4.3 innodb_flush_log_at_trx_commit = 2

  • 事务提交时写入 page cache（文件系统缓存），不立即 fsync 刷盘。
  • 后台线程每秒 fsync，写入 redo log 文件。
  • 仅 MySQL 崩溃不会丢数据，但服务器宕机可能丢失 1 秒数据。

总结：

  • 业务对数据持久性要求高（如金融交易）→ 设为 1（保证提交即持久化）。
  • 追求极致性能（如高并发日志系统）→ 设为 0（最大吞吐量，但可能丢失 1 秒数据）。
  • 兼顾性能和安全 → 设为 2（仅系统崩溃可能丢数据）。

5. redo log 存储结构

redo log 不是一个单一的文件，而是多个日志文件组成的“日志文件组”，以 环形队列 方式存储：

  1. write pos（写入位置）：当前写入 redo log 的位置，不断向前推进。
  2. checkpoint（检查点）：当前可以擦除的 redo log 位置，定期后移。
  3. 日志空间满了怎么办？
    • 不能写入新日志，必须等 checkpoint 向前推进，腾出空间。

6. MySQL 8.0.30 及以上版本的 redo log 变化

在 MySQL 8.0.30 之后：

  • innodb_log_files_in_group 和 innodb_log_file_size 被废弃。
  • 改用 innodb_redo_log_capacity 变量控制 redo log 大小。
  • 固定为 32 个 redo log 文件，每个文件大小 = innodb_redo_log_capacity / 32。

示例验证

  • MySQL 8.0.32 配置：
    [mysqld]
innodb_redo_log_capacity=1G
  • 计算文件大小：
    1GB / 32 = 32MB（每个 redo log 文件大小）
  • 实际存储目录：
    ls -lh /var/lib/mysql/#innodb_redo/

7. redo log vs. 直接写数据页

有人可能会问：为什么不直接每次修改数据时就刷盘，而要用 redo log？

方式数据页写入redo log 写入
直接刷数据页每次修改都写入 16KB 数据页，且是 随机写，I/O 开销大-
redo log事务提交前不刷数据页仅写入 几十字节的 redo log 记录，是顺序写，性能高

优点

  • 减少磁盘 I/O：直接写数据页是 随机写，redo log 是 顺序写，更快。
  • 事务原子性：redo log 记录的是变更，崩溃时可恢复，直接写数据页可能部分写入导致数据不一致。
  • 高并发：MySQL 可在事务提交时刷 redo log，而不是每次都刷数据页，提高吞吐量。
结论

redo log 通过 预写日志（WAL）机制 记录数据变更，确保数据安全，同时提升 MySQL 并发能力：

  • 避免频繁刷数据页，减少磁盘 I/O 开销。
  • 提供数据恢复能力，保证已提交事务持久化。
  • 支持高并发事务，提升数据库性能。

binlog（归档日志）

1. binlog 的作用

binlog（二进制日志）是 MySQL Server 层 产生的 逻辑日志，记录的是 SQL 语句的逻辑操作，而不是具体的物理修改。它的主要用途包括：

  • 数据恢复（通过 binlog 进行增量恢复）。
  • 主从复制（主库写入 binlog，从库读取 binlog 并重放，实现数据同步）。
  • 审计日志（记录所有修改数据的操作）。
  • 主主同步（双主架构，两台 MySQL 互为主备）。

2. binlog 与 redo log 的区别

区别binlogredo log
日志类型逻辑日志物理日志
记录内容记录 SQL 语句逻辑（如 UPDATE T SET c = c + 1 WHERE id = 2）记录 数据页 具体修改（页号 + 偏移量 + 变更值）
作用范围适用于所有存储引擎仅适用于 InnoDB
写入机制事务提交时一次性写入WAL 机制，先写 redo log，后写数据页
持久化策略sync_binlog 控制 fsync 频率innodb_flush_log_at_trx_commit 控制 fsync 频率
主要作用复制、恢复、审计崩溃恢复、事务持久化

3. binlog 记录格式

binlog 有 3 种格式，可通过 binlog_format 参数控制：

  1. statement（语句模式）

    • 记录 SQL 语句本身，如：
      UPDATE T SET c = c + 1 WHERE id = 2;
    • 优点：占用空间小，性能较高。
    • 缺点：某些情况下可能导致数据不一致。例如 UPDATE t SET update_time=NOW() 在主库和从库执行时，NOW() 可能返回不同值。

  2. row（行模式）

    • 记录 具体的数据变更，如：
      @1=2, @2="old_value" -> @1=2, @2="new_value"
    • 优点：可以 精确还原数据，不会有 NOW() 这样的同步问题。
    • 缺点：占用空间更大，影响恢复速度。

  3. mixed（混合模式）

    • 默认使用 statement，但 遇到不安全的语句（如 NOW()）时切换为 row。
    • 折中方案，既考虑空间，又能保证数据一致性。

4. binlog 写入流程

事务执行时，binlog 不会立刻写入磁盘，而是先写入 binlog cache，然后 事务提交时一次性刷盘。

4.1 binlog 写入流程

  1. 事务执行时
    • binlog 记录 SQL 逻辑，写入 binlog cache（内存）。
  2. 事务提交时
    • 将 binlog cache 写入 page cache（文件系统缓存）。
    • 触发 fsync，将数据真正写入 binlog 文件（持久化）。

4.2 sync_binlog 控制刷盘策略

sync_binlog 参数决定 binlog 刷盘的时机：

sync_binlog 值行为持久性性能
0事务提交时 仅 write，系统决定何时 fsync可能丢失 binlog（宕机丢失 page cache）最高
1（默认）每次事务提交后 fsync，数据最安全最安全（binlog 立即持久化）最低
N（N > 1）事务提交时 先 write，累积 N 个事务后 fsync宕机可能丢失 N 个事务 binlog折中
  • sync_binlog=1（默认）：保证 每个事务提交后立即持久化，数据不会丢失，但性能较低。
  • sync_binlog=0：性能最好，但 系统宕机会丢失 binlog。
  • sync_binlog=N（N > 1）：批量 fsync，提高性能，但 宕机会丢失 N 个事务的 binlog。

4.3 binlog 缓存控制

  • MySQL 会为 每个线程 分配一块 binlog cache（大小由 binlog_cache_size 控制）。
  • 如果 binlog cache 用完（事务过大），会写入 临时磁盘文件，影响性能。
  • binlog_cache_size 太小，可能导致 binlog 频繁写入磁盘，建议适当调整。

5. binlog 在主从复制中的作用

MySQL 主从复制 依赖 binlog 进行数据同步：

  1. 主库 记录事务的 binlog，并写入 binlog 文件。
  2. 从库 通过 IO 线程 读取主库 binlog，写入 relay log（中继日志）。
  3. SQL 线程 读取 relay log 并执行，完成数据同步。

主从复制模式：

  • 异步复制：主库提交事务后，不等待从库，存在数据延迟问题。
  • 半同步复制：主库至少等待 一个 从库收到 binlog，减少数据丢失风险。
  • 组复制（Group Replication）：多个节点采用 Paxos 或 Raft 协议，保证数据一致性。

6. binlog 的作用与 redo log 配合

6.1 binlog & redo log 双写机制

MySQL 事务提交时，同时写入 redo log 和 binlog：

  1. 先写 redo log（prepare 状态）
  2. 写 binlog
  3. redo log 变更为 commit，事务提交完成

为什么要同时写这两个日志？

  • redo log 解决 崩溃恢复（保证事务原子性）。
  • binlog 解决 数据复制与恢复（主从同步、增量恢复）。
  • 如果先写 binlog，再写 redo log，MySQL 崩溃可能导致 binlog 有记录，但事务未提交，导致主从不一致。
  • 正确顺序：redo log（prepare） → binlog → redo log（commit）

7. binlog 与 redo log 的核心区别总结

特点binlogredo log
日志类型逻辑日志（SQL 语句）物理日志（数据页变更）
作用范围所有存储引擎（MyISAM、InnoDB）仅 InnoDB
持久化策略sync_binlog 控制 fsync 频率innodb_flush_log_at_trx_commit 控制 fsync 频率
写入方式事务提交时写入先写 redo log，再写数据页
作用数据备份、恢复、主从同步崩溃恢复、事务原子性
结论
  1. binlog 是逻辑日志，记录 SQL 语句，主要用于 主从复制、数据恢复。
  2. binlog 通过 sync_binlog 控制刷盘策略，影响数据安全性和性能。
  3. binlog & redo log 搭配使用，保证事务的完整性，避免数据丢失：
    • redo log 保证崩溃恢复
    • binlog 保证复制和数据一致性
  4. 正确的事务提交流程：
    1. redo log（prepare）
    2. binlog
    3. redo log（commit）
    • 这样可以保证事务的原子性和主从一致性。

binlog 是 MySQL 复制、恢复和数据一致性的核心机制，合理配置 binlog 刷盘策略，可以在安全性和性能之间找到平衡。

两阶段提交（Two-Phase Commit）

1. 为什么需要两阶段提交？

在 MySQL 中，redo log 和 binlog 都是用于保证事务持久化的日志：

  • redo log（重做日志）：用于 崩溃恢复，保证事务持久化。
  • binlog（归档日志）：用于 主从复制、数据恢复，保证数据一致性。

但是，它们的写入时机不同：

  • redo log 可以在事务执行过程中不断写入（WAL 机制）。
  • binlog 只有 事务提交时 才写入。

2. redo log 和 binlog 不一致会有什么问题？

假设我们执行以下 update 语句：

UPDATE T SET c = 1 WHERE id = 2;

如果 redo log 写完，但 binlog 写入失败，会导致以下情况：

  1. MySQL 崩溃后，通过 redo log 恢复，数据 c=1（新值）。
  2. 通过 binlog 进行主从同步或数据恢复，发现 binlog 没有这条记录，导致 c=0（旧值）。
  3. 最终数据不一致：
    • 原库数据 c=1
    • 备份或从库数据 c=0（因为 binlog 里没有这条更新记录）

3. 两阶段提交（2PC）的解决方案

为了避免 redo log 和 binlog 不一致，MySQL 采用“两阶段提交”（Two-Phase Commit, 2PC）：

  • 事务提交分为 两个阶段：prepare 和 commit。
  • 先记录 redo log 的 prepare 状态，再写 binlog，最后 commit redo log。

阶段 1：Prepare（预提交）

  1. 执行 SQL 语句，修改 Buffer Pool 中的数据页。
  2. 写 redo log，标记为 prepare 状态（不提交）。
  3. 刷盘 redo log（持久化），确保崩溃后可恢复。

阶段 2：Commit（提交）

  1. 写入 binlog（持久化）。
  2. 提交 redo log（修改状态为 commit）。
  3. 事务提交成功。

4. 两阶段提交如何保证数据一致性？

两阶段提交的关键在于 如果某个阶段失败，MySQL 能正确处理事务。

场景 1：binlog 写入失败

  • redo log 已经 prepare，但 binlog 未写入，此时 MySQL 崩溃。
  • 恢复时
    • MySQL 发现 redo log 处于 prepare 状态，但没有对应的 binlog。
    • 回滚事务，保证数据一致性。

场景 2：redo log commit 失败

  • redo log 已 prepare，binlog 已写入，但 redo log commit 失败。
  • 恢复时
    • MySQL 发现 redo log 处于 prepare 状态。
    • 但是可以 找到对应 binlog 记录，说明事务已经提交。
    • MySQL 继续提交 redo log，保证数据一致性。

5. 两阶段提交的核心逻辑

两阶段提交的核心是 通过 redo log 的 prepare 状态 解决 redo log 和 binlog 不一致的问题：

  • binlog 未写入 → 事务回滚
  • binlog 已写入 → 事务提交
事务提交流程：
1. redo log → `prepare`
2. binlog → 写入
3. redo log → `commit`
结论
  • 两阶段提交确保 binlog 和 redo log 逻辑一致，保证 崩溃恢复 & 主从复制 数据不丢失。
  • MySQL 事务提交顺序：
    1. 先 prepare redo log（预提交）
    2. 再写入 binlog（记录归档日志）
    3. 最后 commit redo log（完成提交）
  • MySQL 恢复机制：
    • binlog 丢失 → redo log 处于 prepare 状态，事务回滚。
    • redo log 丢失 → 事务根据 binlog 重新提交。

两阶段提交是 MySQL 保证数据一致性的关键机制！

undo log（回滚日志）

1. 什么是 undo log？

undo log（回滚日志）是 InnoDB 存储引擎 用于 事务回滚（Rollback）和多版本并发控制（MVCC） 的日志，属于 逻辑日志，记录的是 数据的历史版本。

在 MySQL 事务执行过程中：

  • redo log 负责数据持久化，确保崩溃恢复。
  • undo log 负责回滚事务，保证事务的原子性（Atomicity）。

2. undo log 的作用

undo log 主要有两个作用：

  1. 事务回滚（Rollback）

    • 当事务发生错误或主动回滚时，MySQL 需要撤销已经执行的 SQL 语句，undo log 记录的是 反向 SQL 操作，用于恢复数据到事务开始前的状态。

  2. MVCC（多版本并发控制）

    • InnoDB 使用 MVCC 解决并发读写问题，undo log 记录了历史数据版本，可以提供一致性读（Consistent Read）。

3. undo log 记录方式

undo log 采用逻辑日志方式，即它记录的是逻辑反向操作，不同 SQL 语句的 undo log 记录方式如下：

SQL 语句undo log 记录的反向操作
INSERT INTO T VALUES (1, 'A')DELETE FROM T WHERE id = 1
DELETE FROM T WHERE id = 1INSERT INTO T VALUES (1, 'A')
UPDATE T SET name = 'B' WHERE id = 1UPDATE T SET name = 'A' WHERE id = 1

undo log 以逻辑日志的方式记录 SQL 反向操作，而不是像 redo log 记录物理页的变更。

4. undo log 的存储结构

undo log 采用 segment（段） 的方式存储：

  1. undo log segment（回滚日志段）：

    • 每个 undo 操作占用 一个 undo log segment。
    • undo log segment 存储在 rollback segment（回滚段） 中。

  2. rollback segment（回滚段）：

    • 每个 rollback segment 包含 1024 个 undo log segment。
    • 事务开始时，MySQL 会为其分配一个 rollback segment，用于存储 undo log。

  3. rollback segment header（回滚段头）：

    • rollback segment 的第一个页存储 undo log 相关信息。

  4. history list（历史版本列表）：

    • 记录所有 已提交但未被清理（purge） 的 undo log。
    • MySQL 的 purge 线程会定期清理已不需要的 undo log，减少存储占用。

5. undo log 的清理机制

undo log 不会永久存储，为了优化数据库性能，MySQL 采用 purge 线程 进行清理。

5.1 undo log 何时可以删除？

  • INSERT 操作的 undo log

    • 事务提交后可以 立即删除，因为回滚后不需要恢复新插入的数据。

  • UPDATE/DELETE 操作的 undo log

    • 不能立即删除，因为可能有其他事务正在读取历史版本（MVCC）。
    • 会加入 history list，等待 purge 线程 进行清理。

5.2 purge 线程（清理历史版本）

  • InnoDB 采用 后台 purge 线程 定期清理 已不需要的 undo log。
  • purge 线程会从 history list 找到 不再被任何事务访问的 undo log，然后删除。

6. undo log 与 MVCC（多版本并发控制）

MVCC（Multi-Version Concurrency Control，多版本并发控制） 依赖 undo log 来实现 一致性读。

6.1 MVCC 依赖的三个关键数据：

  1. 隐藏字段

    • InnoDB 记录每行数据的 DB_TRX_ID（创建该记录的事务 ID）。
    • 记录上一版本数据的指针 DB_ROLL_PTR，用于回溯历史版本。

  2. Read View

    • 事务开始时，MySQL 创建一个 Read View，确定该事务可以看到的数据版本。
    • Read View 不会看到比自己开始时间更新的事务数据，但可以看到已提交的历史版本。

  3. undo log

    • 如果数据行的 DB_TRX_ID 在 Read View 之后，说明该数据不可见，需要回溯 DB_ROLL_PTR 读取 undo log 获取历史版本。

6.2 事务读取不同版本的数据：

  • 事务 T1 读取数据 id=1：
    • 发现 DB_TRX_ID > T1（即数据是事务 T2 更新的，T1 不可见）。
    • 通过 DB_ROLL_PTR 找到 undo log，读取历史版本数据。

7. undo log vs redo log vs binlog

类型undo log（回滚日志）redo log（重做日志）binlog（归档日志）
日志类型逻辑日志物理日志逻辑日志
作用事务回滚、MVCC崩溃恢复数据备份、主从复制
记录内容反向 SQL 语句物理页变更（数据页号+偏移量+修改值）SQL 语句
写入时机事务执行时事务执行时事务提交时
存储位置InnoDB 内部（rollback segment）redo log buffer → redo log 文件binlog cache → binlog 文件
是否会清除会清理（purge 线程）不会清理不会清理
持久性保障依赖 redo log 保护持久化存储持久化存储

8. 事务日志总结

  1. undo log 负责事务回滚和 MVCC，用于撤销已执行的 SQL，并提供一致性读。
  2. redo log 负责崩溃恢复，在崩溃后保证数据的持久化。
  3. binlog 负责归档日志，用于主从复制、数据备份。

InnoDB 同时使用 redo log 和 undo log，确保：

  • 事务持久性（Durability） → redo log
  • 事务原子性（Atomicity） → undo log
  • 数据一致性（Consistency） → MVCC + undo log
  • 数据复制（Replication） → binlog

undo log 通过 逻辑日志 + 历史版本存储 实现 事务回滚 & MVCC，是 MySQL 事务机制的重要组成部分。

上次编辑于: 2025/2/8 23:13:36
贡献者: jiang