前言

对MySQL有些了解的人一定知道MySQL不是直接把数据直接写到数据表上，而是先写到日志上，在从日志信息同步到对应的数据库文件。所以MySQL中有很多关于数据存储的日志文件，当然也有很多MySQL运行时的日志，比如错误日志、查询日志、慢查询日志等等。我们这次主要想说一些关于MySQL存储数据的日志。比如二进制日志bin log和事务日志（InnoDB存储引擎）redo log这两种日志。

WAL机制

WAL：（Write-Ahead Logging 预写日志），上面我们说MySQL存储到表数据前是把数据以日志形式记录下来。这种方式有好处就是可以提高部分MySQL性能，同时在备份和还原数据记录有可以有很多日志可以帮DBA更好的完成工作（crash recovery）。但是也有不好地方，就是会产生脏数据产生锁冲突，大量日志文件也占用了IO资源。下面我们详细说下WAL机制。（不过也不详细，毕竟不是DBA）

用户如果对数据库中的数据进行了修改，必须保证日志先于数据落盘。当日志落盘后，就可以给用户返回操作成功，并不需要保证当时对数据的修改也落盘。如果数据库在日志落盘前crash，那么相应的数据修改会回滚。在日志落盘后crash，会保证相应的修改不丢失。有一点要注意，虽然日志落盘后，就可以给用户返回操作成功，但是由于落盘和返回成功包之间有一个微小的时间差，所以即使用户没有收到成功消息，修改也可能已经成功了，这个时候就需要用户在数据库恢复后，通过再次查询来确定当前的状态。

WAL提高性能，保证数据完整（crash recovery）

当日志落盘，直接返回用户操作成功。日志时顺序IO对日志操作肯定快于对表的操作，表查找数据时随机IO（不过后面也要去做表数据更新）。这样直接返回给用户，提高了MySQL并发（类似于RocketMQ削峰填谷）。后面统一批量处理日志将数据记录更新到具体的表里。同时所有对数据操作都有日志记录，数据只要落在日志就可以了。数据相对于日志，它并不决定数据的安全。

WAL也存在问题

一、大量日志刷盘问题。

由于所有对数据的修改都需要写日志，当并发量很大的时候，必然会导致日志的写入量也很大，为了性能考虑，往往需要先写到一个日志缓冲区，然后再按照一定规则刷入磁盘，此外日志缓冲区大小有限，而用户会源源不断的生产日志，数据库需要不断的把缓存区中的日志刷入磁盘，缓存区才可以复用，由此可见，这里构成了一个典型的生产者和消费者模型。现代数据库必须直面这个问题，在高并发的情况下，这一定是个性能瓶颈，也一定是个锁冲突的热点。

引入mtr，多次缓存提交（先提交私有缓存合并，提交到全局缓存，在刷盘）

当用户线程产生日志的时候，首先缓存在一个mtr，只有完成某些原子操作(例如完成索引分裂或者合并等)的时候，才把日志提交到全局的日志缓存区中。全局缓存区的大小(innodb_log_file_size)可以动态配置。当线程的事务执行完后，会按照当前的配置(innodb_flush_log_at_trx_commit)决定是否需要把日志从缓冲区刷到磁盘。（后面会介绍mtr）

二、大量数据刷盘问题。（MySQL底层的数据是按照页存储）

在用户收到操作成功的时候，用户的数据不一定已经被持久化了，很有可能修改还没有落盘，这就需要数据库有一套刷数据的机制，专业术语叫做刷脏页算法。脏页(内存中被修改的但是还没落盘的数据页)在源源不断的产生，然后要持续的刷入磁盘，这里又凑成一个生产者消费者模型，影响数据库的性能。如果在脏页没被刷入磁盘，但是数据库异常crash了，这个就需要做奔溃恢复，具体的流程是，在接受用户请求之前，从checkpoint点(这个点之前的日志对应的数据页一定已经持久化到磁盘了)开始扫描日志，然后应用日志，从而把在内存中丢失的更新找回来，最后重新刷入磁盘。这里有一个很重要的点：在数据库正常启动的期间，checkpoint怎么确定，如果checkpoint做的慢了，就会导致崩溃恢复时间过长，从而影响数据库可用性，如果做的快了，会导致刷脏压力过大，甚至数据丢失。MySQL 从 内存更新到磁盘的过程，称为刷脏页的过程（flush）

脏页数据链表，保证脏页的有序性通过oldest_modification确定刷入时间。

当把日志成功拷贝到全局日志缓冲区后，会继续把当前已经被修改过的脏页加入到一个全局的脏页链表中。这个链表有一个特性：按照最早被修改的时间排序。例如，有数据页A,B,C，数据页A早上9点被第一次修改，数据页B早上9点01分被第一次修改，数据页C早上9点02分被第一次修改，那么在这个链表上数据页A在最前，B在中间，C在最后。即使数据页A在早上9点之后又一次被修改了，他依然排在B和C之前。在数据页上，有一个字段来记录这个最早被修改的时间：oldest_modification，只不过单位不是时间，而是lsn，即从数据库初始化开始，一共写了多少个字节的日志，由于其是一个递增的值，因此可以理解为广义的时间，先写的数据，其产生的日志对应的lsn一定比后写的小。在脏页列表上的数据页，就是按照oldest_modification从小到大排序，刷脏页的时候，就从oldest_modification小的地方开始。checkpoint就是脏页列表中最小的那个oldest_modification，因为这种机制保证小于最小oldest_modification的修改都已经刷入磁盘了。这里最重要的是，脏页链表的有序性。

数据库crash导致数据丢失

假设这个有序性被打破了，如果数据库异常crash，就会导致数据丢失。例如，数据页ABC的oldest_modification分别为120，100，150，同时在脏页链表上的顺序依然为A，B，C，A在最前面，C在最后面。数据页A被刷入磁盘，然后checkpoint被更新为120，但是数据页B和C都还没被刷入磁盘，这个时候，数据库crash，重启后，从checkpoint为120开始扫描日志，然后恢复数据，我们会发现，数据页C的修改被恢复了，但是数据页B的修改丢失了。

上面扯了那么多，其实在InnoDB上就是redo log。下面我们详细说下redo log，所有的脏页数据也就存在redo log，刷脏页也就是刷redo log。

mtr（Mini-Transaction）

设计MySQL的⼤叔把对底层⻚⾯中的⼀次原⼦访问的过程称之为⼀个Mini-Transaction，简称mtr。⼀个所谓的mtr可以包含⼀组redo log，在进⾏崩溃恢复时这⼀组redo log作为⼀个不可分割的整体。⼀个事务可以包含若⼲条语句，每⼀条语句其实是由若⼲个mtr组成，每⼀个mtr⼜可以包含若⼲条redo log。

如何保证mtr的不可分割：

在该组中的最后⼀条redo log后边加上⼀条特殊类型的redo log，该类型名称为MLOG_MULTI_REC_END。所以某个需要保证原⼦性的操作产⽣的⼀系列redo log必须要以⼀个类型为MLOG_MULTI_REC_END结尾，只有当解析到类型为MLOG_MULTI_REC_END的redo log，才认为解析到了⼀组完整的redo log，才会进⾏恢复。否则的话直接放弃前边解析到的redo log。

什么样的操作是不可分割的操作：

更新Max Row ID属性时产⽣的redo log是不可分割的。

向聚簇索引对应B+树的⻚⾯中插⼊⼀条记录时产⽣的redo log是不可分割的。

向某个⼆级索引对应B+树的⻚⾯中插⼊⼀条记录时产⽣的redo log是不可分割的。

redo log

redo log基本结构

redo log占⽤的空间⾮常⼩ 存储表空间ID、⻚号、偏移量以及需要更新的值所需的存储空间是很⼩的。 redo log是顺序写⼊磁盘的 在执⾏事务的过程中，每执⾏⼀条语句，就可能产⽣若⼲ 条redo log，redo log是按照产⽣的顺序写⼊磁盘的，也就是使⽤顺序IO。

redo log的通用格式，redo log本质上只是记录了⼀下事务对数据库做了哪些修改，存放的是物理日志。物理日志（mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更）

type：redo log类型。space ID：表空间ID。pageNumber：页号。data：redo log具体内容。

redo log分成两部分，一个是内存中的日志缓冲( redo log buffer)，另一个是磁盘上的日志文件( redo logfile)。

redo log WAL的实现

MySQL 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志，再写磁盘 的技术就是 我们上面常说到的 WAL(Write-Ahead Logging) 技术。

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

因此， redo log buffer 写入 redo logfile 实际上是先写入 OS Buffer ，然后再通过系统调用 fsync() 将其刷到 redo log file中，过程如下：

MySQL 支持三种将 redo log buffer 写入 redo log file 的时机，可以通过 innodb_flush_log_at_trx_commit 参数值配置。

参数值 参数值含义
0 延迟写 提交事务时不会将redo log buffer中的日志写到 os buffer。而且每次也不都是提交时写入，而是每1s钟写入os buffer并调用fsync()刷入redo log file。也就是设置为0的时候，数据每1s落一次盘，如果MySQL在这个时候宕机会丢失1s的数据。
1 实时写 实时刷 每次事务提交都会将redo log buffer中的日志写入os buffer 并调用 fsync()刷入到 redo log file。这种方法即使系统崩溃也不会丢失数据，但每次提交都写入磁盘，IO性能比较差
2 实时写 延时刷 每次提价都仅写入到os buffer，然后每1s调用fsync()将os buffer中的日志刷入到redo log file

如图所示

在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置为如下：

  • 如果启用了二进制日志，则设置sync_binlog=1，即每提交一次事务同步写到磁盘中。
  • 总是设置innodb_flush_log_at_trx_commit=1，即每提交一次事务都写到磁盘中。

上述两项变量的设置保证了：每次提交事务都写入二进制日志和事务日志，并在提交时将它们刷新到磁盘中。

不过一般都不会用参数0一般，因为0和2的效率差不多，在没有主从同步的情况下，可以使用2。1是可以保证数据可靠性但是性能会差好多。

上面每1s刷入数据是InnoDB 有一个后台线程，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。也就是WAL中解决大量日志刷盘方法的实现。WAL中提到的就是参数配置为0时候的解决方法。不过它提供了多种选择我们可以每次提交时候在刷盘。

redo log 格式

redo log 实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此 redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。在innodb中，既有redo log 需要刷盘，还有 数据页 也需要刷盘， redo log存在的意义主要就是降低对数据页刷盘的要求。

磁盘上的redo⽇志⽂件不只⼀个，⽽是以⼀个⽇志⽂件组的形式出现的。这些⽂件以ib_logfile[数字]（数字可以是0、1、2…）的形式进⾏命名。在将redo log写⼊⽇志⽂件组时，是从ib_logfile0开始写，一直写到文件最大哦，从图中我们能看出来这个是环，也就是后面的redo log日志会覆盖前面的redo log日志。

在上图中， write pos 表示 redo log 当前记录的 LSN (逻辑序列号)位置， check point 表示 数据页更改记录 刷盘后对应 redo log 所处的 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 ，则会从 checkpoint 开始恢复。

还有一种情况，在宕机前正处于checkpoint 的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的 LSN 大于日志中的 LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

bin log

bin log：（binary log）二进制日志，binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog 是 mysql的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。逻辑日志（可以简单理解为记录的就是sql语句）与上面redo log物理日志对应。

binlog 是通过追加的方式进行写入的，可以通过max_binlog_size 参数设置每个 binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志 。

binlog 的主要使用场景有两个，分别是 主从复制 和 数据恢复 。

主从复制：在 Master 端开启 binlog ，然后将 binlog发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据一致。（也可以使用cancal来重放bin log实现数据同步，备份。比如同步到ES，同步实时中间库）

数据恢复 ：通过使用 mysqlbinlog 工具来恢复数据

bin log WAL实现

对于 InnoDB 存储引擎而言，只有在事务提交时才会记录biglog ，此时记录还在内存中，那么 biglog是什么时候刷到磁盘中的呢？

mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：

0：不去强制要求，由系统自行判断何时写入磁盘；

1：每次 commit 的时候都要将 binlog 写入磁盘；

N：每N个事务，才会将 binlog 写入磁盘。

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

bin log 格式

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

  • 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

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

启用binlog，通过配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件的 log-bin 选项

总结

redo log bin log
存放内容 物理日志，主要存放数据存放的物理信息，修改的数据页信息 逻辑日志，简单理解就是记录执行的每一条sql。
实现方式 redo log是InnDB 实现的，是存储引擎层面的。不是所有的存储引擎都支持 bin log 是Server层，所有MySQL都有，所有引擎都可以使用bin log
记录模式 循环写入的方式，从头写到尾，环的数据结构。通过LSN和checkpont实现。 bin log是追加记录的方式，当给定固定大小文件时会不停追加，不会覆盖最开始的数据。
适用场景 数据库崩溃后的恢复，保存事务相关日志。（cash-safe） bin log 主从同步，数据恢复
文件大小 redo log 固定大小。 bin log可以指定文件的大小,配置max_binlog_size

由 binlog 和 redo log 的区别可知：binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。

但只有 redo log 也不行，因为 redo log 是 InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要 binlog和 redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失