数据库技术:MySQL的三大日志：redolog、undolog、binlog

mysql的日志分为几大类：错误日志、查询日志、慢查询日志、事务日志（redo log和undo log）、二进制日志（binlog）。

binlog

简单的例子，我们在硬盘加载到内存之后，进行一系列操作，一顿操作猛如虎，还未刷新到硬盘之前，在XXX位置记录先记录下，然后再进行增删改查操作，最后刷入硬盘。如果未刷入硬盘，在重启之后，先加载之前的记录，那么数据就回来了。

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

用途：

• 复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的
• 数据恢复：通过mysqlbinlog工具恢复数据
• 增量备份

查看：

    1.  mysqlbinlog  mysql-bin.000007

    2. 直命令行解析 SHOW BINLOG EVENTS  [IN &＃39;log_name&＃39;]  [FROM pos]    [LIMIT [offset,] row_count]

mysql> show binlog events in &＃39;mysql-bin.000007&＃39; from 1190 limit 2G

格式：STATMENT、ROW和MIXED

• 基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。
• 基于行的复制(row-based replication, RBR)，不记录每条sql语句的上下文信息，记录哪条数据被修改了。
• 基于上述两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用前一模式保存binlog，无法复制的操作使用ROW模式保存binlog。
• 选取规则：如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog_format 的设定而记录；如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何都采用statement模式记录

刷盘时机：

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

    mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N，MySQL 5.7.7之后版本的默认值为 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。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。

redo log写入流程： A. redo log buffer –>  B. os buffer  –>  C. redo log file

刷盘时机：

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

0：延迟写，大约每秒刷新写入到磁盘数据 。如果出现系统崩溃，可能会出现丢失1秒数据，在流程中的A-B之间。

1：实时写，实时刷，每次提交就写入磁盘，IO 性能差。

2：实时写，延迟刷，即每秒刷，在流程中的B-C之间。

记录形式：

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

write pos 是redo log当前记录的LSN(逻辑序列号)位置，checkpoint 是表示数据页更改记录刷盘后对应redo log所处的LSN位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos到check point之间的部分是redo log空着的部分，用于记录新的记录；check point到write pos之间是redo log待落盘的数据页更改记录，当write pos追上check point时，会先推动check point向前移动，空出位置再记录新的日志。

crash-safe：

启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如binlog)要快很多。重启innodb时，首先会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从checkpoint开始恢复。

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

两阶段提交        

 看看下图中update执行流程：

其中redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"，它是为了让两份日志之间的逻辑一致。  

那么，保持两份日志之间逻辑一致，有什么用呢？简单说，当你误操作数据库 或者 给数据库扩容增加读能力的时候，这种一致性能保证数据库数据恢复到误操作之前，或者能达到主从一致的目的。

binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内的数据可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；

然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。

这两歩可以参照：https://zhuanlan.zhihu.com/p/33504555

两阶段提交怎么保证一致的？或者说如果没有两阶段提交，数据能保证一致吗？

再用上面流程图的例子，假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

1. 要么是先写 redo log 后写 binlog。redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

2. 要么先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

现在，可以看看在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象？

如果是在写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库

如果binlog 写完，redo log 还没 commit 前发生 crash，那崩溃恢复的时候 MySQL 会怎么处理？如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整，是则提交事务。

追问几个问题：

1. 不引入两个日志，也就没有两阶段提交的必要了。只用 binlog 来支持崩溃恢复，又能支持归档，不就可以了？

历史原因，InnoDB 并不是 MySQL 的原生存储引擎。MySQL 的原生引擎是 MyISAM，设计之初就没有支持崩溃恢复。InnoDB 在作为 MySQL 的插件加入 MySQL 引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了。

实现上的原因，那就是binlog没有crash-safe能力。

2. 发过来，只用redo log行不行？

一是 redo log没有归档能力，他都是循环写。一个是mysql系统依赖于binlog，MySQL 系统高可用的基础，就是 binlog 复制。

3. 正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。

undo log

数据库事务四大特性中有一个是原子性，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。

实际上，原子性底层就是通过undo log实现的。undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。同时，undo log也是MVCC(多版本并发控制)实现的关键

需要了解更多数据库技术:MySQL的三大日志：redo log、undo log、 binlog，都可以关注数据库技术分享栏目—编程笔记