MySQL 事务机制是如何实现的？

写在前面

周末，我与阿里 P9 资深技术专家（这里就不说名字了），聊起了 MySQL 这个话题，为啥会聊这个呢？因为他看到我出版了一部《MySQL 技术大全：开发、优化与运维实战》，对书籍的评价也是不错的。随后，我们聊了关于 MySQL 的几个话题，其中一个就是 MySQL 的日志机制。今天，我就把大概聊的一些内容以书面文章的形式分享给大家。希望能够为小伙伴们带来实质性的帮助！

文章已收录到：

https://github.com/sunshinelyz/technology-binghe

https://gitee.com/binghe001/technology-binghe

MySQL 日志

说起 MySQL 的日志，有三种类型的日志对于 MySQL 来说是至关重要的，这三种日志分别为：Binlog、Undo Log 和 Redo Log。

由于 Binlog 和 UndoLog 有类似的地方，所以，我们按照如下顺序依次介绍 MySQL 中的三大日志原理：Undo Log——> Redo Log ——> Binlog。

Undo Log 日志

什么是 Undo Log

顾名思义，Undo Log 的字面意思就是撤销操作的日志，指的是使 MySQL 中的数据回到某个状态。

在 MySQL 数据库中，事务开始之前，MySQL 会将待修改的记录保存到 Undo Log 中，如果数据库崩溃或者事务需要回滚时，MySQL 可以通过利用 Undo Log 日志，将数据库中的数据回滚到之前的状态。

MySQL 新增、修改和删除数据时，在事务开始前，就会将信息写入 Undo Log 中。事务提交时，并不会立刻删除 Undo Log， InnoDB 存储引擎会将事务对应的 Undo Log 放入待删除列表中，之后会通过后台的 purge thread 对待删除的列表进行删除处理。这里，值得注意的是：Undo Log 是一种 逻辑日志， 记录的是一个变化过程。比如，MySQL 执行一个 delete 操作，Undo Log 就会记录一个 insert 操作；MySQL 执行一个 insert 操作，Undo Log 就会记录一个 delete 操作；MySQL 执行一个 update 操作，Undo Log 就会记录一个相反的 update 操作。

Undo Log 以段的方式来管理和记录日志信息，在 InnoDB 存储引擎的数据文件中，包含了一种叫做 rollback segment 的回滚段，其内部包含了 1024 个 undo log senment。

Undo Log 作用

Undo Log 对于 MySQL 实现事务来说，起着至关重要的作用，它实现了事务的原子性和多版本并发控制，也就是我们经常说的 MVCC。

• 实现事务的原子性

Undo Log 能够实现 MySQL 事务的原子性，在事务的处理过程中，如果 MySQL 出现了错误或者用户手动执行了事务的回滚操作（执行了 rollback 操作），MySQL 可以利用 Undo Log 日志将数据库中的数据恢复到之前的状态。

• 实现 MVCC 机制

Undo Log 在 MySQL 的 InnoDB 存储引擎中实现了多版本并发控制（MVCC）机制。事务未提交前，Undo Log 保存了未提交之前的版本数据，Undo Log 中的数据可以作为旧版本数据的副本或者快照以便其他并发事务进行读取操作。

事务 A 手动开启事务后，对 goods 数据表中 id 为 1 的数据进行更新操作，首先会把更新命中的数据写入到 Undo Buffer 中。在事务 A 未提交之前，此时，事务 B 手动开启事务，对 goods 数据表中的 id 为 1 的数据进行查询操作，此时的事务 B 会读取 Undo Log 中的数据并返回给客户端，这就是 MySQL 中的 MVCC 机制。

可以在 MySQL 中通过下面的命令来查看控制 Undo Log 日志的参数。

show variables like '%innodb_undo%';

Redo Log 日志

说了 MySQL 中的 Undo Log，我们再来看看 MySQL 中的 Redo Log 日志。

什么是 Redo Log

顾名思义 Redo Log 的字面意思就是重做日志，指的是在数据库出现意外情况时能够对重新执行某种操作。在 MySQL 中，事务中修改的任何数据，都会将最新的数据写入 Redo Log 中进行备份。

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

Redo Log 的原理

Redo Log 能够实现事务的持久性，防止在发生故障的时间点，有脏页未写入表的 ibd 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而将未提交的事务进行持久化。这个过程可以简化为下图所示。

Redo Log 的写机制

Redo Log 文件的内容是以顺序循环的方式写入文件的，写满时就会回到第一个文件，进行覆盖写。

• Write Pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头；

• CheckPoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；

Write Pos 和 CheckPoint 之间还空着的部分，可以用来记录新的操作。如果 Write Pos 追上 CheckPoint，表示已经写满，此时就需要向后移动 CheckPoint 来擦除数据。

每个 InnoDB 存储引擎至少有 1 个重做日志文件组（group），每个文件组至少有 2 个重做日志文件，默认为 ib_logfile0 和 ib_logfile1 。

可以在 MySQL 中通过如下命令来查看控制 Redo Log 的参数。

show variables like '%innodb_log%';

Redo Log 写入机制

在 Redo Log 日志信息从 Redo Buffer 持久化到 Redo Log 时，具体的持久化策略可以通过 innodb_flush_log_at_trx_commit 参数进行设置，具体策略如下所示。

• 0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数据。由后台 Master 线程每隔 1 秒执行一次操作。

• 1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，这种方式最安全，性能最差。

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

一般建议选择取值 2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失 1 秒的事务提交数据。

Binlog 日志

什么是 Binlog

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

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

• 数据恢复：通过 mysqlbinlog 等工具来恢复数据

关于 Binlog 的使用场景可以参见我出版的图书《MySQL技术大全：开发、优化和运维实战》一书。

Binlog 文件记录模式

Binlog 文件记录模式有 STATEMENT、ROW 和 MIXED 三种，具体含义如下。

ROW 模式

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

优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。

缺点：批量操作，会产生大量的日志，尤其是 alter table 会让日志暴涨。

STATMENT 模式

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

优点：日志量小，减少磁盘 IO，提升存储和恢复速度

缺点：在某些情况下会导致主从数据不一致，比如 last_insert_id()、now()等函数。

MIXED 模式

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

Binlog 文件结构

对于 MySQL 的 Binlog 文件结构有三种版本，见下图。

关于 Binlog 文件结构的具体信息，小伙伴们可以参考 MySQL 的官方文档，具体链接为：https://dev.mysql.com/doc/internals/en/event-header-fields.html

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 功能，需要修改 my.cnf 或 my.ini 配置文件，在[mysqld]下面增加 log_bin=mysql_bin_log，重启 MySQL 服务。

binlog-format=ROWlog-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="2021-02-28 18:00:00" --stopdatetime="2021-03-01 00:00:00" mysqlbinlog.000001 | mysql -uroot -p123456//按事件位置号恢复mysqlbinlog --start-position=1789 --stop-position=2674 mysqlbinlog.000001| mysql -uroot -p123456

删除 Binlog 文件

purge binary logs to 'mysqlbinlog.000001'; //删除指定文件purge binary logs before '2021-03-01 00:00:00'; //删除指定时间之前的文件reset master; //清除所有文件

可以通过设置 expire_logs_days 参数来启动自动清理功能。默认值为 0 表示没启用。设置为大于 0 的整数表示超出多少天 binlog 文件会自动清除。

好了，今天就到这儿吧，我是冰河，大家有啥问题可以在下方留言，也可以加我微信：sun_shine_lyz，我拉你进群，一起交流技术，一起进阶，一起进大厂~~