01 前言

事件是这样的，我负责我司的报表零碎，小胖是我小弟。某天他手贱误删了一条生产的数据。被用户在群里疯狂投诉质问，火急火燎的跑来问我怎么办。我特么冷汗都进去了，申斥了他一顿：蠢，蠢得都能够进博物馆了，生产的数据能轻易动？

小胖看我平时笑嘻嘻的，没想到发这么大的火。心一急，竟然给我跪下了：远哥，我上有老，下有小，中有女朋友，不要开革我呀。我一听火更大了：合着就你有女朋友？？？这个时候咱们 DBA 老林来打圆场：别慌，年轻人管不住下自身，不免做错事。我能够把数据恢复到一个月内任意时刻的状态。听到这，小胖忙抱着老林大腿哭爹喊娘地感激。

听到这你是不是很奇怪？能复原到半个月前的数据？DBA 老林到底是如何做到的？我跟他细聊了一番。老林点燃了手中 82 年的华子，深深吸了一口说到：事件还得从 update 语句是如何执行的说起。

1.1 从更新语句说起

假如我当初有建表语句，如下：

CREATE TABLE `student`  (`id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 66 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

表数据如下：

明天恰好张三生日，我要把它的 age 加一岁。于是执行以下的 sql 语句：

update student set age = age + 1 where id = 2;

后面聊过查问语句是如何执行的？错过的同学看这篇《工作三年：小胖连 select 语句是如何执行的都不晓得，真的菜！》，外面的查问语句流程，更新语句也会走一遍，如下流程图：

update 语句发动：首先连接器会连贯数据库。接着分析器通过词法、语法分析晓得这是更新语句。所以查问缓存生效。

之前的文章提到：如果表有更新。那么它的查问缓存会失败。这也是为啥，我不倡议你应用查问缓存的起因。

优化器则决定应用 ID 索引去更新，最初执行器负责找到这行数据，执行更新。

重点来了：与查问流程不一样，更新还波及两个重要的日志模块。一是重做日志：redo log，二是归档日志：binlog。要解答文章结尾的问题，必须要明确这两日志的原理能力整明确 DBA 是怎么做到的。

02 事务日志：redo log

什么是 redo log？为了不便了解，先举个来自极客工夫的例子：

还记得《孔乙己》这篇文章，饭店掌柜 有一个粉板，专门用来记录客人的赊账记录 。如果赊账的人不多，那么他能够把顾客名和账目写在板上。但如果赊账的人多了，粉板总会有记不下的时候，这个时候掌柜肯定还有一个 专门记录赊账的账本。

如果有人要赊账或者还账的话，掌柜个别有两种做法：

• 一种做法是间接把账本翻出来，把这次赊的账加上去或者扣除掉；
• 另一种做法是先在粉板上记下这次的账，等打烊当前再把账本翻出来核算。

在生意红火柜台很忙时，掌柜肯定会抉择后者，因为前者操作切实是太麻烦了。首先，你得找到这个人的赊账总额那条记录。你想想，稀稀拉拉几十页，掌柜要找到那个名字，可能还得带上老花镜缓缓找，找到之后再拿出算盘计算，最初再将后果写回到账本上。

这整个过程想想都麻烦。相比之下，还是先在粉板上记一下不便。你想想，如果掌柜没有粉板的帮忙，每次记账都得翻账本，效率是不是低得让人难以忍受？

2.1 为什么须要 redo log？

同样，在 MySQL 中，如果每一次的更新要写进磁盘，这么做会带来重大的性能问题：

• 因为 Innodb 是以页为单位进行磁盘交互的，而一个事务很可能只批改一个数据页外面的几个字节，这时将残缺的数据页刷到磁盘的话，太浪费资源了！
• 一个事务可能波及批改多个数据页，并且这些数据页在物理上并不间断，应用 随机 IO 写入性能太差！

为了解决这个问题，MySQL 的设计者就用了相似掌柜粉板的思路来晋升更新效率。这种思路在 MySQL 中叫 WAL（Write-Ahead Logging），意思就是：先写 redo log 日志，后写磁盘。日志和磁盘就对应下面的粉板和账本。

具体到 MySQL 是这样的：有记录须要更新，InnDB 把记录写到 redo log 中，并更新内存中的数据页，此时更新就算实现。同时，后盾线程会把操作记录更新异步到磁盘中的数据页。

PS：当须要更新的数据页在内存中时，就会间接更新内存中的数据页；不在内存中时，在能够应用 change buffer（篇幅无限，这个前面写文章再聊）的状况下，就会将更新操作记录到 change buffer 中，并将这些操作记录到 redo log 中；如果此时有查问操作，则触发 merge 操作，返回更改后的记录值。

有些人说 InnoDB 引擎把日志记录写到 redo log 中，redo log 在哪，不也是在磁盘上么？

对，这也是一个写磁盘的过程，然而与更新过程不一样的是，更新过程是在磁盘上随机 IO，费时。而写 redo log 是在磁盘上程序 IO，效率要高。

PPS：redo log 的存在就是把全局的随机写，变换为部分的程序写，从而提高效率。

2.2 redo log 的写入过程

redo log 记录了事务对数据页做了哪些批改。它包含两局部：别离是内存中的日志缓冲（redo log buffer）和磁盘上的日志文件（redo logfile）。

mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个工夫点再一次性将多个操作记录写到 redo log file。也就是咱们下面提到的 WAL 技术。

计算机操作系统通知咱们：用户空间下的缓冲区数据是无奈间接写入磁盘的。因为两头必须通过操作系统的内核空间缓冲区（OS Buffer）。

所以，redo log buffer 写入 redo logfile 实际上是先写入 OS Buffer，而后操作系统调用 fsync() 函数将日志刷到磁盘。过程如下：

mysql 反对三种将 redo log buffer 写入 redo log file 的机会，能够通过 innodb_flush_log_at_trx_commit 参数配置，各参数值含意如下：倡议设置成 1，这样能够保障 MySQL 异样重启之后数据不失落。

写的过程如下：

2.3 redo log file 的构造

InnoDB 的 redo log 是固定大小的。比方能够配置为一组 4 个文件，每个文件的大小是 1GB，那么 redo log file 能够记录 4GB 的操作。从头开始写。写到开端又回到结尾循环写。如下图：

上图中，write pos 示意 redo log 以后记录的 LSN (逻辑序列号) 地位，一边写一遍后移，写到第 3 号文件开端后就回到 0 号文件结尾；check point 示意数据页更改记录刷盘后对应 redo log 所处的 LSN(逻辑序列号) 地位，也是往后推移并且循环的。

PS：check point 是以后要擦除的地位，它与数据页中的 LSN 该当是统一的。

write pos 到 check point 之间的局部是 redo log 的未写区域，可用于记录新的记录；check point 到 write pos 之间是 redo log 已写区域，是待刷盘的数据页更改记录。

当 write pos 追上 check point 时，示意 redo log file 写满了，这时候有就不能执行新的更新。得停下来先擦除一些记录（擦除前要先把记录刷盘），再推动 check point 向前挪动，腾出地位再记录新的日志。

2.4 什么是 crash-save？

有了 redo log，即在 InnoDB 存储引擎中，事务提交过程中任何阶段，MySQL 忽然奔溃，重启后都能保障事务的完整性，已提交的数据不会失落，未提交残缺的数据会主动进行回滚。这个能力称为 crash-safe，依赖的就是 redo log 和 undo log 两个日志。

比方：重启 innodb 时，首先会查看磁盘中数据页的 LSN，如果数据页的 LSN 小于日志中 check point 的 LSN，则会从 checkpoint 开始复原。

2.5 回滚日志 undo log

undo log，次要提供回滚的作用，但还有另一个作用，就是多个行版本控制 (MVCC)，保障事务的原子性。在数据批改的流程中，会记录一条与以后操作相同的逻辑日志到 undo log 中（能够认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相同的 update 记录），如果因为某些起因导致事务异样失败了，能够借助该 undo log 进行回滚，保障事务的完整性，所以 undo log 也必不可少。

03 归档日志：binlog

上一篇聊查问语句的执行过程时，聊到 MySQL 的架构蕴含 server 层和引擎层。而 redo log 是 InnoDB 引擎特有的日志，而 server 层也有本人的日志，那就是 binlog。

最开始 MySQL 里并没有 InnoDB 引擎。MySQ L 自带的引擎是 MyISAM，然而 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件模式引入 MySQL 的，只依附 binlog 是没有 crash-safe 能力的，所以 InnoDB 应用另外一套日志零碎——也就是 redo log 来实现 crash-safe 能力。

3.1 binlog 日志格局？

binlog 有三种格局，别离为 STATMENT、ROW 和 MIXED。

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

• STATMENT：每一条会批改数据的 sql 语句会记录到 binlog 中。
• ROW：不记录 sql 的上下文信息，仅需记录哪条数据被批改。记两条，更新前和更新后都有。
• MIXED：前两种模式的混合，个别的复制应用 STATEMENT 模式保留 binlog，对于 STATEMENT 模式无奈复制的操作应用 ROW 模式保留 binlog

3.2 binlog 能够做 crash-save 吗？

只用一个 binlog 是否能够实现 cash_safe 能力呢？答案是能够的，只不过 binlog 中也要退出 checkpoint，数据库故障重启后，binlog checkpoint 之后的 sql 都重放一遍。然而这样做让 binlog 耦合的性能太多。

有人说，也能够间接间接比照匹配全量 binlog 和磁盘数据库文件，但这样做的话，效率低不说。因为 binlog 是 server 层的记录并不是引擎层的，有可能导致数据不统一的状况：

如果 binlog 记录了 3 条数据，失常状况引擎层也写了 3 条数据，然而此时节点宕机重启，binlog 发现有 3 条记录须要回放，所以回放 3 条记录，然而引擎层可能曾经写入了 2 条数据到磁盘，只须要回放一条 1 数据。那 binlog 回放的前两条数据会不会反复呢，比方会报错 duplicate key。

另外，binlog 是追加写，crash 时不能断定 binlog 中哪些内容是曾经写入到磁盘，哪些还没被写入。而 redolog 是循环写，从 check point 到 write pos 间的内容都是未写入到磁盘的。

所以，binlog 并不适宜做 crash-save。

3.3 两种日志的区别

redo log 和 binlog 次要有三种不同：

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都能够应用。
• redo log 是物理日志，记录的是 在某个数据页上做了什么批改；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比方“ 给 ID=2 这一行的 age 字段加 1 ”。
• redo log 是循环写的，空间固定会用完；binlog 是能够追加写入的。追加写 是指 binlog 文件写到肯定大小后会切换到下一个，并不会笼罩以前的日志。

3.4 update 语句的执行流程

理解了两种日志的概念，再来看看执行器和 InnoDB 引擎在执行 update 语句时的流程：

• 执行器取 id = 2 的行数据。ID 是主键，引擎用树搜寻找到这一行。如果这一行所在的数据页原本就在内存中，就间接返回给执行器；否则，须要先从磁盘读入内存，再返回。
• 执行器拿到引擎给的行数据，把这个值加上 1，比方原来是 N，当初就是 N+1，失去新的一行数据，再调用引擎接口写入这行新数据。
• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 外面，此时 redo log 处于 prepare 状态。而后告知执行器执行实现了，随时能够提交事务。
• 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，redo log 会写入 binlog 的文件名和地位信息来保障 binlog 和 redo log 的一致性，更新实现。

整个过程如下图所示，其中橙色框示意是在 InnoDB 外部执行的，绿色框示意是在执行器中执行的：

3.5 两阶段提交

因为 redo log 和 binlog 是两个独立的逻辑，如果不必两阶段提交，要么就是先写完 redo log 再写 binlog，或者采纳反过来的程序。咱们看看这两种形式会有什么问题。

依然用后面的 update 语句来做例子。假如以后 id=2 的行，字段 age 的值是 22，再假如执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间产生了 crash，会呈现什么状况呢？

1. 先写 redo log 后写 binlog。假如在 redo log 写完，binlog 还没有写完的时候，MySQL 过程异样重启。因为咱们后面说过的，redo log 写完之后，零碎即便解体，依然可能把数据恢复回来，所以复原后这一行 age 的值是 22。

然而 binlog 没写完就 crash 了，这时 binlog 外面并没有记录这个语句。因而，之后备份日志的时候，存起来的 binlog 外面就没有这条语句。

等到须要用这个 binlog 来复原长期库的话，因为这个语句的 binlog 失落，这个长期库就会少了这一次更新，复原进去的这一行 age 值就是 22，与原库的值不同。

1. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，因为 redo log 还没写，解体复原当前这个事务有效，所以 age 的值是 22。然而 binlog 外面曾经记录了 ” 把从 22 改成 23″ 这个日志。所以，在之后用 binlog 来复原的时候就多了一个事务进去，复原进去的这一行 age 的值就是 23，与原库的值不同。

所以，如果不应用 ” 两阶段提交 ”，数据库的状态就有可能和用 binlog 复原进去的不统一。

另外：sync_binlog 这个参数倡议设置成 1，示意每次事务的 binlog 都长久化到磁盘，这样能够保障 MySQL 异样重启之后 binlog 不失落。

3.6 binlog 的利用场景

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

04 数据恢复的过程

后面说过，binlog 会记录所有的逻辑操作，并且是采纳 ” 追加写 ” 的模式。如果你的 DBA 承诺说一个月内能够复原，那么备份零碎中肯定会保留最近一个月的所有 binlog，同时零碎会定期做整库备份。这里的 ” 定期 ” 取决于零碎的重要性，能够是一天一备，也能够是一周一备。

当须要复原到指定的某一秒时，比方某天下午两点发现中午十二点有一次误删表，须要找回数据，那你能够这么做：

• 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份复原到长期库；
• 而后，从备份的工夫点开始，将备份的 binlog 顺次取出来，重放到中午误删表之前的那个时刻。

这样你的长期库就跟误删之前的线上库一样了，而后你能够把表数据从长期库取出来，按须要复原到线上库。

看到这里，小胖露出了目视父亲的笑容。

伟人的肩膀

• 《高性能 MySQL》
• zhihu.com/question/411272546/answer/1375199755
• zhihu.com/question/425750274/answer/1525436152
• time.geekbang.org/column/article/68633
• my.oschina.net/vivotech/blog/4289724
• hiddenpps.blog.csdn.net/article/details/108505371

05 总结

本文解说了事务日志（redo og）的几个方面：为什么须要 redo log？它的写入过程、构造、存的啥以及什么是 crash-save 等等；此外还聊了 binlog 的定义、日志格局、与 redo log 的区别、update 语句的执行流程、两阶段提交、以及 binlog 的利用场景。

好啦，以上就是狗哥对于 MySQL 日志的总结。感激各技术社区大佬们的付出，如果说我看得更远，那是因为我站在你们的肩膀上。心愿这篇文章对你有帮忙，咱们下篇文章见~

06 送点书

如果看到这里，喜爱这篇文章的话，请帮点个难看。微信搜寻 一个优良的废人 ，关注后回复 电子书 送你 1000+ 本编程电子书，包含 C、C++、Java、Python、GO、Linux、Git、数据库、设计模式、前端、人工智能、面试相干、数据结构与算法以及计算机根底，详情看下图。回复 1024送你一套残缺的 java 视频教程。