MySQL专栏学习系列，这个专栏我不会奢望多少人会看，只是记录下来，希望可以帮到那些需要的人。
看到这个题目是不是觉得数据库再也不用担心服务器 crash 了？
那我们需要学习为什么可以这么做？以及如何做？
即为什么可以恢复到任意时间点？如何恢复到任意时间点？
为什么有了 binlog 还需要 redo log？
事务是如何提交的？事务提交先写 binlog 还是 redo log？如何保证这两部分的日志做到顺序一致性？
上一次课我们学习了一条 select 语句的全部执行过程，那么今天我们就从一条 update 语句开始。
mysql> update T set c=c+1 where ID=2;其实执行流程和查询流程一致，只是最后执行器执行的是找到这条数据，并进行更新。
另外，更新过程还涉及到一个重要的日志模块，即 redo log（重做日志）和 binlog（归档日志）。
我个人是只听过 binlog 的。
1 redo log
和大多数关系型数据库一样，InnoDB 记录了对数据文件的物理更改，并保证总是日志先行。
也就是所谓的 WAL（Write-Ahead Logging），即在持久化数据文件前，保证之前的 redo log 已经写到磁盘。
MySQL 的每一次更新并没有每次都写入磁盘，InnoDB 引擎会先将记录写到 redo log 里，并更新到内存中，然后再适当的时候，再把这个记录更新到磁盘。
提到了两个重要的日志，我觉得这里有必要贴一下 InnoDB 的存储结构图，对其有一个整体的认识：
如果下面看的各种空间懵逼了，建议回来看一眼这个图。
1.1 redo log 是啥？
当数据库对数据做修改的时候，需要把数据页从磁盘读到 buffer pool 中，然后在 buffer pool 中进行修改。
那么这个时候 buffer pool 中的数据页就与磁盘上的数据页内容不一致，我们称 buffer pool 的数据页为 dirty page 脏数据。
感觉就像先拷贝一份数据，对拷贝的数据进行修改，修改完毕后再覆盖到原数据。
这里也可以看出，所有的更新操作都是现在 dirty page 中进行的。
如果这个时候发生非正常的 DB 服务重启，那么这些数据还没在内存，并没有同步到磁盘文件中（注意，同步到磁盘文件是个随机 IO），也就是会发生数据丢失。
如果这个时候，能够在有一个文件，当 buffer pool 中的 dirty page 变更结束后，把相应修改记录记录到这个文件（注意，记录日志是 顺序 IO）。
那么当 DB 服务发生 crash 的情况，恢复 DB 的时候，也可以根据这个文件的记录内容，重新应用到磁盘文件，数据保持一致。
这个文件就是 redo log ，用于记录数据修改后的记录，顺序记录。
我理解的，redo log 就是存放 dirty page 的物理空间。
1.2 何时产生 & 释放？
在事务开始之后就产生 redo log，redo log 的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 redo log 文件中。
当对应事务的脏页写入到磁盘之后，redo log 的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。
1.3 如何写？
redo log 文件以 ib_logfile[number] 命名，并以顺序的方式写入文件文件，写满时则回溯到第一个文件，进行覆盖写。
如图所示：
-
write pos是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头； -
checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；
write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。
如果 write pos 追上 checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
redo log 文件是循环写入的，在覆盖写之前，总是要保证对应的脏页已经刷到了磁盘。
在非常大的负载下，redo log 可能产生的速度非常快，导致频繁的刷脏操作，进而导致性能下降。
如果可预期会有这样的场景，我们建议调大 redo log 文件的大小。可以做一次干净的 shutdown，然后修改 redo log 配置，重启实例。
参考：http://mysql.taobao.org/monthly/2015/05/01/
1.4 相关配置
默认情况下，对应的物理文件位于数据库的 data 目录下的 ib_logfile1、 ib_logfile2。
innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认2# 关于文件的大小和数量，由一下两个参数配置innodb_log_file_size 重做日志文件的大小。innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认11.5 其他
redo log 有一个缓存区 Innodb_log_buffer，默认大小为 8M，Innodb 存储引擎先将重做日志写入 innodb_log_buffer 中。
然后会通过以下三种方式将 innodb 日志缓冲区的日志刷新到磁盘：
1、Master Thread 每秒一次执行刷新 Innodb_log_buffer 到重做日志文件； 2、每个事务提交时会将重做日志刷新到重做日志文件； 3、当 redo log 缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件；
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。
CrashSafe 能够保证 MySQL 服务器宕机重启后：
-
所有已经提交的事务的数据仍然存在
-
所有没有提交的事务的数据自动回滚
2 binlog
如前文所讲，MySQL 整体可以分为 Server 层和引擎层。
其实，redo log 是属于引擎层的 InnoDB 所特有的日志，而 Server 层也有自己的日志，即 binlog（归档日志）。
2.1 binlog 是什么？
逻辑格式的日志，可以简单认为就是执行过的事务中的 sql 语句。
但又不完全是 sql 语句这么简单，而是包括了执行的 sql 语句（增删改）反向的信息。
也就意味着 delete 对应着 delete 本身和其反向的 insert；update 对应着 update 执行前后的版本的信息；insert 对应着 delete 和 insert 本身的信息。
2.2 何时产生 & 释放？
事务提交的时候，一次性将事务中的 sql 语句按照一定的格式记录到 binlog 中。因此，对于较大事务的提交，可能会变得比较慢一些。
binlog 的默认是保持时间由参数 expire_logs_days 配置，也就是说对于非活动的日志文件，在生成时间超过配置的天数之后，会被自动删除。
2.3 和 redo log 的区别
1、redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现，所有引擎都可以使用；
2、内容不同：redo log 是物理日志，记录的是在数据页上做了什么修改,是正在执行中的 dml 以及 ddl 语句；
而 binlog 是逻辑日志，记录的是语句的原始逻辑，已经提交完毕之后的 dml 以及 ddl sql 语句，如「给 ID=2 的这一行的 c 字段加 1」；
3、写方式不同：redo log 是循环写的，空间固定；binlog 是可以一直 追加写的，一个文件写到一定大小后，会继续写下一个，之前写的文件不会被覆盖；
4、作用不同：redo log 主要用来保证事务安全，作为异常 down 机或者介质 故障后的数据恢复使用，binlog 主要用来做主从复制和即时点恢复时使用；
5、另外，两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。
参考：http://www.importnew.com/28039.html
3 数据更新事务流程
有了对这两个日志的概念性理解，我们再来看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。
1、执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
对应到上面讲的，就是将数据加载到脏数据中。
2、执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3、引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4、执行器生成这个操作的 binlog，并把 binlog 写入磁盘；
5、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
两阶段提交
上面处理 redo log 和 binlog 看着是不是有点懵逼？
其实这就是所谓的两阶段提交，即 COMMIT 会被自动的分成 prepare 和 commit 两个阶段。
MySQL 在 prepare 阶段会生成 xid，然后会在 commit 阶段写入到 binlog 中。在进行恢复时事务要提交还是回滚，是由 Binlog 来决定的。
由上面的二阶段提交流程可以看出，通过两阶段提交方式保证了无论在任何情况下，事务要么同时存在于 redo log 和 binlog 中，要么两个里面都不存在。
这样就可以保证事务的 binlog 和 redo log 顺序一致性。一旦阶段 2 中持久化 binlog 完成，就确保了事务的提交。
此外需要注意的是，每个阶段都需要进行一次 fsync 操作才能保证上下两层数据的一致性。
PS：记录 Binlog 是在 InnoDB 引擎 Prepare（即 Redo Log 写入磁盘）之后，这点至关重要。
另外需要注意的一点就是，SQL 语句产生的 Redo 日志会一直刷新到磁盘（master thread 每秒 fsync redo log），而 Binlog 是事务 commit 时才刷新到磁盘，如果 binlog 太大则 commit 时会慢。
参考：http://www.ywnds.com/?p=7892
举个例子
Bin log 用于记录了完整的逻辑记录，所有的逻辑记录在 bin log 里都能找到，所以在备份恢复时，是以 bin log 为基础，通过其记录的完整逻辑操作，备份出一个和原库完整的数据。
如 redo log 执行了 update t set status = 1，此时原库的数据 status 已更新为 1，而 bin log 写入失败，没有记录这一操作，后续备份恢复时，其 status = 0，导致数据不一致）。
其核心就是， redo log 记录的，即使异常重启，都会刷新到磁盘，而 bin log 记录的， 则主要数据库逻辑操作，主要用于备份恢复。
一个完整的交易过程：
账本记上卖一瓶可乐（redo log 为 prepare 状态），然后收钱放入钱箱（bin log 记录）然后回过头在账本上打个勾（redo log 置为commit），给人可乐，表示一笔交易结束。
如果收钱时交易被打断，回过头来整理此次交易，发现只有记账没有收钱，则交易失败，删掉账本上的记录（回滚）；
如果收了钱后被终止，然后回过头发现账本有记录（prepare）而且钱箱有本次收入（bin log），则继续完善账本（commit），本次交易有效。
4 如何恢复任意时间的数据？
当需要恢复到指定的某一秒时，比如 2018.11.23 14.23.45 有一次数据库误操作，需要找回数据，那你可以这么做：
1、首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上 11.22 日的一个备份，从这个备份恢复到临时库；
2、然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。
这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。
当遇到 crash 时，恢复的过程也非常简单：
1、恢复过程中会扫描最后一个 binlog 文件，提取其中的 xid；
2、重做检查点以后的 redo 日志，搜集处于 prepare 阶段的事务链表，将事务的 xid 与 binlog 中的 xid 对比。
若存在，说明事务记录到 binlog 成功，但是最终未 commit 成功，则提交，否则就回滚；
这里要结合上面的两段提交一起看，才能理解得比较透彻。
总结一下，基本顶多会出现下面是几种情况：
-
当事务在 prepare 阶段 crash，数据库 recovery 的时候该事务未写入 binlog 并且 redo log 未提交，将该事务 rollback。
-
当事务在 binlog 阶段 crash，此时日志还没有成功写入到磁盘中，启动时会 rollback 此事务。
-
当事务在 binlog 日志已经 fsync 到磁盘后 crash，但是 InnoDB 没有来得及 commit，此时 MySQL 数据库 recovery 的时候将会读出 binlog 中的 xid，然后告诉 InnoDB 提交这些 xid 的事务，InnoDB 提交完这些事务后会回滚其它的事务，使 redo log 和 binlog 始终保持一致。
我再来说下自己的理解
1、prepare 阶段； 2、写binlog 阶段；3、commit 阶段；
当在2之前崩溃时
重启恢复：后发现没有commit，回滚。
备份恢复：没有 binlog 。一致。
当在3之前崩溃
重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。
备份：有binlog，一致。
总结起来说就是如果一个事务在 prepare 阶段中落盘成功，并在 MySQL Server 层中的 binlog 也写入成功，那这个事务必定 commit 成功。
总结
介绍了 MySQL 里面最重要的两个日志，即物理日志 redo log 和逻辑日志 binlog。
最好能够理解这两种日志的作用分别是什么，自己能够理清楚事物的提交流程。
课后题目 & 评论区精华
这次评论区精彩有点多！
问
课后题目
前面我说到定期全量备份的周期“取决于系统重要性，有的是一天一备，有的是一周一备”。那么在什么场景下，一天一备会比一周一备更有优势呢？或者说，它影响了这个数据库系统的哪个指标？
答1
备份时间周期的长短，感觉有2个方面
首先，是恢复数据丢失的时间，既然需要恢复，肯定是数据丢失了。如果一天一备份的话，只要找到这天的全备，加入这天某段时间的binlog来恢复，如果一周一备份，假设是周一，而你要恢复的数据是周日某个时间点，那就，需要全备+周一到周日某个时间点的全部binlog用来恢复，时间相比前者需要增加很多；看业务能忍受的程度
其次，是数据库丢失，如果一周一备份的话，需要确保整个一周的binlog都完好无损，否则将无法恢复；而一天一备，只要保证这天的binlog都完好无损；当然这个可以通过校验，或者冗余等技术来实现，相比之下，上面那点更重要
答2
备份数据库的周期直接影响到了恢复的速度，一天一备的话，恢复时只需要重新执行最近一天的数据库修改操作。而一周一备则需要做很多。所以在对于系统恢复速度很敏感的系统，最好使用一天一备，甚至一小时一备等等。
答3
我理解备份就是救命药加后悔药，灾难发生的时候备份能救命，出现错误的时候备份能后悔。事情都有两面性，没有谁比谁好，只有谁比谁合适，完全看业务情况和需求而定。一天一备恢复时间更短，binlog更少，救命时候更快，但是后悔时间更短，而一周一备正好相反。我自己的备份策略是设置一个16小时延迟复制的从库，充当后悔药，恢复时间也较快。再两天一个全备库和binlog，作为救命药,最后时刻用。这样就比较兼顾了。
答4
1如果没有主从，无Binlog Server，建议至少每天一备份，库很小并发少，可以缩短备份周期，例如每小时备份一次。
2如果有主从，有Binlog Server，建议至少每周备份一次，库较小并发不算高，可以缩短备份周期，例如每天备份一次。
这里不区分有主从无Binlog Server的情况，是由于重要系统建议至少搭建主从复制，尽可能搭建Binlog Server(金融环境尤为重要)。
问
1.首先客户端通过tcp/ip发送一条sql语句到server层的SQL interface
2.SQL interface接到该请求后，先对该条语句进行解析，验证权限是否匹配
3.验证通过以后，分析器会对该语句分析,是否语法有错误等
4.接下来是优化器器生成相应的执行计划，选择最优的执行计划
5.之后会是执行器根据执行计划执行这条语句。在这一步会去open table,如果该table上有MDL，则等待。
如果没有，则加在该表上加短暂的MDL(S)
(如果opend_table太大,表明open_table_cache太小。需要不停的去打开frm文件)
6.进入到引擎层，首先会去innodb_buffer_pool里的data dictionary(元数据信息)得到表信息
7.通过元数据信息,去lock info里查出是否会有相关的锁信息，并把这条update语句需要的锁信息写入到lock info里(锁这里还有待补充)
8.然后涉及到的老数据通过快照的方式存储到innodb_buffer_pool里的undo page里,并且记录undo log修改的redo
(如果data page里有就直接载入到undo page里，如果没有，则需要去磁盘里取出相应page的数据，载入到undo page里)
9.在innodb_buffer_pool的data page做update操作。并把操作的物理数据页修改记录到redo log buffer里
由于update这个事务会涉及到多个页面的修改，所以redo log buffer里会记录多条页面的修改信息。
因为group commit的原因，这次事务所产生的redo log buffer可能会跟随其它事务一同flush并且sync到磁盘上
10.同时修改的信息，会按照event的格式,记录到binlog_cache中。(这里注意binlog_cache_size是transaction级别的,不是session级别的参数,
一旦commit之后，dump线程会从binlog_cache里把event主动发送给slave的I/O线程)
11.之后把这条sql,需要在二级索引上做的修改，写入到change buffer page，等到下次有其他sql需要读取该二级索引时，再去与二级索引做merge
(随机I/O变为顺序I/O,但是由于现在的磁盘都是SSD,所以对于寻址来说,随机I/O和顺序I/O差距不大)
12.此时update语句已经完成，需要commit或者rollback。这里讨论commit的情况，并且双1
13.commit操作，由于存储引擎层与server层之间采用的是内部XA(保证两个事务的一致性,这里主要保证redo log和binlog的原子性),
所以提交分为prepare阶段与commit阶段
14.prepare阶段,将事务的xid写入，将binlog_cache里的进行flush以及sync操作(大事务的话这步非常耗时)
15.commit阶段，由于之前该事务产生的redo log已经sync到磁盘了。所以这步只是在redo log里标记commit
16.当binlog和redo log都已经落盘以后，如果触发了刷新脏页的操作，先把该脏页复制到doublewrite buffer里，把doublewrite buffer里的刷新到共享表空间，然后才是通过page cleaner线程把脏页写入到磁盘中
老师，你看我的步骤中有什么问题嘛？我感觉第6步那里有点问题,因为第5步已经去open table了，第6步还有没有必要去buffer里查找元数据呢?这元数据是表示的系统的元数据嘛,还是所有表的？谢谢老师指正
答
其实在实现上5是调用了6的过程了的，所以是一回事。MySQL server 层和InnoDB层都保存了表结构，所以有书上描述时会拆开说。
这个描述很详细，同时还有点到我们后面要讲的内通
问
你好，关于提到的'数据页'这个词我没有太理解，是一种存储方式么？
答
MySQL的记录是以“页”为单位存取的，默认大小16K。也就是说，你要访问磁盘中一个记录，不会只读这个记录，而会把它所在的16K数据一起读入内存
问
请问用redolog恢复时还写binlog吗？反之呢？
答
崩溃恢复过程不写binlog了，用binlog恢复实例（或搭建备库）的时候，是会写redolog的
往期精彩
↓↓↓ 不止技术 ↓↓↓
你的关注是我持续写作的动力！
