【数据库】MySQL中bin log、redo log、undo log详解

bin log、redo log、undo log简介

• bin log：归档日志。是逻辑日志，存储的是数据库变更的数据，包括 INSERT、UPDATE、DELETE、DDL等操作。（数据库层面日志）
• redo log：重做日志。使得MySQL在崩溃后有了恢复能力（innodb存储引擎日志）
• undo log：回滚日志。用于事务异常/主动回滚的场景（innodb存储引擎日志）

redo log（存储引擎日志）

MySQL中，查询一条数据，会将该数据相关页的所有数据一次性加载出来，放入 Buffer Pool中，后续都是从Buffer Pool中进行查找；对数据做修改时，如果数据在Buffer Pool中存在，则直接更新里面数据，然后将更新操作记录到 redo log buffer中，等待合适时间刷新到redo log 文件中。

落盘时机

• 提交事务：通过innodo_flush_log_at_trx_commit控制，在事务提交时，直接刷新到磁盘中
  • innodo_flush_log_at_trx_commit = 0：提交事务时不进行刷盘操作，性能最高，安全性低
  • innodo_flush_log_at_trx_commit = 1：每次提交都进行刷盘操作，性能最低，安全性最高
  • innodo_flush_log_at_trx_commit = 2：每次提交，将log buffer 中数据写入page cache，
• buffer空间不足：当redo log buffer中存储的 redo log 超过容量的一半左右，会将日志存储的磁盘上。
• checkpoint：innodb有定期检查的操作，会将内存中脏数据（已修改，但尚未落盘的数据）刷新到磁盘中，这时会将redo log 一同刷新
• 后台刷新线程：innodb后台有个线程，周期性（1秒）的将脏页刷新到磁盘中，此时会将redo log 疫情通刷新
• 正常关闭数据库：MySQL关闭时，redo log 会刷新到磁盘中

redo log 写入时机

• 开始事务
• 执行SQL语句
• 写入redo log日志
• 提交事务
  

日志组文件

• redo log的日志文件是以日志文件组的形式出现的，每个redo日志文件大小都是一样的，采用环形数组的形式，从头开始写，写到结尾继续从头开始写。

• 日志文件组中有两个重要的属性

  • write pos：当前记录的位置，边写边向后推移
  • checkpoint：需要清除的位置，边清除边向后推移

• 当redo log记录到日志文件组中，write pos向后推移

• MySQL从日志文件组中恢复数据时，会清空加载过得redo log，checkpoint 向后推移

• write pos和checkpoint中间的的差值就是可写的空间

为什么不直接刷盘？

前面提到过，数据是以数据页的形式加载的，数据修改可能仅修改了部分很小额数据就要把整个数据页进行刷盘，会造成大量IO。

binlog （数据库日志）

逻辑日志，记录的是SQL语句的原始逻辑，不管什么存储引擎，只要发生了表数据的更新，就会产生binlog日志。

主要用途

主要依赖binlog进行数据同步，保证数据一致性

• 数据备份
• 主备
• 主主
• 主从

记录格式

• statement：记录内容是SQL的原语句 select * from table where date = now()
• row：不仅记录原始语句，还记录操作的具体数据，select * from table where date='20250317'，不可直接查看，需要使用mysqlbinlog工具来解析
• mixed：会判定SQL语句在不同的时候执行是否结果不同
  • 相同：使用statement格式进行记录
  • 不相同：使用row格式进行记录

写入时机

• 开始事务
• 记录操作变更语句和信息到binlog cache中
• 事务提交阶段，先同步binlog到磁盘，再通知存储引擎提交事务
• 提交事务

写入策略

• sync_binlog
  • 1：事务每次提交后，同步binlog到磁盘，安全，性能低
  • N：（N>1）每N次提交后同步一次
  • 0：异步同步，性能高，安全性低

两阶段提交

Innodb在执行更新语句时会记录redo log和binlog日志，两种日志在写入时机不一样

• redo log：事务提交过程中随时都会写
• binlog：只有事务提交时才会写入
  如果在执行一个更新语句期间，记录了redolog但是未记录binlog，会发生什么情况？
  例如 update table set name = 'A1' where id=1，
• 将id=1的名称改为 A1，此时redolog写完，写入binlog发生异常
• 从库使用binlog进行恢复时，id=1的数据name=A与主库 name=A1就会发生数据不一致的情况
  为杜绝上述问题的出现，innodb使用两阶段提交的方法来进行解决
  redo log的写入拆分成两步
• prepare：如果没有commit阶段，代表失败会进行回滚
• commit
  

undo log

回滚日志，每个事务对数据的修改都会记录到undo log中，当事务执行过程中出现异常，MySQL利用undo log中的数据链进行数据追溯，将数据恢复到开始之前的状态。

例如：
insert into table values(1,'A',15) 会在undo log中有一条记录

update table set name='A1' where id=1 也会在undo log中生成一条记录

TX_ID=2记录中 ROL_POINT回滚指针指向的是 TX_ID记录的地址。

这样就能保证，更新失败后，能够通过记录找到原始数据进行恢复