MySQL 日志 15 连问

前言

金九银十已经来了，整理了 15 道经典 MySQL 日志面试题，希望对大家有帮助。

1. redo log 是什么? 为什么需要 redo log？

redo log 是什么呢?

• redo log 是重做日志。

• 它记录了数据页上的改动。

• 它指事务中修改了的数据，将会备份存储。

• 发生数据库服务器宕机、或者脏页未写入磁盘，可以通过 redo log 恢复。

• 它是 Innodb 存储引擎独有的

为什么需要 redo log？

• redo log 主要用于 MySQL 异常重启后的一种数据恢复手段，确保了数据的一致性。

• 其实是为了配合 MySQL 的 WAL 机制。因为 MySQL 进行更新操作，为了能够快速响应，所以采用了异步写回磁盘的技术，写入内存后就返回。但是这样，会存在 crash 后内存数据丢失的隐患，而 redo log 具备 crash safe 的能力。

2. 什么是 WAL 技术, 好处是什么.

• WAL，中文全称是 Write-Ahead Logging，它的关键点就是日志先写内存，再写磁盘。MySQL 执行更新操作后，在真正把数据写入到磁盘前，先记录日志。

• 好处是不用每一次操作都实时把数据写盘，就算 crash 后也可以通过 redo log 恢复，所以能够实现快速响应 SQL 语句。

3. redo log 的写入方式

redo log 包括两部分内容，分别是内存中的日志缓冲(redo log buffer)和磁盘上的日志文件(redo log file)。

mysql 每执行一条 DML 语句，会先把记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种先写日志，再写磁盘的技术，就是 WAL。

在计算机操作系统中，用户空间(user space)下的缓冲区数据，一般是无法直接写入磁盘的，必须经过操作系统内核空间缓冲区(即 OS Buffer)。

• 日志最开始会写入位于存储引擎 Innodb 的 redo log buffer，这个是在用户空间完成的。

• 然后再将日志保存到操作系统内核空间的缓冲区(OS buffer)中。

• 最后，通过系统调用fsync()，从 OS buffer 写入到磁盘上的 redo log file 中，完成写入操作。这个写入磁盘的操作，就叫做刷盘。

我们可以发现，redo log buffer 写入到 redo log file，是经过 OS buffer 中转的。其实可以通过参数innodb_flush_log_at_trx_commit进行配置，参数值含义如下：

• 0：称为延迟写，事务提交时不会将 redo log buffer 中日志写入到 OS buffer，而是每秒写入 OS buffer 并调用写入到 redo log file 中。

• 1：称为实时写，实时刷”，事务每次提交都会将 redo log buffer 中的日志写入 OS buffer 并保存到 redo log file 中。

• 2：称为实时写，延迟刷。每次事务提交写入到 OS buffer，然后是每秒将日志写入到 redo log file。

4. Redo log 的执行流程

我们来看下 Redo log 的执行流程，假设执行的 SQL 如下：

update T set a =1 where id =666

1. MySQL 客户端将请求语句update T set a =1 where id =666，发往 MySQL Server 层。

2. MySQL Server 层接收到 SQL 请求后，对其进行分析、优化、执行等处理工作，将生成的 SQL 执行计划发到 InnoDb 存储引擎层执行。

3. InnoDb 存储引擎层将 a 修改为 1 的这个操作记录到内存中。

4. 记录到内存以后会修改 redo log 的记录，会在添加一行记录，其内容是需要在哪个数据页上做什么修改。

5. 此后，将事务的状态设置为 prepare ，说明已经准备好提交事务了。

6. 等到 MySQL Server 层处理完事务以后，会将事务的状态设置为 commit，也就是提交该事务。

7. 在收到事务提交的请求以后，redo log 会把刚才写入内存中的操作记录写入到磁盘中，从而完成整个日志的记录过程。

5. redo log 为什么可以保证 crash safe 机制呢？

• 因为 redo log 每次更新操作完成后，就一定会写入的，如果写入失败，说明此次操作失败，事务也不可能提交。

• redo log 内部结构是基于页的，记录了这个页的字段值变化，只要 crash 后读取 redo log 进行重放，就可以恢复数据。

6. binlog 的概念是什么, 起到什么作用, 可以保证 crash-safe 吗?

• bin log 是归档日志，属于 MySQL Server 层的日志。可以实现主从复制和数据恢复两个作用。

• 当需要恢复数据时，可以取出某个时间范围内的 bin log 进行重放恢复。

• 但是 bin log 不可以做 crash safe，因为 crash 之前，bin log 可能没有写入完全 MySQL 就挂了。所以需要配合 redo log 才可以进行 crash safe。

7. binlog 和 redolog 的不同点有哪些?

8. 执行器和 innoDB 在执行 update 语句时候的流程是什么样的?

• 执行器在优化器选择了索引后，会调用 InnoDB 读接口，读取要更新的行到内存中

• 执行 SQL 操作后，更新到内存，然后写 redo log，写 bin log，此时即为完成。

• 后续 InnoDB 会在合适的时候把此次操作的结果写回到磁盘。

9. 如果数据库误操作, 如何执行数据恢复?

数据库在某个时候误操作，就可以找到距离误操作最近的时间节点的 bin log，重放到临时数据库里，然后选择误删的数据节点，恢复到线上数据库。

10. binlog 日志三种格式

binlog 日志有三种格式

• Statement：基于 SQL 语句的复制((statement-based replication,SBR))

• Row:基于行的复制。(row-based replication,RBR)

• Mixed:混合模式复制。(mixed-based replication,MBR)

Statement 格式

每一条会修改数据的 sql 都会记录在 binlog 中

• 优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO，提高性能。

• 缺点：由于记录的只是执行语句，为了这些语句能在备库上正确运行，还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在备库得到和在主库端执行时候相同的结果。

Row 格式

不记录 sql 语句上下文相关信息，仅保存哪条记录被修改。

• 优点：binlog 中可以不记录执行的 sql 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以 rowlevel 的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题。

• 缺点:可能会产生大量的日志内容。

Mixed 格式

实际上就是 Statement 与 Row 的结合。一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog,MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式

11. 什么是 MySQL 两阶段提交, 为什么需要两阶段提交?

其实所谓的两阶段就是把一个事务分成两个阶段来提交。

两阶段提交主要有三步曲：

1. redo log 在写入后，进入 prepare 状态

2. 执行器写入 bin log

3. 进入 commit 状态，事务可以提交。

为什么需要两阶段提交呢?

• 如果不用两阶段提交的话，可能会出现这样情况：bin log 写入之前，机器 crash 导致需要重启。重启后 redo log 继续重放 crash 之前的操作，而当 bin log 后续需要作为备份恢复时，会出现数据不一致的情况。

• 如果是 bin log commit 之前 crash，那么重启后，发现 redo log 是 prepare 状态且 bin log 完整（bin log 写入成功后，redo log 会有 bin log 的标记），就会自动 commit，让存储引擎提交事务。

• 两阶段提交就是为了保证 redo log 和 binlog 数据的安全一致性。只有在这两个日志文件逻辑上高度一致了。你才能放心的使用 redo log 帮你将数据库中的状态恢复成 crash 之前的状态，使用 binlog 实现数据备份、恢复、以及主从复制。

12. 如果不是两阶段提交, 先写 redo log 和先写 bin log 两种情况各会遇到什么问题?

• 先写 redo log，crash 后 bin log 备份恢复时少了一次更新，与当前数据不一致。

• 先写 bin log，crash 后，由于 redo log 没写入，事务无效，所以后续 bin log 备份恢复时，数据不一致。

13. binlog 刷盘机制

所有未提交的事务产生的 binlog，都会被先记录到 binlog 的缓存中。等该事务提交时，再将缓存中的数据写入 binlog 日志文件中。缓存的大小由参数binlog_chache_size控制。

binlog 什么时候刷新到磁盘呢？由参数sync_binlog控制

• 当sync_binlog为 0 时，表示 MySQL 不控制 binlog 的刷新，而是由系统自行判断何时写入磁盘。选这种策略，一旦操作系统宕机，缓存中的 binlog 就会丢失。

• sync_binlog为 N 时，每 N 个事务，才会将 binlog 写入磁盘。。

• 当sync_binlog为 1 时，则表示每次 commit，都将 binlog 写入磁盘。

来看一个比较完整的流程图吧：

14.undo log 是什么？它有什么用

• undo log 叫做回滚日志，用于记录数据被修改前的信息。

• 它跟 redo log 重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log 主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，这样发生错误时才可以回滚。

15. 说说 Redo log 的记录方式

redo log 的大小是固定。它采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。如下图（图片来源网络）：

redo log buffer(内存中)是由首尾相连的四个文件组成的，它们分别是：ib_logfile_1、ib_logfile_2、ib_logfile_3、ib_logfile_4。

• write pos 表示当前写入记录位置(写入磁盘的数据页的逻辑序列位置)

• check point 表示刷盘(写入磁盘)后对应的位置。

• write pos 到 check point 之间的部分用来记录新日志，也就是留给新记录的空间。

• check point 到 write pos 之间是待刷盘的记录，如果不刷盘会被新记录覆盖。

有了 redo log，当数据库发生宕机重启后，可通过 redo log 将未落盘的数据（check point 之后的数据）恢复，保证已经提交的事务记录不会丢失，这种能力称为 crash-safe。

来源：https://mp.weixin.qq.com/s/tmlk9p3UOnxzMwjggCtC2g