MySQL日志

日志分类

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

redo log

我们都知道，事务的四大特性里面有一个是 持久性 ，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态 。

我们先了解MySQL中的数据存储： 数据以 页 方式存储，每次存取都是以页为单位。

如果每次修改都去覆盖原有的页，可能就修改了少数字段，但是需要将没有修改的页进行覆盖，就造成了大量的磁盘IO。

理解： 一本书（数据库）有很多页（存储的数据），如果你需要修改这一页中的某些字，就需要将这一页进行替换（覆盖）

那么有什么好的办法可以解决这种频繁的页修改吗？

使用一个文件去记录哪一页修改了什么，在后续需要刷新的时候统一修改。这个文件就是redo log

MySQL查询以页为单位，会将读入的数据存入 Buffer Pool中，后续读取会先从Buffer Pool中读取；修改时不是直接修改磁盘中的数据，而是修改 Buffer Pool中的数据（从操作磁盘变成操作内存，提高性能），同时将记录的更新信息记录到 redo log buffer中，后续将 redo log buffer刷入磁盘中。

图解：

刷盘

InnoDB存储引擎为redo log的刷盘策略提供了innodb_flush_log_at_trx_commit参数，它支持三种策略：

• 0：设置为0的时候，每次提交事务时不刷盘。
• 1：设置为1的时候，每次提交事务时刷盘。默认值
• 2：设置为2的时候，每次提交事务时都只把redo log buffer写入page cache。

innodb_flush_log_at_trx_commit = 0

这种方法有什么问题吗？

如果MySQL在这1秒内宕机了，这部分日志还没有写入到page cache中（事务已提交），就丢失了这部分数据

innodb_flush_log_at_trx_commit = 1

每次都调用fsync将数据写入到磁盘中，只要事务提交了，磁盘中就一定存在这部分日志。

innodb_flush_log_at_trx_commit = 2

如果机器发生了故障，没来得及调用fsync进行刷盘，产生日志数据丢失。

文件组

redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。

比如可以配置为一组4个文件，每个文件的大小是 1GB，整个 redo log 日志文件组可以记录4G的内容。

在个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

• write pos 是当前记录的位置，一边写一边后移
• checkpoint 是当前要擦除的位置，也是往后推移

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。

每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。

write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。

如果 write pos 追上checkpoint，表示日志文件组满了，这时候不能再写入新的redo log记录，MySQL得停下来，清空一些记录，把checkpoint推进一下。

bin log

bin log是逻辑日志，记录的是语句的原始逻辑。

无论是什么存储引擎，只要数据发生变化就会产生bin log日志。

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

作用：用来做数据库的数据备份、主备、主主、主从

bin log记录格式

1. statement
2. row
3. mixed

statement： 记录SQL语句原文，记录占用磁盘空间小

这会有什么问题？

当SQL语句中需要操作跟当前时间有关系的函数时，执行原语句的时间和同步时使用bin log时产生的数据不一致

row： 记录操作的具体数据，记录占用磁盘空间大

也就是说将记录时刻的数据保存下来。例如：同步时SQL语句中包含了获取当前时间的函数 now() ，记录时刻是 t1，同步时刻是 t2，由于记录了具体的数据，此时会使用t1作为数据填充

既然上述2种方式都有优缺，那么就可以取长补短。

mixed： 是statement和row的结合。判断SQL语句是否会引起数据不一致，如果不会就使用statement，如果会就使用row。即保证了数据一致性，又节省了磁盘空间。

写入机制

一个事务的binlog无论这个事务多大，也要确保一次性写入，所以给每个线程分配一个binlog cache，来保存该线程的binlog，在事务提交时，将binlog cache写入到binlog文件中。

图解：

• 图中的write操作只是写入到文件系统缓存，并没有将其刷入磁盘，所以速度较快
• 只有调用fsync后，才能将文件系统缓存中的数据进行刷盘

write和fsync的时机，可以由参数sync_binlog控制，默认是1

0：进行write操作后，不会马上调用fsync进行刷盘，fsync调用时机由操作系统控制

问题： 写入到page cache后，机器由于某些原因宕机了，数据还没进行持久化，这部分数据就丢失了

1：进行write操作后，马上调用fsync进行刷盘

n：不为0、1的值，进行write操作后，不会马上调用fsync，等待page cache中有n个事务后才会进行刷盘

问题； 写入到page cache后，机器由于某些原因宕机了，数据还没有进行持久化，那么最近的 N（0~n-1） 个事务就会丢失

redo log和bin log比较

• redo log是Innodb独有，bin log是数据库Server层日志，所有存储引擎都有
• redo log占用磁盘空间固定（文件组和文件组数量固定），bin log以追加的形式添加日志，占用磁盘空间越来越大。
• redo log用来做数据灾难恢复，bin log用来做数据库主从备份

思考： 为什么redo log不能作为主从备份的日志？

1. 显然redolog是Innodb独有，如果是其它存储引擎就无法适用
2. redo log采用的是文件组的方式，也就是说旧redo log会被新的redo log覆盖。保存的是“未做的操作”，无法保存从0开始的所有日志

两段提交

redo log（重做日志）让InnoDB存储引擎拥有了崩溃恢复能力。

binlog（归档日志）保证了MySQL集群架构的数据一致性。

虽然它们都属于持久化的保证，但是侧重点不同。

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

回到正题，redo log与binlog两份日志之间的逻辑不一致，会出现什么问题？

我们以update语句为例，假设id=2的记录，字段c值是0，把字段c值更新成1，SQL语句为update T set c=1 where id=2。

假设执行过程中写完redo log日志后，binlog日志写期间发生了异常，会出现什么情况呢？

由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。因此，之后用binlog日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为redo log日志恢复，这一行c值是1，最终数据不一致。

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

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

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

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

来源： MySQL三大日志(binlog、redo log和undo log)详解 | JavaGuide

总结： 在执行更新语句过程，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入。发生故障或事物回滚，就会出现bin log中没有而redo log有的情况，为解决这种问题，引入了两段提交，写入redo log时，处于prepare阶段，表示该“日志不可用”，在写入bin log后将设置为redo log 设置成commit阶段，表示该“日志可用”。（可用与不可用相对于灾难恢复而言）

undo log

在MySQL中，确保事务的原子性通常需要通过回滚日志（undo log）来实现恢复机制。在执行任何修改操作之前，MySQL会先将这些修改记录到回滚日志中，然后再执行相应的操作。如果在执行过程中发生异常，系统可以利用回滚日志中的信息将数据回滚到修改之前的状态。这一机制的重要性在于，回滚日志的记录优先于数据的持久化到磁盘上。即使数据库突然宕机等情况发生，当数据库重新启动时，它仍然可以通过查询回滚日志来回滚未完成的事务。

此外，多版本并发控制（MVCC）的实现依赖于隐藏字段、Read View和undo log。在InnoDB的内部实现中，数据行的DB_TRX_ID和Read View用于判断数据的可见性。如果数据不可见，则系统会通过数据行的DB_ROLL_PTR来找到undo log中的历史版本。每个事务读取的数据版本可能是不同的。在同一个事务中，用户只能看到该事务创建Read View之前已经提交的修改，以及该事务本身所做的修改。

参考文章

面试官：聊聊binlog/redolog/undolog的区别？ - 知乎

MySQL三大日志(binlog,redolog,undolog)详解 - 掘金