日志是mysql数据库的重要组成部分，记录着数据库运行期间各种状态信息。mysql日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为研发人员，我们重点需要关注的是二进制日志(binlog)和事务日志(包括redo log和undo log)，本文接下来会详细介绍这三种日志。

一、binlog日志

binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。

逻辑日志：可以简单理解为记录的就是sql语句。
物理日志：因为mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

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

1.1、binlog使用场景

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

主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
数据恢复：通过使用mysqlbinlog工具来恢复数据。

1.2 binlog 的相关配置

1.2.1 开启binlog

配置log_bin=ON

1.2.2 binlog 单个文件大小

max_binlog_size 可以设置每个 binlog 文件的大小，当binglog 文件达到指定大小后会发生滚动，之后日志记录到新文件上。

1.2.3 binlog 名字

log_bin_basename 设置生成的binlog 文件的名字。

1.2.4 binlog 格式binlog_format

statement 方式记录的是的 sql 语句

优点：不需要记录每一行的变化，减少 binlog 日志量，节约 IO，提高性能。支持主从版本不一致，从服务器的版本可以比主服务器版本高。
缺点：再某些情况下会导致主从数据不一致，比如执行 sysdate()、sleep() 函数等【sysdate() 函数为取系统时间。 sleep() 函数为让语句执行多少秒】。

row 方式记录的是行内容，记录更新前和更新后的行内容

优点：不会出现某些特定情况下的存储过程或 function 或 trigger 的调用和触发无法被正确复制的问题。
缺点：会产生大量的日志，尤其时 alter table 的时候由于表结构修改了，每条记录都回发生变化，此时该表的每一条数据都会记录到日志中，相当于表重建了，会让日志暴涨。

mixed 基于 statement 和 row 两种模式的混合复制

一般复制使用 statement 保存binlog，对于 statement 模式无法复制的操作，使用row 模式保存binlog。

1.2.5 binlog 的刷新机制使用sync_binlog配置

0：存储引擎不进行binlog刷新到磁盘，存储引擎将binlog写入文件系统缓存，刷新到磁盘的时机由文件系统控制。安全级别中等，性能中等（如果数据库服务挂了，这个日志不会丢失，如果系统宕机了，可能会丢失部分数据）。
1：每提交一次事务，存储引擎调用文件系统的 sync操作进行一次缓存的刷新，这种方法最安全，但性能比较低。
n：当提交的日志组 =n时，存储引擎调用文件系统的sync操作进行一次缓存刷新。该值越大性能越好，但同时安全性越低（如果数据库挂了，可能丢失 n个日志）。

1.2.6 binlog 存储路径

默认 dataDir 参数配置【就是数据目录】。

1.2.7 binlog 保留天数

expire_logs_days 参数配置。

1.3 binlog生成

1.3.1 binlog的要求

只有完整的事件或事务能被记录到binlog 中，并且可以回读
写入二进制日志语句中的密码由服务器重写，不会以纯文本的形式出现
二进制日志文件和中继日志文件要支持加密，以保护文件中潜在的敏感数据不被外部攻击者滥用。

1.3.2 binlog 写入的时机

a、事务表

在执行语句或事务完成后，但在锁释放前或事务提交前日志要写入完成。
以innoDB 为例，在事务未提交的期间里，事务相关的 update、insert、delete 操作将全部被缓存，直到server 层接收到 commit 操作。此时mysql 会在 commit 执行之前将整个事务写到binlog 中。

【拓展：binlog 写入时差造成的问题】

上面说，binlog 是在事务执行完成后，commit 执行之前将事务写入binlog 文件中，如果在binlog 写入完成但是 InnoDB 事务还未执行 commit 的这个时间差中系统崩溃了。此时重启数据库，该事务将被回滚(因为在 innodb 中还没有commit)，但是binlog 却已经有这条记录了。
此类问题，在 mysql8.0版本中已经解决。系统崩溃重启时，在执行事务回滚后，mysql 服务器会扫描最新的二进制日志文件以收集事务的 xid 值，并计算二进制文件中的最后一个有效位置将日志截断到此，这就确保了二进制日志反映了 InnoDB 表的确切数据。如果innodb 中还没有commit，但是binlog 却已经有这条记录了，则说明该sql已经执行到最后的commit，可以重放redo log，完成事务，保持事务一致性。

b、非事务表

执行完成后立即写入 binlog，InnoDB为例，结合redo log 说明binlog 的写入过程
binlog 必须与redo log，保持一致，在 redo log 写入成功后即写入binlog，此时redo log为prepare 状态，若事务回滚，redo log 没有真正的刷入到磁盘中，此时binlog 擦除此次事务记录。

写入方式：当处理事务的线程启动时，数据库会以 binlog_cache_size 设置的大小建立一个缓冲区，如果执行语句所需的空间大于此值，则线程会打开一个临时文件用来存储事务，线程结束删除临时文件。binlog 通过追加的方式写入到文件尾部。

ps：系统max_binlog_cache_size 变量用于现在用于缓存多语句事务的总体大小，如果一个事务大于这么多个字节，则会失败回滚（默认 4G）

二、redo log

2.1、为什么需要redo log

我们都知道，事务的四大特性里面有一个是持久性，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。那么mysql是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

因为Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

因此mysql设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。

Redo log 使用了以下技术:

2.1.1 WAL(Write-Ahead Logging)

WAL技术是指先把操作命令写入日志文件并刷盘，这一块属于顺序io。而真正的数据操作因为要修改的数据比较分散属于随机io 都是先放在内存缓存的。WAL保证了数据持久化的同时，减少了写io 。并不是指先把日志写到内存，再把日志写到磁盘！这一点要理解清楚。

2.1.2 crash safe

crash safe是靠redo log来保障的，并不靠binlog。只不过为了奔溃恢复后的数据与binlog 一致，某些有commit缺失的redo log 在恢复时要根据binlog 是否写入来决定是否恢复。

2.2 redo log buffer刷盘时机

在计算机操作系统中，用户空间(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存储引擎有一个后台线程，每隔1秒，就会把redo log buffer中的内容写到文件系统缓存（page cache），然后调用fsync刷盘。

page 和log buffer先刷入os buffer，然后由计算机系统刷盘到file文件中，如下：

下面是不同刷盘策略的流程图：

2.2.1 后台线程会主动刷盘

redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候，一个没有提交事务的redo log记录，也可能会刷盘。

2.2.2 innodb_flush_log_at_trx_commit=0

2.2.3 innodb_flush_log_at_trx_commit=1

2.2.4 innodb_flush_log_at_trx_commit=2

innodb_flush_log_at_trx_commit参数总结如下：

innodb_flush_log_at_trx_commit各参数流程如下：

2.3 redo log记录形式

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

同时我们很容易得知，在innodb中，既有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，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

三、redo log，binlog，undo log对比

3.1 redo log

redo log为写前日志，主要是为了保证事务的原子性、一致性和持久性，并提升数据库的写入性能，保证数据库故障恢复。

为什么这么说呢？首先数据库数据按页存储，一页数据 16kb，所以就算是改了一页当中的一丢丢数据也得整页存储，而且数据库数据刷入磁盘是随机IO，如果实时存入，必然成为性能瓶颈。所以mysql 使用写前日志来提升性能。

写前日志如何提升性能呢？redo log 分为 redo log buffer 和 redo log file。mysql 先将数据的变化写入redo log buffer（当然写入的不是数据库一整页的数据，redo log 是逻辑日志与物理日志的中和体，叫做 physiological，它记录的内容是数据库哪一页改了啥东西），redo log buffer 写入后，后续会有一个线程批量将数据刷入磁盘。redo log具有幂等性，所以多次操作得到同一结果的行为在日志中只记录一次。而二进制日志不具有幂等性，多次操作会全部记录下来，在恢复的时候会多次执行二进制日志中的记录，速度就慢得多。

那么这边就引出另外一个问题：如果数据库或系统崩溃，内存中的东西还没来得及刷入磁盘就丢失了，这不是违背了事务的原子性，一致性和持久性？

于是乎，有了 redo log file，这是redo log 持久化到磁盘的文件，一个事务只有对应的 redo log 持久化到磁盘才会给客户返回写入成功(有多个策略)，redo log file 是一个逻辑上的环状结构，有固定大小，且顺序写入，所以它的性能比数据库数据刷盘好很多。那么数据既然写入磁盘，当数据库故障的时候，就可以通过 redo log file 判断哪些事务已经提交，但还没有刷盘，哪些事务还没提交需要回滚，以此保证事务的一致性、原子性和持久性

3.2 undo log

undo log和redo logo都是InnoDB的功能，都是事务日志。

undo log 在数据库故障恢复方面：首先redo log 的大小有限，所以当系统收到一个大事务的时候，可能会出现一部分数据已经刷盘，一部分数据在redo log 中，但是事务还没有完成的时候数据库崩溃了，之后数据是要回滚的。对redo log 中未刷盘的数据直接丢弃即可，但是已经刷盘的数据就需要使用 undolog 中记录的历史数据来回滚事务，以保证事务的原子性和一致性。

undo log 主要是为了保证数据库故障恢复和给数据库的 MVCC 机制提供支持。undo log 亦日志，亦数据，它记录了每个事务执行之前的数据版本，所以它是可提供查询读取的，undo log 跟数据库数据一样按页存储，它也有自己的redo log 机制。

undo log 给MVCC 提供支持，简单说就是事务的执行是有顺序的，undo log 维护了数据因事务而产生更改的链状结构，每个查询事务进来的时候，会获取一份当前活跃的写事务ID，称为 ReadView。通过 RollPtr 和 readView，就可以知道哪些事务修改的数据对当前事务可见，哪些不可见，以保证数据的一致性。

undo log用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

3.3 binlog

binlog 为二进制日志，主要作用是主备流复制、数据增量备份、误操作数据恢复。binlog 是数据库server 层支持的，独立于引擎之外，也就是说数据库的任何引擎都是支持binlog 的，redolog和 undolog 是 InnoDB 特有的。

redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

二进制日志在提交的时候一次性写入，一次提交对应一条记录，而redo log中是记录的物理页的修改，redo log文件中同一个事务可能多次记录，最后一个提交的事务记录会覆盖所有未提交的事务记录。

四、Logical logging和physical log介绍

目前主要有两种Logical logging 和 Physical logging。

Logical logging 像Binlog 这种, 记录的是操作, 跟物理格式无关, 所以通过binlog 可以对接不同的存储引擎。

记法1。类似Binlog的statement格式，记原始的SQL语句，insert/delete/update。

记法2。类似Binlog的RAW格式，记录每张表的每条记录的修改前的值、修改后的值，类似（表，行，修改前的值，修改后的值）。

Physical logging 是纯物理格式, byte for byte 的记录数据的改动, 比如 [start, end, 'xxxxx'] 这样的格式内容改动。

记法3。记录修改的每个Page的字节数据。由于每个Page有16KB，记录这16KB里哪些部分被修改了。一个Page如果被修改了多个地方，就会有多条物理日志，如下所示：

a、（Page ID1，offset1，len1，改之前的值1，改之后的值1）

b、（Page ID2，offset2，len2，改之前的值2，改之后的值2）

前两种记法都是逻辑记法；第三种是物理记法。

4.1 Physical logging

Physical logging的优点是高效率, 并且可以直接修改物理格式, 任何操作都不需要重新遍历btree 到指定page.如果redo log是纯physical log的话, 那么就可以省去double write buffer 的开销, 保证每一次修改都是在4kb以内(由操作系统保证4kb以内的原子操作), 那么就不存在应用redo 到不新不旧的page 上的问题, 就不需要double write buffer。

但是缺点也很明显, 记录的内容非常冗余, 比如一次delete 操作, logical logging 只需要记录MLOG_COMP_REC_DELETE offset 就可以, 实际执行的过程中会修改prev record->next_record, next_record->prev_record, checksum, PAGE_DIR_SLOT_MIN_N_OWNED, 可能还需要更新dir slot 信息等等. 如果改成physical logging 那么这些信息涉及到的内容在page 不同位置, 那么需要记录的日志就非常多了。

另外在page reorgnize 或者 SMO 场景需要记录大量的无用日志, 比如当一个page 内部有过大量的删除, 有碎片需要整理的时候, 因为需要重新组织page结构, 如果physical logging 那么就需要一个一个record 重新insert 到当前page, offset 需要重新记录, 而logical logging 就只需要记录MLOG_PAGE_REORGANIZE 就可以了. 对比一下16kb的page只需要记录几个字节, 而physical logging 需要写差不多16kb 的内容了。

4.2 Logical logging

Logical logging的优点是记录非常高效, 如上面说的delete 操作, 只需要记录几个字节, 在SMO, page reorgnize 等场景更加明显。

但是最大的缺点也很明显, 因为记录的是record_id, 那么所有改动就需要重新遍历btree, 因为都需要对btree 进行修改, 那么就得走加index lock, 串行修改的逻辑。而物理日志因为page 之前完全没有依赖, 那么就可以并行回放, 这样crash recovery 的效率最高的.

五、 Physiological Logging

5.1 Redo log 为什么采用Physiological Logging

Redo log 采用Physiological Logging作为数据记录格式，就是先以Page为单位记录日志，每个Page里面再采取逻辑记法（记录Page里面的哪一行被修改了）。eg：MLOG_REC_UPDATE_IN_PLACE 类型的 REDO 记录了 Page 中的一个 Record 的修改，如下所示：

PageID：指定要操作的 Page 页

Record Offset：记录 Record 在 Page 内的偏移量

field 数组：记录需要修改的 field 及其修改后的 value

(Page ID，Record Offset, (Filed 1, Value 1) ... (Filed i, Value i) ....)

知道了Redo Log的整体结构，下面进一步来看每个Log Block里面Log的存储格式。这个问题很关键，是数据库事务实现的一个核心点。

Redo Log采用了逻辑和物理的综合体，要搞清楚为什么Redo Log采用逻辑和物理的综合体Physiological Logging，就得知道逻辑日志和物理日志的对应关系：

一条逻辑日志可能产生多个Page的物理日志。比如往某个表中插入一条记录，逻辑上是一条日志，但物理上可能会操作两个以上的Page？为什么呢，因为一个表可能有多个索引，每个索引都是一颗B+树，插入一条记录，同时更新多个索引，自然可能修改多个Page。如果Redo Log采用逻辑日志的记法，一条记录牵涉的多个Page写到一半系统宕机了，要恢复的时候很难知道到底哪个Page写成功了，哪个失败了。

即使1条逻辑日志只对应一个Page，也可能要修改这个Page的多个地方。因为一个Page里面的记录是用链表串联的，所以如果在中间插入一条记录，不仅要插入数据，还要修改记录前后的链表指针。对应到Page就是多个位置要修改，会产生多条物理日志。

所以纯粹的逻辑日志宕机后不好恢复；物理日志又太大，一条逻辑日志就可能对应多条物理日志。Physiological Logging综合了两种记法的优点，先以Page为单位记录日志，在每个Page里面再采用逻辑记法，兼得数据量小、基于Page幂等二者优势。

5.2 Redo Log日志物理结构和逻辑结构关联

从逻辑上来讲，日志就是一个无限延长的字节流，从数据库安装好并启动的时间点开始，日志便源源不断地追加，永无结束。

但从物理上来讲，日志不可能是一个永不结束的字节流，日志的物理结构和逻辑结构，有两个非常显著的差异点：

a、磁盘的读取和写入都不是按一个个字节来处理的，磁盘是“块”设备，为了保证磁盘的I/O效率，都是整块地读取和写入。对于Redo Log来说，就是Redo Log Block，每个Redo Log Block是512字节。为什么是512字节呢？因为早期的磁盘，一个扇区（最细粒度的磁盘存储单位）就是存储512字节数据。

b、日志文件不可能无限制膨胀，过了一定时期，之前的历史日志就不需要了，通俗地讲叫“归档”，专业术语是Checkpoint。所以Redo Log其实是一个固定大小的文件，循环使用，写到尾部之后，回到头部覆写（实际Redo Log是一组文件，但这里就当成一个大文件，不影响对原理的理解）。之所以能覆写，因为一旦Page 数据刷到磁盘上，日志数据就没有存在的必要了。

前面介绍过，LSN（Log Sequence Number）是逻辑上日志按照时间顺序从小到大的编号。在InnoDB中，LSN是一个64位的整数，取的是从数据库安装启动开始，到当前所写入的总的日志字节数。实际上LSN没有从0开始，而是从8192开始，这个是InnoDB源代码里面的一个常量LOG_START_LSN。因为事务有大有小，每个事务产生的日志数据量是不一样的，所以日志是变长记录，因此LSN是单调递增的，但肯定不是呈单调连续递增。

5.2.1将逻辑 redo 放到物理 redo 中

由于 block 空间固定 512字节，而逻辑 redo 的大小是不固定的，因此可能一个 Block 有多个 redo，也可能一个 redo 被拆分到多个 block 中。

如下图：棕色为 Block Header，红色为一个 block 的结尾。下图可以看到有6个redo 保存在了两个连续的 block中，Log Black1 和 Log Black2。其中 1、2、3、4保存在 Log Black1 中，但是 Log Black1 空间不够大，4 保存不下了，所以4 还有一部分保存到了 Log Black2 中。

5.2.2 redo 放到文件空间中

a、logical redo 真正需要使用到的数据，用sn 索引数据位置。

b、将 logical redo 按照固定大小的 Block 组织，并添加 Block 的首尾信息形成物理 redo，以 lsn 索引。

c、将 Physical redo 的 Block 放到循环使用的文件空间中的某一个位置，文件中使用 offset 索引。

LSN 用来标识一个 Redo 的位置，但是最终 redo 的读写还是需要转换成对文件的读写IO，这时候需要的是文件空间的 offset，其换算方法如下real_offset=log.current_file_real_offset + (lsn - log.current_file_lsn)

内存中存有当前文件开头的 offset（current_file_real_offset）以及对应的前文件开始的 LSN【提示：Log File Header Block 中的 Start Lsn】，通过 current_file_real_offset 加上需要寻找的redo 的 Lsn 减去文件开始时的 Lsn，可以算出文件的 offset 位置。【注意：这里的offset是相当于整个redo文件空间而言的，由于InnoDB中读写文件的space层实现支持多个文件，因此，可以将首尾相连的多个REDO文件看成一个大文件，那么这里的offset就是这个大文件中的偏移】

物理上面，一个固定的文件大小，每 512 个字节一个 Block，循环使用。显然，很容易通过LSN换算出所属的Block。反过来，给定Redo Log，也很容易算出第一条日志在什么位置。

5.3 redo log如何保证一致性

5.3.1 基于正确的page 状态重放 redo log

由于在一个 Page 内，redo log 是以逻辑的方式记录前后两次的修改，因此重放 redo log 必须基于正确的 page 状态。我们知道 innodb 的 page 大小是 16KB，大于文件系统保证原子性的 4KB 大小，因此可能出现 Page 中的内容一半成功，一半失败的情况。为了保证 Page 状态正确，InnoDB 通过 Double Write Buffer的方式来通过写两次的方式保证有一个正确的page 状态。

5.3.2 保证 redo log 重放幂等

Double Write Buffer 可以得到一个正确的 Page 状态，但是我们还需要知道这个状态对应 redo log 上的哪个记录，以免对page 重复执行 redo log。因此 InnoDB 会给每个 redo log 记录一个全局唯一的递增标识 LSN(Log Sequence Number)。Page 修改的时候，会将对应的 redo log 上记录的 LSN 记录到 page 的 FIL_PAGE_LSN字段中。这样恢复重放 redo log 的时候，就可以用来判断跳过已经应用的 redo log。

MySQL必知必会_日志