前言

Github：https://github.com/HealerJean

博客：http://blog.healerjean.com

1. 重做日志（redo log）`
2. 回滚日志（undo log）`
3. 二进制日志（binlog）`
4. 错误日志（errorlog）`
5. 慢查询日志（slow query log）`
6. 一般查询日志（general log）

binlog文件记录了mysql所有的增、删、改语句，只要有了这个 binlog，我们就拥有了mysql的完整备份了。

一、三大日志

日志划分：redo log 是物理日志，undo log 和 binlog 是逻辑日志

⬤ 逻辑日志：可以简单理解为记录的就是sql 语句。

⬤ 物理日志：因为mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

协同机制：事务提交时 redo log 和 binlog 的写入通过 prepare/commit 状态保证一致性

1、redo log（重做日志）

物理日志：记录的是”在某个数据页上做了什么修改”

核心作用：保证事务的持久性（ACID 中的 D），崩溃恢复时重放未刷盘的脏页

1）基本概念与组成

redo log 包括两部分：一个是内存中的日志缓冲 ( redo log buffer )，另一个是磁盘上的日志文件(redo log file)。

mysql 每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的 WAL (Write-Ahead Logging) 技术。

• 内存部分：redo log buffer，日志缓存，事务执行过程中，修改操作会先写入内存中的 redo log buffer，每个线程共享同一缓冲区（不同于 binlog 的线程独立缓存）

• 磁盘部分：redo log file：日志文件，默认由 ib_logfile0 和 ib_logfile1 组成，循环写入固定大小的文件，写满后触发检查点（checkpoint）清理可覆盖的日志

• 写入流程：通过 innodb_flush_log_at_trx_commit 参数控制刷盘策略

  redo log buffer → write → OS page cache → fsync → disk
  

2）刷盘策略与未提交事务的持久化

innodb_flush_log_at_trx_commit 参数

• 0（延迟写）：事务提交时不会将 redo log buffer 中日志写入到 os buffer，而是每秒写入 os buffer 并调用 fsync() 写入到 redo log file 中。也就是说设置为 0 时是（大约）每秒刷新写入到磁盘中的，当系统崩溃，会丢失 1 秒钟的数据。

• 1（实时写，实时刷）：事务每次提交都会将 redo log buffer 中的日志写入 os buffer 并调用 fsync() 刷到 redo log file 中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO 的性能较差。

• 2（实时写，延迟刷）：每次提交都将 redo log buffer 中的日志仅写入到 os buffer，然后是每秒调用 fsync() 将 os buffer 中的日志写入到 redo log file。

3）生命周期

1. 事务开始后：就开始产生 redo log 日志了，在事务执行的过程中，redo log 开始逐步落盘
2. 脏页刷盘后：当对应事务的脏页写入到磁盘之后，redo log 的使命就完成了，它所占用的空间也就可以被覆盖了。 。
3. 循环写入：redo log 文件组大小固定（如 2×1GB），写满后从头覆盖，通过 write pos 和 checkpoint 指针协调

4）常见问题

a、0 和 2 区别是啥？

• 参数设为 0
  • 行为：系统每秒批量写入日志到磁盘，事务提交时无磁盘 I/O 操作。
  • 风险：若 MySQL 进程突然崩溃，这时 redo log buffer 就丢了，最后 1 秒的日志分析结果丢失
  • 优势：吞吐量提升 30%+（实测 RAID 环境下性能差异约 10%）
• 参数设为 2

  • 行为：订单支付成功后，日志立即写入 page cache，但磁盘刷盘由操作系统控制。

  • 风险：服务器断电时，若 page cache，未刷盘，最近 1 秒的订单状态更新丢失

  • 优势：相比设为 1，并发写入性能提升 20%，且比设为 0 更安全（仅断电有风险）

b、OS Buffer 作用是啥？

在计算机操作系统中，用户空间 (user space) 下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过先写入操作系统缓冲区 (OS Buffer)。因此，redo log buffer 写入 redo log file 实际上是先写入OS Buffer，然后再通过系统调用 fsync()将其刷到redo log file中

c、为什么会出现 redo log？

1. 解决「随机 I/O 性能瓶颈」：
   • 问题背景：InnoDB 以数据页（16KB）为单位进行磁盘 I/O，但事务可能仅修改页内少量字节（如更新某行的一个字段）。若每次修改都刷整个页到磁盘，会产生大量无效 I/O（如仅修改 100B 却需写入 16KB）
   • redo log 的优化：仅记录物理修改（如“页号+偏移量+新值”），日志体积小且顺序写入磁盘，避免随机 I/O。例如，一个事务修改 10 个不同页的数据，redo log 只需顺序写入 10 条记录，而非随机写入 10 个分散的页
2. 保证事务的持久性（ACID 中的 D）：记录事务提交后数据的物理修改（如“页号 X 的偏移量 Y 处值从 A 改为 B ”），用于崩溃恢复时重放已提交但未刷盘的修改，确保持久性（D）

d、未提交事务的 redo log 可能持久化的场景?

• 后台线程定时刷盘：若参数设为 0，每 1 秒触发一次 write + fsync，可能将未提交事务的日志持久化到磁盘
• 并行事务提交触发：若参数设为 1，事务 B 提交时会连带事务 A 的未提交日志一起刷盘
  • 假设一个事务 A 执行到一半，已经写了一些redo log到buffer中，这时候有另外一个线程的事务 B 提交，如果innodb_flush_log_at_trx_commit设置的是 1 ，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务A 在redo log buffer里的日志一起持久化到磁盘。
• redo log buffer 空间不足：当占用超过 innodb_log_buffer_size（默认 8MB）一半时，后台线程会主动 write 到 page cache（不调用 fsync）

2、undo log（回滚日志）

逻辑日志，记录的是逆向 SQL（如 INSERT 对应 DELETE）

作用：

• 实现事务回滚（ACID 中的 A）

• 实现 MVCC 的关键：存储事务版本链

1）核心功能

a、事务回滚（原子性保障）

为保证事务的原子性（要么全执行，要么全不执行），MySQL 在执行变更前会记录回滚日志（Undo Log），用于事务失败时回滚。

InnoDB 引擎使用 undo log（回滚日志）来保证原子性操作，你对数据库的每一条数据的改动（INSERT、DELETE、UPDATE）都会被记录到 undo log 中，比如以下这些操作，当事务执行失败或者调用了 rollback 方法时，就会触发回滚事件，利用 undo log 中记录将数据回滚到修改之前的样子。

• 你插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。

• 你删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。

• 你修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。

b、多版本并发控制（MVCC，隔离性保障）

作用：为并发事务提供数据的历史版本，实现非锁定读（快照读）：

• 可重复读（REPEATABLE READ）：事务读取的是事务开始时的数据快照，通过 Undo Log 的版本链实现

• 读已提交（READ COMMITTED）：每次读取最新已提交版本，通过 Undo Log 避免脏读

2）工作原理

a、写入流程

• 事务开始：分配一个回滚段（Rollback Segment）

• 修改数据前：将原始数据备份到 Undo Log 中，并更新 DB_ROLL_PTR 指向该记录

• 事务提交/回滚：

• 提交：Undo Log 放入链表，供后续 Purge 线程清理

• 回滚：根据 Undo Log 恢复数据到修改前的状态

b、版本链与 MVCC

• 事务 A 更新 name='Tom' 为 name='Jerry'，Undo Log 记录旧值 Tom 并链接到版本链。
• 事务 B 查询时，若事务 A 未提交，则通过版本链读取旧值 Tom

3）常见问题

a、与 Redo Log 的关系

• 持久化依赖：Undo Log 的修改会记录到 Redo Log，确保 Undo Log 本身的持久性

• 崩溃恢复：崩溃时，先通过 Redo Log 恢复数据页和 Undo 页，再通过 Undo Log 回滚未提交事务

3、binlog（归档日志）

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog 是 mysql 的逻辑日志，并且由Server层进行记录，使用任何存储引擎的 mysql 数据库都会记录binlog日志。

特点：binlog 是通过追加的方式进行写入的，可以通过max_binlog_size 参数设置每 个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

1）核心作用

• 主从复制：主库（Master）将 Binlog 发送给从库（Slave），从库重放日志实现数据同步，是 MySQL 高可用架构的基础

• 数据恢复：记录所有数据修改操作（DDL/DML），结合全量备份可实现时间点恢复（PITR）。例如，通过 mysqlbinlog 工具重放日志到指定时间点

• 增量备份与审计：记录增量变更，减少全量备份频率；通过解析日志追踪数据变更历史

2）Binlog 的三种格式

binlog日志有三种格式，分别为STATMENT、ROW和MIXED。

在 MySQL 5.7.7之前，默认的格式是 STATEMENT，MySQL 5.7.7 之后，默认值是 ROW。日志格式通过binlog-format指定。

a、STATMENT

基于SQL 语句的复制 (statement-based replication, SBR)， 每一条修改数据的 sql 都会记录到 master 的 binlog 中，slave 在复制的时候，sql 进程会解析成和原来在 master 端执行时的相同的sql 再执行。

• 优点：在 statement 模式下首先就是解决了 row 模式的缺点，不需要记录每一行数据的变化，从而减少了 binlog 的日志量，，节约了 IO , 从而提高了性能；

• 缺点：

  • 在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。 在 statement 模式下，由于它是记录的执行语句，所以，为了让这些语句在 slave 端也能正确执行，那么它还必须记录每条语句在执行的时候的一些相关信息，即上下文信息，以保证所有语句在 slave 端和在master 端执行结果相同。
  • 另外就是，由于 MySQL 现在发展比较快，很多新功能不断的加入，使 MySQL 的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug 也就越容易出现。在statement 中，目前已经发现不少情况会造成 MySQL 的复制出现问题，主要是在修改数据的时候使用了某些特定的函数或者功能才会出现，比如：sleep() 函数在有些版本中就不能被正确复制，在存储过程中使用了 last_insert_id() 函数，可能会使 slave 和 master 上得到不一致的 id 等等。由于 row 模式是基于每一行来记录变化的，所以不会出现类似的问题。

b、ROW

基于行的复制，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。然后在 slave 端再对相同的数据进行修改。row 模式只记录要修改的数据，只有 value，不会有 sql 多表关联的情况。

⬤ 优点：在 row 模式下，binlog 中可以不记录执行的 sql 语句的上下文相关的信息，仅仅只需要记录哪一条记录被修改了，修改成什么样了，所以 row 的日志内容会非常清楚的记录下每一行数据的修改细节，非常容易理，而且不会出现某些特定情况下的存储过程和 function，以及 trigger的调用和触发无法被正确复制问题。；

⬤ 缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨

c、MIXED

基于STATMENT 和 ROW 两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用 STATEMENT 模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

MySQL 会根据执行的每一条具体的SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。新版本中的 statment 还是和以前一样，仅仅记录执行的语句。而新版本的 MySQL 也对 row 模式做了优化，并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。

3）工作原理

• 写入流程：
  1. 事务提交时：操作记录为事件（Event）写入内存缓存（Binlog Cache）
  2. 刷盘策略：根据 sync_binlog 参数（推荐设为1）决定何时同步到磁盘
  3. 两阶段提交（InnoDB）：
     1. Prepare 阶段：Redo Log 标记为准备状态
     2. Commit 阶段：Binlog 刷盘后，Redo Log 提交，确保崩溃恢复一致性
• 文件结构
  • 索引文件：mysql-bin.index 记录所有 Binlog 文件列表
  • 日志文件：mysql-bin.000001（按序号递增）

4）binlog 写入机制

其实，binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache写到binlog 文件中。 一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了binlog cache的保存问题。

a、Binlog Cache 的内存管理

• 线程私有性：每个线程拥有独立的 binlog cache，由参数 binlog_cache_size 控制其内存大小（默认 32KB）。若事务产生的日志超过该限制，超出的部分会暂存到磁盘临时文件，避免内存溢出

• 事务完整性：无论事务大小，binlog cache 必须完整保存事务的所有日志事件，确保提交时一次性写入 binlog 文件。例如，一个事务修改 100万 行数据，其日志可能远超 binlog_cache_size，此时会触发磁盘暂存，但提交时仍会合并写

b、写入流程的两阶段：write 与 fsync

• write 阶段：将 binlog cache 中的日志写入文件系统的 page cache（内存缓冲区），此过程由操作系统异步管理，速度极快且不直接占用磁盘 I/O

• fsync 阶段：调用 fsync() 强制将 page cache 中的数据刷入磁盘，此时才会真正占用磁盘 I/OPS。该操作由参数 sync_binlog 控制触发时机

5）二阶段提交的核心流程

关键：

• 两阶段提交通过 原子性绑定 redo log 和 binlog，确保两者要么都成功，要么都失败

• 若 binlog 写入失败，事务会回滚，避免主从数据不一致

a、准备阶段（Prepare Phase）

• InnoDB 准备

​ 事务的修改先写入 redo log，并标记为 PREPARE 状态（此时事务未提交，但已持久化到磁盘）

​ redo log 刷盘由参数 innodb_flush_log_at_trx_commit=1 控制（默认每次提交刷盘）

• 目的：确保事务的修改可恢复，即使后续崩溃也能通过 redo log 重做或回滚

b、提交阶段（Commit Phase)

• Binlog 写入：MySQL Server 层将事务的逻辑操作（如 SQL 语句或行变更）写入 binlog 文件，并调用 fsync 刷盘（由 sync_binlog 参数控制）

• InnoDB 提交：binlog 写入成功后，redo log 状态从 PREPARE 更新为 COMMIT，事务正式提交

6）常见问题

a、崩溃恢复与数据一致性

• 风险场景：若 sync_binlog=N 且主机崩溃，最近 N 个已提交事务的 binlog 可能丢失，但 redo log 已持久化（若 innodb_flush_log_at_trx_commit=1），导致主从数据不一致。此时 MySQL 会报错 "binary log is shorter than expected"，需人工介入从备份恢复

• 两阶段提交（2PC）保障：binlog 与 redo log 通过 2PC 机制协同

  • Prepare 阶段：redo log 标记为 PREPARE 状态

  • Commit 阶段：binlog 持久化后，redo log 标记为 COMMIT。

    • 确保两者状态一致，崩溃后可根据 binlog 完整性决定提交或回滚

b、为什么需要 redo log与 binlog 两者协同

• Binlog 的局限性：
  • 无 Crash-Safe 能力：InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe，Binlog 仅在事务提交阶段（COMMIT）写入，若崩溃发生在提交前（如 PREPARE 阶段），Binlog 中不会有该事务的记录，但此时 InnoDB 可能已修改内存数据页（通过 redo log 持久化）
  • 逻辑日志的恢复效率低：通过 binlog 恢复需重放所有 SQL，而 redo log 直接恢复物理数据页，速度更快
• Redo Log 的局限性：
  • 引擎特异性：仅 InnoDB 支持 redo log，其他引擎（如 MyISAM）无法依赖它实现崩溃恢复
  • 日志覆盖风险：redo log 空间固定，写满后需覆盖旧日志，若未及时刷盘则历史操作可能丢失
• 协同价值
  • 互补性：redo log 保证 实时持久性，binlog 提供 长期可追溯性，二者结合覆盖了从崩溃恢复到数据同步的全场景需求
  • 两阶段提交（2PC）：通过 prepare-commit 状态同步，确保 redo log 和 binlog 逻辑一致，避免主从数据分歧

c、Crash-Safe 的实现原理

• 扫描 redo log：找到所有 PREPARE 状态的事务（未完整提交）

• 检查 binlog：
  • 若 binlog 中存在对应事务的完整记录 → 提交事务（重做）
  • 若 binlog 中无记录 → 回滚事务（撤销）
• 数据页修复：通过 redo log 恢复已提交但未刷盘的物理数据页

二、ACID

InnoDB 通过 Undo Log（原子性）、锁与 MVCC（隔离性）、Redo Log（持久性） 的协同，最终实现 一致性，构成完整的 ACID 保障体系

Binlog 并不直接参与持久性保障，但两者通过协同机制（如两阶段提交）间接关联

1、原子性（Atomicity）

定义：事务中的操作要么全部成功，要么全部失败回滚，不存在部分成功的情况。

实现机制：

• Undo Log（回滚日志）

  • 每条数据变更（INSERT/UPDATE/DELETE）前，InnoDB 会先在 Undo Log 中记录修改前的数据状态（如原始值、行标识等）

  • 若事务回滚，InnoDB 根据 Undo Log 逆向执行操作（如 INSERT 的逆向是 DELETE）恢复数据

• 关键设计
  • Undo Log 的修改会记录到 Redo Log，确保 Undo Log 本身的持久性（即使崩溃也能恢复回滚所需信息）

示例：转账事务中，若扣款成功但存款失败，Undo Log 会撤销扣款操作，保证账户总额不变

2、 一致性（Consistency）

定义：事务执行前后，数据库必须满足所有完整性约束（如主键、外键）和业务逻辑一致性（如账户总额不变）。

​实现机制​​：要保证数据库的数据一致性，要在以下两个方面做努力：

• 数据库的特性：
  • 约束校验：事务提交时检查外键、唯一键等约束，违反则回滚（如转账后账户余额为负会触发回滚）
  • 比如声明某个列为 NOT NULL 来拒绝 NULL值得插入等。
• 绝大部分还是需要我们程序员保证
  • 原子性、隔离性、持久性的协同：
    • 原子性确保操作全或无，隔离性避免并发干扰，持久性保证提交后数据永久生效，三者共同支撑一致性

示例：用户A和B共有5000元，无论转账多少次，事务结束后总额仍为5000元

3、隔离性（Isolation）

定义：多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

• 隔离性可能会引入脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）等问题，为了解决这些问题就引入了“隔离级别”的概念。

​实现机制​​：

• 锁机制

  • 行级锁：写操作加排他锁（X 锁），阻塞其他事务修改同一行

  • 间隙锁（Gap Lock）：在可重复读隔离级别下锁定范围，防止幻读（如阻止其他事务在查询范围内插入新记录）

• MVCC（多版本并发控制）：每个事务启动时生成一个 Read View，基于事务 ID 和版本链判断数据可见性

  • 读已提交：每次查询生成新 Read View，看到其他事务已提交的修改

  • 可重复读（默认）：事务内复用同一 Read View，保证多次读取结果一致

示例：事务A读取数据时，MVCC 会返回事务开始前的快照版本，避免读到事务 B 未提交的修改（脏读）

⬤ 读未提交（read uncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到。

⬤ 读已提交（read committed）：一个事务提交之后，它做的变更才会被其他事务看到。

⬤ 可重复读（repeatable read） 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

⬤ 和串行化（serializable）： 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

SQL 标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下：