MySQL日志

一、binlog

• 用于复制，在主从复制中，从库利用主库上的 binlog 进行重播，实现主从同步。
• 用于数据库的基于时间点的数据恢复。

基本概念

binlog用于记录数据库执行的写入性操作信息，以二进制的形式保存在磁盘中。binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。
逻辑日志，可以简单认为就是执行过的事务中的 SQL 语句，但又不完全是 SQL 语句这么简单，而是包括了执行的 SQL 语句（增删改）反向的信息。
binlog由 Server 层进行记录，使用任何存储引擎的 MySQL 数据库都会记录binlog日志。

产生时间

事务提交的时候，一次性将事务中的 SQL 语句（一个事务可能对应多个 SQL 语句）按照一定的格式记录到binlog中。

释放时间

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

刷盘时机

对于InnoDB存储引擎而言，只有在事务提交时才会记录binlog，此时记录还在内存中，MySQL 通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N：

• 0：不去强制要求，由系统自行判断何时写入磁盘；
• 1：每次commit的时候都要将binlog写入磁盘；
• N：每N个事务，才会将binlog写入磁盘。

从上面可以看出，sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

日志格式

binlog日志有三种格式，分别为STATMENT、ROW和MIXED。

在 MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format指定。

• STATMENT 基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 从而提高了性能；缺点：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。
• ROW 基于行的复制(row-based replication, RBR)，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨
• MIXED 基于STATMENT和ROW两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

二、redo log

• 确保事务的持久性。
• 防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 MySQL 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性。

基本概念

物理日志，记录的是物理数据页面的修改的信息。
MySQL 每执行一条 DML 语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是 MySQL 里经常说到的WAL(Write-Ahead Logging) 技术。
在计算机操作系统中，用户空间(user space)下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间(kernel space)缓冲区(OS Buffer)。因此，redo log buffer写入redo log file实际上是先写入OS Buffer，然后再通过系统调用fsync()将其刷到redo log file中，过程如下：

MySQL 支持三种将redo log buffer写入redo log file的时机，可以通过innodb_flush_log_at_trx_commit参数配置，各参数值含义如下：

因此重做日志的写盘，并不一定是随着事务的提交才写入重做日志文件的，而是随着事务的开始，逐步开始的。

产生时间

事务开始之后就产生redo log，redo log的写盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log file中。

释放时间

当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

记录形式

前面说过，redo log实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。如下图：

在innodb中，既有**redo log**需要刷盘，还有数据页也需要刷盘，**redo log**存在的意义主要就是降低对数据页刷盘的要求。在上图中，write pos表示当前日志的LSN(逻辑序列号)位置，check point表示数据页更改记录要刷盘日志的LSN位置。write pos到check point之间的部分是redo log空着的部分，用于记录新的日志；check point到write pos之间是redo log待写盘的数据页更改记录。当write pos追上check point时，会先推动check point向前移动，空出位置再记录新的日志。
启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如binlog)要快很多。重启innodb时，首先会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从check point开始恢复。还有一种情况，在宕机前正处于check point的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的LSN大于日志中的LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

redo log与binlog区别

由binlog和redo log的区别可知：binlog日志只用于归档，只依靠binlog是没有crash-safe能力的。但只有redo log也不行，因为redo log是InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要binlog和redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

三、undo log

• 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC）

数据库事务四大特性中有一个是原子性，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。实际上，原子性底层就是通过undo log（逻辑日志）实现的。undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。同时，undo log也是MVCC(多版本并发控制)实现的关键。

产生时间

事务开始之前，将当前事务版本生成undo log，undo也会产生 redo来保证undo log的可靠性。

释放时间

当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由 purge 线程判断是否由其他事务在使用undo log段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

对应的物理文件：MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。
MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数，如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

关于 MySQL5.7 之后的独立undo表空间配置参数如下

• innodb_undo_directory = /data/undospace/ — undo独立表空间的存放目录
• innodb_undo_logs = 128 — 回滚段为128KB
• innodb_undo_tablespaces = 4 — 指定有4个undo log文件

如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为与 MySQL 的数据目录下面，其属性由参数innodb_data_file_path配置。

mysql> show variables like '%innodb_data_file_path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set, 1 warning (0.01 sec)

其他

• undo是在事务开始之前保存的被修改数据的一个版本，产生undo日志的时候，同样会伴随类似于保护事务持久化机制的redo log的产生。
• 默认情况下undo文件是保持在共享表空间的，也即ibdatafile文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的undo信息，全部保存在共享表空间中的。
• 因此共享表空间可能会变的很大，默认情况下，也就是undo日志使用共享表空间的时候，被“撑大”的共享表空间是不会也不能自动收缩的。
• 因此， MySQL5.7 之后的“独立undo表空间”的配置就显得很有必要了。

  四、两阶段提交

  
  前面的这个阶段大家应该都能看懂，重点说一下最后三个阶段：

1. 引擎将新数据更新到内存中，将操作记录到redo log中，此时redo log处于prepare状态，然后告知执行器执行完成了，可以提交事务。
2. 执行器生成操作的binlog，并把binlog写入磁盘。
3. 引擎将写入的redo log改为提交状态，更新完成。

为什么要把**redo log**的写入拆成2个步骤？即**prepare**和**commit**，两阶段提交。

因为不管先写redo log还是binlog，奔溃发生后，最终都有可能会造成原库和用日志恢复出来的库不一致。而两阶段提交可以避免这个问题。在恢复数据时，redo log 状态为commit则说明binlog也成功，直接恢复数据；如果redo log是prepare，则需要查询对应的binlog事务是否成功，决定是回滚还是执行。

redo log和binlog具有关联性，在恢复数据时，redo log用于恢复主机故障时的未更新的物理数据，binlog用于备份操作。每个阶段的log操作都是记录在磁盘的，在恢复数据时，redo log 状态为commit则说明binlog也成功，直接恢复数据；如果redo log是prepare，则需要查询对应的binlog事务是否成功，决定是回滚还是执行。

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

再看一个场景，redo log设置commit阶段发生异常，那会不会回滚事务呢？

并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以 MySQL 认为是完整的，就会提交事务恢复数据。

五、总结

Buffer Pool是MySQL进程管理的一块内存空间，有减少磁盘IO次数的作用。

参考文档

1. MySQL中的重做日志，回滚日志，以及二进制日志的区别及各自作用
2. 动画图解MySQL为什么需要binlog、redo log和undo log
3. MySQL七种日志总结