【数据库】聊聊MySQL的日志，binlog、undo log、redo log

日志

在数据库中，如何保证数据的回滚，以及数据同步，系统宕机后可以恢复到原来的状态，其实就是依靠日志。
其中bin log是Server层特有的，redo log是Innodb存储引擎特有的。
bin log 是逻辑日志，主要记录这条SQL的原始逻辑，比如 name 设置为abc
redo log是物理日志，在某个数据页上做了什么修改。

一个案例

假设我们现在要对t表，(id,name) update t set name=‘qxlx’ where id = 1;设置操作，
根据上一篇数据库基础架构篇，这条SQL执行的流程都会走连接器，查询缓存，分析器，优化器，最后到执行器。
1.执行器根据建表的存储引擎来觉得使用哪个存储引擎，一般都是InnoDB。因为InnoDB中存在change buffer这样的机制，所以会先查询change buffer中有没有数据，如果有的话，直接操作更新，没有的话会先从磁盘中查询数据，返回给执行器。之后写入undo log 日志。

2.如何保证SQL没有提交，可以回滚到之前的状态呢，
在执行如上的SQL的之后，会先写入undo log，进行标记原始数据是什么 比如name = abc, 如果这个SQL没有提交事务，那么数据就会回滚到之前的状态 name=abc。
3.写入bin log日志。其实这个流程比较复杂，我们来慢慢梳理以下，具体的执行流程图在如下。
a.先从存储引擎中获取id=1这样数据，如果在内存中的话，那么直接返回，否则话，从磁盘中读取到数据。将数据返回给执行器
b.执行器会进行数据增加操作，name=‘qxlx’，写入新的一行，将数据更新到内存中，redo log此时处于prepare阶段，Server层写入bin log，存储引擎将redo log提交。

WAL

在写redo log日志的时候，一般来说，我们可以先把数据写到临时的缓存中，然后等到一定的时候比如mysql空闲的时候，在同步写回到磁盘中。而这样的话可以避免大量的IO 和 数据查找操作。这种机制就是 writer ahead logging技术。而redo log的区域是固定的，也就是写满的之后，需要强制刷新数据到磁盘中。
而如果要强制更新到磁盘中，可以通过以下参数强制更新到磁盘中。sync_binlog设置成1，也可以保证bin log 实时存储到磁盘中，保证系统重启后数据不会丢失。

innodb_flush_log_at_trx_commit = 1 
sync_binlog = 1 

其实可以想一下，一个学生正在听课，老师讲的比较快的时候，是赶不上讲笔记全部整理到自己的笔记本上，可以先写到抄本上，然后在下课后，讲笔记整理到笔记本上。

两阶段提交

以上在写入bin log和 redo log的时候，先写redo log 然后bin log redolog提交，需要这么麻烦嘛，我们这里分析以下。如果将两个过程分开来写的话
1.先写bin log，然后此时系统宕机了，重启之后，因为redo log没有写入，bin log(name=‘qxlx’) ，如果需要按照bin log进行数据同步到从库上，那么数据就是错误的，数据库本上是(name=‘abc’)
2.先写redo log，然后系统宕机，重启之后，redo log (name=‘qxlx’) 但是数据库 (name=‘abc’) ，会按照redo log进行重新更新数据，此时数据被更新为 (name=‘qxlx’) 所以数据不一致了，所以需要按照两阶段提交的方式来执行保证数据的一致性。

资料来源：mysql45讲