面试题：MySQL 数据恢复中的日志应用

Question

在 MySQL 中，二进制日志（binlog）和重做日志（redo log）在数据恢复过程中分别起到什么作用？请详细阐述它们的工作机制以及如何协同工作来确保数据的一致性和可恢复性。

Answer 1

二进制日志（binlog）

1. 作用：主要用于主从复制和数据备份恢复。记录数据库的逻辑更改，比如执行的 SQL 语句。用于恢复数据库到某个时间点，也用于主库将更新操作发送给从库实现数据同步。
2. 工作机制：
   • 写入时机：采用“写后日志”，在事务提交时，一次性将该事务的所有逻辑日志写入到 binlog 中。
   • 写入模式：有 statement、row、mixed 三种格式。Statement 记录 SQL 语句；Row 记录每一行数据的修改；Mixed 是两者结合，根据不同情况选择合适的格式。
   • 持久化：通过 sync_binlog 参数控制，0 表示由系统自行决定何时将 binlog 刷盘，1 表示每次事务提交都将 binlog 刷盘，大于 1 表示累计到 N 个事务提交后将 binlog 刷盘。

重做日志（redo log）

1. 作用：保证在发生故障时，已提交的事务对数据的修改不会丢失，确保事务的持久性。主要用于崩溃恢复（crash - recovery）。
2. 工作机制：
   • 写入时机：采用“写前日志”，在执行事务过程中，当数据页发生修改时，就将修改记录写入到 redo log 中。
   • 写入模式：循环写，空间使用完后会覆盖旧的日志。
   • 持久化：通过 innodb_flush_log_at_trx_commit 参数控制，0 表示每秒将 redo log 缓冲区内容刷盘；1 表示每次事务提交都将 redo log 刷盘；2 表示每次事务提交都将 redo log 写入到文件系统缓存，但由系统决定何时刷盘到磁盘。

协同工作

1. 事务执行过程：
   • 事务开始，数据修改时，先将修改记录写入 redo log 缓冲区，同时记录逻辑日志到 binlog 缓存。
   • 事务执行过程中，redo log 持续记录数据页修改，而 binlog 缓存累积逻辑日志。
2. 事务提交时：
   • 先将 redo log 缓冲区的内容持久化到磁盘，确保已提交事务的修改不会丢失。
   • 接着将 binlog 缓存的内容持久化到磁盘。这一过程通过两阶段提交（2PC）保证 redo log 和 binlog 的一致性。
3. 崩溃恢复：
   • 数据库重启时，先读取 redo log，将未完成的事务回滚，并将已提交但未更新到数据文件的事务重新应用，恢复到崩溃前已提交事务的状态。
   • 然后通过 binlog 进行 point - in - time recovery（PITR），可以恢复到某个时间点的状态，比如基于时间、位置等进行恢复。通过这种协同工作，确保了数据的一致性和可恢复性。