MySQL：日志

MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。本文将讲解其中比较重要的二进制日志binlog（归档日志）、事务日志redo log（重做日志）和undo log（回滚日志）。

binlog

MySQL二制进日志（binlog），也叫做变更日志（update log）。
二进制日志中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息。

二进制日志主要用于数据库恢复和主从复制，以及审计操作，它包含了所有能够重现数据库状态变更所必需的信息。。如果 MySQL 数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。

内容

二进制日志（binlog）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息：
主要包含以下内容：

• 逻辑操作：对数据库执行的原始SQL语句或其等价物。具体内容与格式有关：
  • Statement格式：记录执行的SQL语句本身，例如INSERT INTO table VALUES(…)、UPDATE table SET column=… WHERE condition等。
  • Row格式：记录每个受影响行的实际变化，而不是整个SQL语句。它会详细说明哪个表的哪一行如何被修改。
  • Mixed格式：混合使用Statement和Row格式，MySQL根据SQL语句的特点自动选择最佳方式。
• 事务边界：
  • Binlog以事务为单位记录变更，确保了事务的原子性和一致性。每个事务的所有操作作为一个整体被记录，且事务开始和结束都有相应的标志。
• GTID（Global Transaction Identifier）：在支持GTID的MySQL版本中，binlog还会包含全局事务ID，用于主从复制时精确地跟踪和同步事务。
• DDL操作：对于数据定义语言（如CREATE TABLE、ALTER TABLE、DROP TABLE等）也会记录在binlog中。
• 其他元数据：事件头信息，如事务ID、时间戳、服务器ID等，用于数据恢复和复制过程中的校验和同步。

写入机制

事务执行过程中，会先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。
系统会给每个线程分配一个块内存作为binlog cache，以确保一个事务的binlog的一次性写入的。

示意图：

注意，如果write操作不会将binlog文件写入磁盘（仅仅是存入缓存），fsync才是将binlog文件写入磁盘的真正动作。如果机器宕机，Buffer Pool里面的 binlog 就会丢失。

sync_binlog参数可用于控制write和fsync的时机。

• 0：sync_binlog为0时，每次提交事务都进行write，执行fsync时机由操作系统决定。
• 1：每次提交事务都会执行fsync。
• N(N>1)：表示每次提交事务都write，但累积N个事务后才fsync。

作用

1. 基于时间点的恢复：在备份文件恢复的基础上，通过binlog日志，可以将数据库恢复到某一时间点；
2. 主从复制：在主从复制模式下，必须在主服务器上开启binlog日志；
3. 操作审计：对所有更改数据的操作进行审计。

操作审计：

• 数据追踪与回溯：通过分析binlog，可以追溯数据库中的任何数据变更历史，这对于数据安全性、合规性要求高的场景尤其重要，比如金融、医疗等行业，需要记录详细的交易历史以供审计或法律调查。
• 审计复核：binlog可用于数据完整性验证，通过重新播放binlog中的事件，可以检查数据库是否按照预期进行了操作，从而帮助发现潜在的误操作或恶意篡改行为。
• 安全审计：安全审计时，binlog可以帮助识别未经授权的访问尝试、非法数据修改等安全事件，因为它记录了所有的数据修改语句。

注意：MySQL本身的审计功能并非直接依赖binlog，而是通过专门的审计插件（如MySQL Enterprise Audit Plugin）或其他审计工具来实现更全面和规范的审计需求，这些工具可以提供更多定制化的审计过滤、报告生成等功能。不过，binlog确实是进行事后审计和数据一致性检查的重要数据源之一。

undo log

undo log是一种用于撤销回退的日志。
在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。

作用

1. 提供回滚操作：在进行数据更新操作的时候，会记录undo log。如果因为某些原因导致事务回滚，那么这个时候MySQL就要执行回滚（rollback）操作，利用undo log将数据恢复到事务开始之前的状态。
   1. 比如我们执行下面一条update语句：update user set name = "李四" where id = 1; ---修改之前name=张三
   2. 此时undo log会记录update之前的状态（id=1,name=‘张三’）；如果这个修改出现异常，就可以使用undo log日志中的操作来实现回滚操作，如：update user set name = "张三" where id = 1;，以保证事务的一致性。
2. 实现多版本并发控制（MVCC）。

MVCC，即多版本控制。在MySQL数据库InnoDB存储引擎中，用undo Log来实现多版本并发控制(MVCC)。当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据版本是怎样的，从而让用户能够快照读读取到当前事务操作之前的数据。

快照读：SQL读取的数据是快照版本，也就是历史版本，不用加锁，普通的SELECT就是快照读。
当前读：SQL读取的数据是最新版本。通过锁机制来保证读取的数据无法被其他事务进行修改。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE都是当前读。

存储机制

undo log的存储由InnoDB存储引擎实现，其采用分段(segment)的方式进行存储的。
rollback segment称为回滚段，每个回滚段中有1024个undo log segment。在MySQL5.5之后，可以支持128个rollback segment，每一个回滚段，内部由1024个undo segment 组成，即总共可以记录128 * 1024个undo操作。

存储结构示意图：

回滚指针第一次如果是insert语句的话，回滚指针为NULL，第二次update之后的undo log的回滚指针就会指向刚刚那一条undo log日志，依次类推，就会形成一条undo log的回滚链，方便找到该条记录的历史版本。

多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表，这个链表就称为版本链。

工作原理

在更新数据之前，MySQL会提前生成undo log日志，当事务提交的时候，并不会立即删除undo log，因为后面可能需要进行回滚操作。
要执行回滚（rollback）操作时，从缓存中读取数据。undo log日志的删除是通过通过后台purge线程进行回收处理的。

示意图：

举例：

1. 事务A执行update操作，此时事务还没提交，会将数据进行备份到对应的undo buffer，然后由undo buffer持久化到磁盘中的undo log文件中，此时undo log保存了未提交之前的操作日志，接着将操作的数据，也就是Teacher表的数据持久保存到InnoDB的数据文件IBD。
2. 此时事务B进行查询操作，直接从undo buffer缓存中进行读取，这时事务A还没提交事务，如果要回滚（rollback）事务，就直接从undo buffer缓存读取。

流程

使用undo log实现原子性和持久化的事务的流程：

1. 事务开始
2. 第一次更改：
   1. 将name=A记录至undo log
   2. 更改Buffer Pool中的name=B（原name=A）
3. 第二次更改：
   1. 将name=B记录至undo log
   2. 更改Buffer Pool中的name=C
4. 持久化undo log（Undo Log在事务执行过程中就已经逐步地持久化到了磁盘中）
5. 提交事务，此时事务才真正完成，同时持久化Buffer Pool中的改动至磁盘。对于MySQL复制环境，在提交事务后，Binlog也会被持久化到磁盘。

注意：在更新数据前会提前记录undo log。

执行该流程的优点：

1. 成功情况：事务提交将与数据一起写入磁盘。
2. 异常情况：
   1. 持久化undo log后发生异常：undo log是完整的，可以用来回滚。
   2. 持久化undo log前或时发生异常：磁盘上的数据保持在事务开始前。

感觉思路有点像磁盘的RAID1级或缓存的差异失效时间，提供了两个备份，因此任何时间点发生崩溃都可以保证数据不丢失。

redo log

redo log（重做日志），也叫重放日志。和undo log回滚日志一样，都是在数据库发生意外时用来进行数据恢复的，之前已经介绍了undo log，我们都知道undo log记录的是数据更新前的样子，主要保证事务的原子性；而redo log则记录的是事务执行过程中的修改情况，redo log主要保证事务的持久性。

Redo Log 并不是直接用于保存事务提交后的“脏页”本身，而是用于保存那些在事务执行过程中对数据页所做的修改操作的日志记录。当一个事务在InnoDB存储引擎中执行时，它首先修改内存中的数据页（这些数据页位于Buffer Pool中），这些被修改过的数据页被称为“脏页”。

Redo Log 记录了足以重新执行（redo）这些修改操作所需的信息，即使在系统发生故障（如宕机）的情况下，也能确保当数据库重启时，能够通过重做(redo)这些日志记录来恢复未持久化到磁盘的事务影响。
在事务提交时，Redo Log 会被刷新到磁盘上的 redo log 文件中，这个过程通常早于对应数据页被刷回磁盘（即脏页flush到磁盘数据文件）。这种机制使得MySQL能够满足ACID中的持久性要求，即一旦事务提交，其对数据库的影响即使在系统崩溃后也能保持不变。

作用

1. 保证事务的持久性：如果buffer pool缓冲池中的脏数据还没有进行刷盘的时候，此时数据库发生崩溃，重启服务后，我们可以通过redo log日志找到需要重放到磁盘文件的那些数据记录。
2. 提高事务提交的速度：buffer pool缓冲池中的数据直接刷新到磁盘，是一个随机IO，效率较差，而把buffer pool中的数据记录到redo log，是一个顺序IO，可以提高事务提交的速度。可将redo log持久化作为事务完成的标志（崩溃了也能通过redo log恢复），所以速度更快。

工作原理

redo log由两部分组成：

• redo log buffer：重做日志缓存，存在于内存中，容易发生丢失。
• redo log file：重做日志文件，存在于磁盘中，不容易发生丢失。

为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作（即fsync()系统调用）。因为MySQL是工作在用户空间的，log buffer处于用户空间的内存中。要写入到磁盘上的log file中，中间还要经过操作系统内核空间的os buffer，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中。

redo log的工作原理示意图：（包括binlog、undo log、redo log）

流程

之前的undo log中的事务执行流程并不完整，现在提供补充redo log后的完整流程：
数据需要在事务提交前写到磁盘，只要事务成功提交，数据必然已经持久化到磁盘。

1. 事务开始
2. 每次数据更改步骤：
   1. 对于第一次更改（如将name从A改为B）：
      1. 创建一个Undo Log条目，记录原始值A以及变更前的数据状态。
      2. 更新内存中的Buffer Pool（即Page Cache），将name字段改为B。
   2. 对于第二次更改（如将name从B改为C）：
      1. 再创建一个新的Undo Log条目，记录原始值B及变更前的状态。
      2. 更新内存中的Buffer Pool，将name字段改为C。
3. Undo Log在事务执行过程中按照checkpoint机制或者达到一定条件时会被刷入磁盘，以保证事务的持久性。
4. 在事务准备提交阶段，Redo Log会被先于事务提交持久化到磁盘，通常通过fsync()系统调用确保其内容已经稳定存储。
5. 提交事务，此时事务才真正完成，同时持久化Buffer Pool中的改动至磁盘（通过Redo Log恢复）。对于MySQL复制环境，在提交事务后，Binlog也会被持久化到磁盘。

异常情况说明：

• 成功情况：一旦事务成功提交，意味着Redo Log已持久化，通过Redo Log可以重播事务的所有更改，即使系统崩溃重启也能恢复数据到最新状态。只要事务成功提交，数据必然已经持久化到磁盘。
• 异常情况：
  • 如果在持久化Undo Log之后发生异常，由于Undo Log和Redo Log都已经持久化，可以根据Redo Log进行前滚恢复，若需要回滚事务，则可以使用Undo Log来还原数据。
  • 如果在持久化Undo Log之前或过程中发生异常，由于InnoDB遵循“Write-Ahead Logging”原则（即修改数据前先写日志），因此磁盘上的数据仍然是事务开始前的状态，不会出现数据不一致的情况。在系统恢复时，未完成的事务可以通过检查Undo Log来决定是否需要回滚。

日志一致

由于binlog和redo log的写入并不是一个原子性操作，所以可能发生写完redo log日志后，binlog日志写期间发生了异常的情况。
对此，InnoDB使用了两阶段提交的方案。

将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。示意图：

使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应的binlog日志，就会回滚该事务。