MySQL InnoDB 三大文件日志

 Undo Log

意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作。Undo Log 属于逻辑日志，记录一个变化过程。主要采用段的方式管理和记录。在 InnoDB 数据文件中包含一种 rollback segment 回滚段（内部包含了 1024 个 Undo Log Segment），可以通过参数 innodb_undo 命令控制 Undo Log。

show variables like '%innodb_undo%';
复制代码

Undo Log 在事务开始前产生；事务在提交时，并不会立刻删除 Undo Log，InnoDB 会将该事务对应的 Undo Log放入到删除列表中，后面会通过后台线程 purge thread 进行回收处理。例如：执行一个 delete，Undo Log 会记录一个 insert；执行一个 Update，Undo Log 会记录一个相反的 Update 操作。

Undo Log 的作用

• 实现事务的原子性

Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

• 实现多版本并发控制（MVCC）

Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log 保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读的操作。

我们看个案例，如下：

图中可以看到，事务 A 手动开启事务，执行了更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。事务 B 手动开启事务，执行了查询操作，此时会读取 Undo Buffer 中的日志数据并返回（进行了快照读操作）。

Redo Log 和 Binlog

Redo Log 和 Binlog 是 MySQL 日志系统中非常重要的两种机制。

Redo Log

顾名思义，就是重做，主要用于在数据库发送意外时进行数据的恢复。

Redo Log 会随着事务操作的执行而生成。在事务提交的时候会将产生的 Redo Log 写入到 Log Buffer 中，并不是随着事务的提交就立刻写入到磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，此时Redo Log 占用的空间就可以重用（被覆盖写入）。

Redo Log 的工作原理图

Redo Log 写入机制

Redo Log 的文件内容是以存下循环的方式写入文件中的，写满时则会回溯到第一个文件，进行覆盖写。

如图所示：

• Write Pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头
• Check Ponit 是当前要擦除的位置，也是往后移动并且循环的，擦除记录前要把记录更新到数据文件中

Write Pos 和 Check Point 之间还空着的部分可以用来记录新的操作。如果 Write Pos 追上 Check Point 则表示已写满，此时不能再继续执行新的更新了，需要停下来擦掉一些记录，把 Check Point 推进一下。

Redo Log 的相关配置

每个 InnoDB 引擎至少拥有 1 个重做日志文件组（Redo Log Group），每个文件组至少有 2 个重做日志文件（Redo Log）,默认为 ib_logfile0 和 ib_logfile1。Redo Buffer 持久化到 Redo Log 的策略，可以通过参数 Innodb_flush_log_at_trx_commit 设置：

• Innodb_flush_log_at_trx_commit =0：

• • 表示每秒提交 Redo Buffer -> OS Cache -> flush cache to disk，这个过程可能会丢失 1 秒内的事务数据（主要由后台的 Master 线程每隔 1 秒执行一次操作）。

• Innodb_flush_log_at_trx_commit =1：

• • 表示每次事务提交执行时，Redo Buffer -> OS Cache -> flush cache to disk，这是最安全且性能最差的方式。

• Innodb_flush_log_at_trx_commit =2：

• • 表示每次事务提交执行时：Redo Buffer -> OS Cache，然后由后台的 Master 线程每隔 1 秒执行 OS Cache -> flush cahce to disk 的操作。

一般建议选择取值 2 ，这样的话 MySQL 挂了时候不会丢失数据，整个 MySQL 访问挂了才会损失 1 秒的事务提交数据。

如图：

Binlog 日志

Binlog 记录模式

Redo Log 是属于 InnoDB 引擎所特有的日志，而 MySQL Server 也有自己的日志，这个日志就是 Binary Log（二进制日志），简称 Binlog。

Binlog 以事件的形式记录了所有数据库表结构变更以及表数据修改的二进制日志，还包含了语句所执行的消耗时间，但是不会记录 Select 和 Show 这类操作。开启 Binlog 日志有以下两个重要的使用场景。

• 主从复制：在主库中开启 Binlog 功能，这样主库就可以把 Binlog 传递给从库，从库拿到 Binlog 后实现数据库恢复达到主从数据的一致性。
• 数据恢复：通过 mysqlbinlog 工具来恢复数据。

Binlog 文件名默认为“主机名_binlog-序列号”格式，例如 oak_binlog-000001，也可以在配置文件中指定名称。文件记录模式有 STATEMENT、ROW 和 MIXED 三种，具体含义如下：

• ROW（row-based replication ，简称 RBR）：日志中会记录每一行数据被修改的情况，然后在 slave 端对相同的数据进行修改。

• • 优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。
  • 缺点：批量操作，会产生大量的日志，尤其是 alter table 会让日志暴涨。

• STATEMENT（statement-based replication，简称 SBR）：每一条被修改的数据的 SQL 都会记录到 Master 的 Binlog 中，Slave 在复制的时候 SQL 进程会解析成和原来 Master 端执行过的相同的 SQL 再次执行。简称 SQL 语句复制。

• • 优点：日志量小，减少磁盘的 IO，提升存储和恢复速度
  • 缺点：在某些情况下会导致主从数据不一致，比如：last_insert_id()、now() 等函数。

• MIXED（mixed-based replication，简称 MBR）：以上两种模式的混合使用，一般会使用 STATEMENT 模式保存 Binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 Binlog，MySQL 会根据执行的 SQL 语句选择写入模式。

Binlog 文件结构

MySQL 的 Binlog 文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是 Log Event。不同的修改操作对应着不同的 Log Event。比较常用的 log event 有：Query Event、Row Event、Xid Event 等。Binlog 文件的内存就是各种 Log Event 的集合。

我们看下 Binlog 文件中的 Log Event 的结构图，如下：

Binlog 写入机制

• 根据记录模式和操作触发 event 事件并生成 Log Event（事件触发执行机制）
• 将事务执行过程中产生的 Log Event 写入缓冲区，每个事务线程都有一个缓冲区。Log Event 保存在一个 binlog_cache_mngr 数据结构中，在该结构中有两个缓冲区，一个是 stmt_cache，用于存放不支持事务的消息；另一个是 trx_cache，用于存放支持事务的消息。

• 事务在提交阶段会将产生的 Log Event 写入到外部的 Binlog 文件中。不同事务以串行的方式将 Log Event 写入到 Binlog 文件中，所以一个事务包含的 Log Event 信息在 Binlog 文件中是连续的，中间不会插入其它事务的 Log Event。

Binlog 文件操作相关命令

• 查看 Binlog 状态

• • show variables like 'log_bin';

• 开启 Binlog 功能

• • set global log_bin=mysqllogbin；(这里可能会报错：ERROR 1238 （HY000）: Variable 'log_bin' is a read only variable)，这个时候需要修改 my.cnf 或 my.ini 配置文件，在 [mysqld] 下面增加 log_bin=mysql_bin_log，重启 MYSQL 服务。

#log-bin=ON
#log-bin-basename=mysqlbinlog
binlog-format=ROW
log-bin=mysqlbinlog
复制代码

• • 使用 show binlog events 命令

show binary logs;// 等价于 show master logs;
show master status;
show binlog events;
show binlog events in 'mysqlbinlog.000001';
复制代码

• • 使用 mysqlbinlog 命令

mysqlbinlog "文件名"
mysqlbinlog "文件名" > "test.sql"
复制代码

• • 使用 binlog 恢复数据

-- 指定时间恢复
mysqlbinlog --start-datetime="2020-0425 18:00:00" --stop-datetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234

-- 按事件位置号恢复
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p1234
复制代码

mysqlddump：定期全部备份数据库数据。mysqlbinlog 可以做增量备份和恢复操作。

• • 删除 Binlog 文件

-- 删除指定文件
purge binary logs to 'mysqlbinlog.000001';
-- 删除指定时间之前的文件
purge binary logs before '2020-0428 00:00:00';
-- 清除所有文件
reset master;
复制代码

可以通过设置 expire_logs_days 参数来启动自动清理功能。默认值为 0 表示没启用。设置为 1 表示超出 1 天 Binlog 文件会自动删除掉。

• Redo Log 和 Binlog 区别

• • Redo Log 是属于 InnoDB 引擎功能，Binlog 是属于 MySQL Server 自带功能，并且是以二进制文件进行记录的。
  • Redo Log 属于物理日志，记录该数据页更新状态内容，Binlog 是逻辑日志，记录更新的过程。

• • Redo Log 日志是循环写的，日志的空间大小是固定的，Binlog 是追加写入，写完一个写下一个，不会覆盖使用。
  • Redo Log 作为服务器异常宕机后，事务数据自动恢复使用，Binlog 可以作为主从复制和数据恢复使用。Binlog 没有自动 crash-safe 能力。

链接：https://juejin.cn/post/7037492319955714078