MySQL日志机制详解

MySQL 八股

Java开发

发布日期: 2024-05-14

更新日期: 2024-08-13

文章字数: 6.6k

阅读时长: 23 分

阅读次数:

前面总结过了MySQL的锁机制，这里我们展开聊聊日志文件的机制；

日志概述

在MySQL中更新语句的流程会涉及到undo log（回滚日志）、redo log（重做日志）、binlog（归档日志）：

undo log：即为Innodb存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC；
redo log：是Innodb存储引擎层生成的日志，实现类事务中的持久性操作，主要用于掉电等故障恢复的情况；
binlog：归档日志，是Server层生成的日志文件，主要用于数据备份和主从复制的情况；

给出本文需要解决的问题大纲：

一、为什么需要undo log？

1.1 功能场景介绍

当我们在SQL语句的’‘增删改’'语句（DML语句）等，虽然我们并没有主动输入begin开启事务和commit提交事务，但是MySQL会主动隐式开启事务进行自动提交的操作等，其中也有参数autocommit决定等；

所以这里如果出现这样的问题：一个事务在执行的过程中，如果此时还没提交事务之前，MySQL反而发生了崩溃，如何回滚到事务之前的数据呢？需要进行备份等？

实现这样的机制就是 undo log（回滚日志），它保证了事务的ACID特性中的原子性

undo log 是一种用于撤销回退的日志，在事务没有提交之前，也就是执行事务时候，MySQL会记录更新前的数据到 undo log 日志文件中，当事务回滚时候，可以利用undo log进行回滚。

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如：

在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

在发生回滚时，就读取 undo log 里的数据，然后做原先相反操作。比如当 delete 一条记录时，undo log 中会把记录中的内容都记下来，然后执行回滚操作的时候，就读取 undo log 里的数据，然后进行 insert 操作。

其中，一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

通过 trx_id 可以知道该记录是被哪个事务修改的；
通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；

除了上述undo log可支持事务回滚操作之外，undo log还可以通过ReadView + undo log实现MVCC（多版本并发控制）

对于「读提交」和「可重复读」隔离级别的事务来说，它们的快照读（普通 select 语句）是通过 Read View + undo log 来实现的，它们的区别在于创建 Read View 的时机不同：

「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录

这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列（trx_id 和 roll_pointer）」的比对，如果不满足可见行，就会顺着 undo log 版本链里找到满足其可见性的记录，从而控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

1.2 总结undo log 的作用

实现事务回滚，保障事务的原子性；事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
实现MVCC（多版本并发控制）关键因素之一；MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

undo log 是如何刷盘（持久化到磁盘）？

undo log 和数据页的刷盘策略是一样的，都需要通过redo log保证持久化。

buffer pool 中有undo 页，对undo 页的修改也都会记录到redo log中去。redo log会每秒刷盘，提交事务时候也会刷盘，数据页和undo 页都是靠这个机制保证持久化的。

二、为什么需要Buffer Pool？

MySQL中的数据都是存储在磁盘中的，那么我们要更新一条记录的时候，需要从磁盘读取该数据，然后在内存中对该记录进行修改。修改完这条数据选择直接缓存起来，便于下次查询语句命中查询，可以直接读取缓存中的记录，不需要从磁盘中再次获取了。

为此，Innodb存储引擎设计一个缓冲池操作，来提高数据库的读写能力：

有了 Buffer Poo 后：

当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

2.1 Buffer Pool 缓存什么？

【页】作为磁盘和内存交互的基本单位，一个页的默认大小是16kb。因此，Buffer Pool是同样需要按照【页】进行划分。

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，申请物理内存，接着将虚拟地址和物理地址建立映射关系。

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

Undo 页是记录什么？

开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

查询一条记录，就只需要缓冲一条记录的吗？

并不是。当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。

三、为什么需要redo log？

Buffer Pool确实是可以提高了读写效率，但是由于Buffer Pool是基于内存的，而内存总是不可靠，如果此时出现重启情况，还没来得及罗盘的脏页数据就会丢失。

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。

InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

好处：

WAL 技术的另外一个优点：MySQL 的写操作从磁盘的「随机写」变成了「顺序写」，提升语句的执行性能。

redo log ？

redo log 是物理日志，记录了某个数据页中做了什么修改，比如对XX空间中的YY数据页ZZ偏移量的地方做了AA修改更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

在事务提交的时候，只要先将redo log持久化到磁盘中即可，可以不需要等到缓存在Buffer Pool里的脏页数据持久化到磁盘。

虽然脏页数据并没有持久化，但是redo log已经持久化了，接着MySQL重启之后，可以根据redo log 的内容，将所有数据进行恢复到最新状态；

被修改 Undo页面，需要记录对应redo log 吗？

需要的。开启事务中后，InnoDB层更新记录前，首先要记录相对应的undo log，如果是更新曹祖，需要把被更新的列的旧值记录下来，也就是要生成一条undo log，undo log会写入Buufer Pool 中的Undo 页面中去。

不过，在内存修改该 Undo 页面后，需要记录对应的 redo log。

redo log 和 undo log 区别在哪？

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务：

所以有了redo log ，再通过WAL技术，InnoDB就可以保证即使数据库发生异常重启之后，之前提交的记录都不会丢失，这个能力被称为crash-safe(崩溃恢复)。 redo log 保证了事务四大特性中的持久性。

其实MySQL已经存在了数据写入的操作，为什么还要加上redo log 写入磁盘的操作呢？或者说两个之间存在什么区别？

写入redo log 的方式使用了追加操作，其实磁盘操作就是顺序写；但是写入数据首先需要找到的是写入位置，接着才可以写入磁盘中，使用的磁盘操作是随机写；
使用了redo log 会存在两个好处“
- **实现事务的持久性，让MySQL存在crash-safe能力。**能够保证MySQL在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
- 将写操作从随机写转化为顺序写，提升了MySQL的写入磁盘的能力；

3.1 redo log 什么时候进行刷盘？

缓存在redo log buffer里的redo log 还是在内存中，主要存在以下的几个时机：

MySQL 正常关闭时；
当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘；这个策略可由 innodb_flush_log_at_trx_commit 参数控制

这三个参数的应用场景是什么？

这三个参数的数据安全性和写入性能的比较如下：

数据安全性：参数 1 > 参数 2 > 参数 0
写入性能：参数 0 > 参数 2> 参数 1

所以，数据安全性和写入性能是熊掌不可得兼的，要不追求数据安全性，牺牲性能；要不追求性能，牺牲数据安全性。

在一些对数据安全性要求比较高的场景中，显然 innodb_flush_log_at_trx_commit 参数需要设置为 1。
在一些可以容忍数据库崩溃时丢失 1s 数据的场景中，我们可以将该值设置为 0，这样可以明显地减少日志同步到磁盘的 I/O 操作。
安全性和性能折中的方案就是参数 2，虽然参数 2 没有参数 0 的性能高，但是数据安全性方面比参数 0 强，因为参数 2 只要操作系统不宕机，即使数据库崩溃了，也不会丢失数据，同时性能方便比参数 1 高。

3.2 redo log 文件写满了怎么办？

默认情况下，InnoDB存储引擎有1个重做日志文件组，「重做日志文件组」由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫：ib_logfile0 和 ib_logfile1 。

在重做日志组中，每个 redo log File 的大小是固定且一致的，假设每个 redo log File 设置的上限是 1 GB，那么总共就可以记录 2GB 的操作。

重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。

所以，InnoDB存储引擎会先写ib_logfile0文件，当ib_logfile0 文件被写满的时候，会切换到ib_logfile1文件；如此这样来回进行切换即可。顺序如下：

四、为什么需要binlog？

前面介绍的undo log 和 redo log 两个日志文件都是Innodb存储引擎生成的；

MySQL在完成一条更新操作后，Server层都还是会生成一条binlog，等之后事务提交的时候，会将该事务执行过程红产生的所以binlog统一写入到binlog文件中去；

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

4.1 redo log 和 binlog 有什么区别？

这两个日志有四个区别。

1、适用对象不同：

binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
redo log 是 Innodb 存储引擎实现的日志；

2、文件格式不同：

binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：
- STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
- ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
- MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；
redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；

3、写入方式不同：

binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

4、用途不同：

binlog 用于备份恢复、主从复制；
redo log 用于掉电等故障恢复。

问题：如果不小心将整个数据库的数据删除了，能用redo log 文件进行数据的恢复吗？

不可以使用redo log 文件恢复，只能使用binlog 文件恢复；

因为redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据物理日志，已经刷入磁盘的数据都会从redo log 文件里面擦除；

binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在binlog上的数据，都是可以恢复的，如果不小心整个数据库都被删除了，这时候得用binlog 文件恢复数据；

4.2 主从复制是怎么实现？

MySQL的主从复制依赖于binlog，也就是记录MySQL上的所有变化并且以二进制形式保存在磁盘中，复制的过程就是将binlog中的数据从主库传输到从库上。

这一过程一般是异步实现的，也就是主库上执行事务操作的线程不会等待复制binlog的线程同步完成。

MySQL集群的主从复制过程梳理成3个阶段：

写入Binlog：主库写binlog日志，提交事务，并更新本地存储数据；
同步Binlog：把binlog 复制到所有从库上去，每个从库把binlog写到暂存日志中去；
回放Binlog：回放binlog，并更新存储引擎中的数据；

在完成主从之后，就可以实现写数据时候只写主库，在读数据时候只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行；

从库是不是越多越好？为什么？

不是的，由于从库数量的增加，从库连接上来的I/O线程也比较多，主库也要创建同样多的log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽请求；

所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。/ 个人项目中就一主一从足够了；

MySQL主从复制还有哪些模型？

三种：

同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

4.3 binlog 什么时候刷盘？

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开的，因此无论这个事务有多大（比如有很多条语句），也要保证一次性写入。这是因为有一个线程只能同时有一个事务在执行的设定，所以每当执行一个 begin/start transaction 的时候，就会默认提交上一个事务，这样如果一个事务的 binlog 被拆开的时候，在备库执行就会被当做多个事务分段自行，这样破坏了原子性，是有问题的。

MySQL 给每个线程分配了一片内存用于缓冲 binlog ，该内存叫 binlog cache，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

上述三个日志讲完了，至此可以先小结下，update语句的执行过程。

当优化器分析出成本最小的执行计划之后，执行器就按照执行计划开始进行更新操作：

具体更新一条记录：UPDATE t_user SET name = 'KJ' WHERE id = 1; 流程如下：

执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取id = 1的这一行记录：
- 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
- 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
执行器得到聚簇索引记录之后，会看一下更新前的记录和更新后的记录是否一样：
- 如果一样的话就不进行后续更新流程；
- 如果不一样的话就把更新前的记录和更新后的记录都当成参数传给InnoDB层，让InnoDB 真正的执行更新记录的操作；
开启事务，InnoDB层更新记录之前，首先需要记录对应的undo log，因为这里是更新操作，需要把被更新的列旧值记录下来，也就是生成一条undo log ， undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该Undo 页面后，需要记录对应的redo log；
InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。
至此，一条记录更新完成了；
在一条更新语句执行完成后，然后开始记录该语句对应的 binlog ，此时记录的 binlog 会被保存在 binlog cache中，并没有刷新到硬盘上的 binlog 文件，在事务提交时候才会统一将该事务运行过程中的所有binlog 刷新到硬盘中去。
事务提交，剩下就是两阶段提交；

五、为什么需要两阶段提交？

在持久化 redo log 和 binlog 这两份日志的时候，如果出现半成功的状态，就会造成主从环境的数据不一致性。这是因为 redo log 影响主库的数据，binlog 影响从库的数据，所以 redo log 和 binlog 必须保持一致才能保证主从数据一致。

**MySQL中为了避免出现两份日志之间的逻辑不一致情况，使用了【两阶段提交】来进行解决，**两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功情况~

5.1 两阶段提交的过程是怎样的？

在MySQL的InnoDB存储引擎中，开启binlog情况下，MySQL会同时维护binlog日志和InnoDB的redo log，为了保证这两个日志的一致性，MySQL使用了内部XA事务，内部 XA 事务由 binlog 作为协调者，存储引擎是参与者。

当客户端执行 commit 语句或者在自动提交的情况下，MySQL 内部开启一个 XA 事务，分两阶段来完成 XA 事务的提交。

事务的提交过程有两个阶段，就是将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

prepare 阶段：将 XID（内部 XA 事务的 ID）写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用）；
commit 阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功；

补充上述：

7：事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）：

prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；