Mysql日志binlog、redo log、undo log

Mysql日志binlog、redo log、undo log

日志种类

本文仅仅针对InnoDb存储引擎进行讨论，在InnoDb存储引擎下，会有下列三种日志：

1. binlog
2. redo log
3. undo log

日志详解

binlog

binlog是MySQL架构中server层产生的日志，属于逻辑日志，可以理解为对mysql增删改行为的记录日志，记录日志的形式为追加，不覆盖原有日志，binlog日志可用于主从复制，数据恢复，恢复形式类似于对之前操作的回放来实现。

binlog也称为二进制日志，默认情况下是二进制格式，无法直接查看。可使用两种方式进行查看:

1. 使用mysqlbinlog工具: 用法：mysqlbinlog: /usr/bin/mysqlbinlog mysql-bin.000001。该工具是mysql自带的，可使用–read-from-remote-server从远程服务器读取二进制日志，还可使用–start-position –stop-position、–start-time= –stop-time精确解析binlog日志。
2. 直接使用命令行解析：在客户端执行如下语句：

SHOW BINLOG EVENTS[IN 'log_name'] //要查询的binlog文件名[FROM pos] [LIMIT [offset,] row_count]

例如：

show binlog events in 'mysql-bin.000001' from 1190 limit 2\G

mysql的binlog日志有ROW,Statement,MiXED三种格式:

1. row: 日志中会记录每一行数据的变化，不会记录SQL语句，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以row level的日志的内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function,以及trigger的调用和触发无法被正确复制的问题。在重放时，会很快的恢复或复制数据。但是因为binlog日志是记录的数据行为，只会追加，并且row是记录每行的变化，所以产生的日志会很大（比如一个SQL语句修改了2行数据，那么会产生2行数据被修改的记录,而Statement 只会记录一行SQL语句）。

2. Statement ：每一条会修改数据的sql都会记录在binlog中。相比row节约了空间，减少了IO，但是，由于只记录语句，所以，在statement level下 已经发现了有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些定的函数或者功能的时候会出现。

3. Mixed：自动模式，实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选一种。新版本中的statement level还是和以前一样，仅仅记录执行的语句。而新版本的mysql中对row level模式被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete 等修改数据的语句，那么还是会记录所有行的变更。

设置三种格式可通过my.cnf进行修改，也可通过执行命令修改，执行语句如下（执行命令若不加global修改默认是session级别，只对当前session有用）：

set global binlog_format='ROW或STATEMENT或MIXED'

可通过如下语句查看日志格式:

show variables like 'binlog_format'

关于MySQL5.6以前，开启了binlog日志后导致group commit失效问题，后面说明。

redo log

基本概念
redo log是在存储引擎层记录的日志，又称重做日志文件，实现事务的持久性，它是固定大小的一个或一组相同大小文件（默认是2个文件，可通过参数修改单个文件的大小和文件个数），redo log 是循环写的，redo log 不是记录数据页更新之后的状态，而是记录这个页做了什么改动，用它恢复数据比binlog要快。

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存（buffer pool），这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候（如系统空闲时），将这个操作记录更新到数据磁盘里面（刷脏页）。这个过程，其实就是 MySQL 里的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘（该磁盘是说的数据磁盘，不是日志磁盘，后面都用数据磁盘来表示数据文件，日志磁盘或redo log file表示redo log在磁盘上的文件）。

那么为什么要先写日志而不是直接修改数据磁盘数据呢？
首先要了解磁盘的寻址时间是毫秒级别的，而内存的寻址时间是纳秒级别的，如果每次都直接直接修改数据磁盘数据，比如做一个更新，就需要发生磁盘IO，在大量的数据里面找到对应的记录，提取出来修改，再写回去，这是非常消耗性能的。MySQL就通过log buffer先写入内存，记录一秒的提交操作（这个策略可改变，见下文），再写入体量较小的redo log日志磁盘文件（减少了磁盘IO的次数），适合的时候（比较空闲的时候）再去更改数据磁盘数据，这样就大大提高了性能。

那么又有人要问这记录日志也是发生磁盘IO，不是也很慢么？我们可通过下面这个故事理解下：
假设在一个没电脑的小旅馆，老板每天要给每个房间记录账本（账本中每个房间都是固定地方），假设客人和房间都比较多，当有人到前台结算时，他都要在厚厚的账本里面找到对应的房间，并记录下消费，要是客人很多，就会挤在前台等他慢慢找。但是如果改变下策略，使用一个小本子，只记录当天结算房间的数据，就不用去翻整个账本上的房间，到了空闲的时候，再根据小本子上的记录，更新到账本上去。

redo log block块
redo log(buffer和file)是以块为单位进行存储的，记录的是每个数据页（一个数据页默认16K大小）的物理变化。一个block(块)的大小是512K，其中日志块头占用12字节，日志块尾占用8字节（新版块尾是4字节），中间主体是：512-块头-块尾的字节=492（以块尾占用8字节来算）。
因为redo log记录的是数据页的变化，当一个数据页产生的变化需要使用超过492字节()的redo log来记录，那么就会使用多个redo log block来记录该数据页的变化。
日志块头包含4部分：

• log_block_hdr_no：(4字节)该日志块在redo log buffer中的位置ID。
• log_block_hdr_data_len：(2字节)该log block中已记录的log大小。写满该log block时为0x200，表示512字节。
• log_block_first_rec_group：(2字节)该log block中第一个log的开始偏移位置，因为数据页的变化可能一个块装不下，最后一个块又写不满。
• lock_block_checkpoint_no：(4字节)写入检查点信息的位置，检查点是已经刷盘到数据磁盘的位置。

redo log buffer刷盘到redo log file
通过上面描述可知道redo log包含两部分，一部分是在内存中（redo log buffer，用户态下的内存空间），一部分是在磁盘中(redo log file),中间会经过os buffer（内核态下的内存空间）。
redo log从内存写到磁盘的过程如下:

使用redo log buffer和os buffer的目的是为了缓冲，因为一次磁盘IO开销是比较大的，使用了缓冲后就减少了刷盘时的IO次数，提交了效率，当然可以通过调节参数来控制buffer如何刷到磁盘：

MySQL支持用户自定义在commit时如何将上图log buffer中的日志刷log file中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值：0、1、2，默认为1。但注意，这个变量只是控制commit动作是否刷新log buffer到磁盘。

• 当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file中。这种方式即使系统崩溃最多造成一次commit的丢失（在内存中，还未刷盘成功的记录最多只会有一条），但是因为每次提交都写入磁盘，IO的性能较差。
• 当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
• 当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file，系统崩溃也会最多丢失1秒钟的数据。

注意：上面描述的是当发生commit提交动作时，redo log的刷盘策略，还有个参数叫innodb_flush_log_at_timeout，其值为1秒，可修改，是控制刷日志的频率（没有commit提交动作时log buffer刷到log file的频率）,它并不能控制commit提交时，0,2策略中刷盘时间。

什么时候redo log buffer会刷到redo log file中

• 发出commit动作时。啥时候刷，看innodb_flush_log_at_trx_commit变量的控制；
• 每秒刷一次。这个刷日志的频率由变量 innodb_flush_log_at_timeout 值决定，默认是1秒。
• 当log buffer中已经使用的内存超过一半时。
• 当有checkpoint时。checkpoint刷数据盘时，会把脏页数据都刷到数据盘中，脏页包含了脏数据（内存中未刷到日志磁盘的数据）和脏日志（redo log file中未刷到数据磁盘中的数据），所以间接的就触发了redo log buffer会刷到redo log file中。

redo log file记录
redo log file因为是循环记录，所以会有两个指针，一个是记录redo log记录到哪里了(write pos)，一个是记录刷数据盘的位置（checkpoint）。

什么时候脏页会刷到数据盘?
脏页刷到数据盘只有一个操作，那就是checkpoint，触发checkpoint有以下几种情况：

• sharp checkpoint：在重用redo log文件(例如切换日志文件)的时候，将所有已记录到redo log中对应的脏数据刷到磁盘。
• fuzzy checkpoint：一次只刷一小部分的日志到磁盘，而非将所有脏日志刷盘。有以下几种情况会触发该检查点：
  1. master thread checkpoint：由master线程控制，每秒或每10秒刷入一定比例的脏页到磁盘。
  2. fuzzy checkpoint：flush_lru_list checkpoint：从MySQL5.6开始可通过 innodb_page_cleaners 变量指定专门负责脏页刷盘的page cleaner线程的个数，该线程的目的是为了保证lru列表有可用的空闲页。
  3. async/sync flush checkpoint：同步刷盘还是异步刷盘。例如还有非常多的脏页没刷到磁盘(非常多是多少，有比例控制)，这时候会选择同步刷到磁盘，但这很少出现；如果脏页不是很多，可以选择异步刷到磁盘，如果脏页很少，可以暂时不刷脏页到磁盘。
  4. dirty page too much checkpoint：脏页太多时强制触发检查点，目的是为了保证缓存有足够的空闲空间。too much的比例由变量 innodb_max_dirty_pages_pct 控制，MySQL 5.6默认的值为75，即当脏页占缓冲池的百分之75后，就强制刷一部分脏页到磁盘。
  5. MySQL停止时是否刷盘由变量innodb_fast_shutdown={ 0|1|2 }控制，默认值为1，即停止时只做一部分purge，忽略大多数flush操作(但至少会刷日志)，在下次启动的时候再flush剩余的内容，实现fast shutdown。
  6. innodb恢复重启时。

undo log

基本概念
undo log有两个作用，数据回滚（实现事务的原子性）和行版本控制（MVCC）。
undo log可以理解为redo log相反的日志，但是不全是，他是逻辑日志。当有个delete操作时，undo log记录的是insert，update时，记录的是update相反的记录。所以可通过他进行回滚。
还可以通过它进行行版本控制，比如：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

存储方式
innodb存储引擎对undo的管理采用段的方式。rollback segment称为回滚段，每个回滚段中有1024个undo log segment。

在以前老版本，只支持1个rollback segment，这样就只能记录1024个undo log segment。后来MySQL5.5可以支持128个rollback segment，即支持128*1024个undo操作，还可以通过变量 innodb_undo_logs (5.6版本以前该变量是 innodb_rollback_segments )自定义多少个rollback segment，默认值为128。

格式
当数据发生变更时，通过undo log记录变更前的数据。
在事务进行如下操作时会产生undo log日志：
① 在常规表上的insert操作
② 在常规表上的update和delete操作
③ 在临时表上的insert操作
④ 在临时表上的update和delete操作
在InnoDB引擎中，undo log格式分为两种：

1. insert undo log: 是指在insert的时候产生的undo log，因为数据不会被其他事物使用（因为是新增的数据，对其他事物不可见），所以在进行提交事物后，直接删除，不需要进行purge操作。
2. update undo log: 是指在delete/update的时候产生的undo log，因为此时可能还在提供MVCC操作，所以不能在提交时直接删除，会放入undo log 列表，等待purge删除。

delete/update操作机制
当执行delete操作时，数据不会立即被删除，而是加入delete列表，等待purge操作删除（因为数据可能还在提供mvcc操作）。

当执行update操作时，需要分两种情况：

1. 不是update主键列:undo log直接记录相反操作，直接update
2. 是update主键列：先删除该行，再插入一行目标行

group commit失效问题

什么是group commit
为了减少磁盘IO或几个事务之间具有原子性，就是一组事务一起提交，要么都成功，要么都失败。

什么情况下group commit会失效
在mysql5.6之前，开启了binlog，会导致group commit失效。原因在于为了保证binlog和redo log日志记录的顺序性和一致性，会开启一把prepare_commit_mutex锁，该锁导致了group commit的失效。

为什么要保证binlog和redo log日志的一致性
要了解这个问题，首先要了解这两个日志干嘛用的。
binlog：能用于主从复制和数据恢复。
redo log: 主要用于数据恢复，保证数据持久性，实现crash-safe。mysql在重启时会使用redo log进行数据恢复（不管是正常关闭还是异常关闭，启东时都会进行恢复操作）。

在了解了他们干嘛用的后，如果不加锁（不保证一致性），那么就会出现先写binlog，后写redo log的情况或者相反的情况。试想下，在现在的公司中，为了保证高可用，MySQL数据库都是集群，也就是有主从复制关系，不管先写谁都会出现主从数据不一致的情况：

1. 先写binlog，redo log未持久化完成宕机了，当此时数据根据binlog复制到从机，从机多了这条数据，主机重启mysql时根据redo log恢复发现数据需要回滚，此时就造成了主从数据不一致。
2. 先写redo log，binlog未持久化完成宕机了，数据不会同步给从机，主机重启后根据redo log恢复后就会多一条记录，也会造成主从数据不一致的情况。

如何保证一致性
了解完为什么要保证他们的一致性后，就该讨论MySQL是如何保证他们的一致性的，在MySQL5.6之前，redo log是两阶段提交：
1、准备阶段（prepare阶段）：数据先写入redo log，此时只记录状态为prepare，此时加锁unlock
2、写入binlog并刷binlog日志盘。
3、commit:修改redo log中的状态为commit,并清除undo log,此时释放锁
提交流程如下图:

由上图可见，由于加了锁，所以能保证事务的顺序性和一致性，两者落盘之后都会记录2PC事务的XID（redolog和binlog中事务落盘的标识），若中途数据库crash，通过XID关联两者并在恢复时决定commit和rollback与否。

恢复步骤：
redolog中的事务如果经历了二阶段提交中的prepare阶段，则会打上prepare标识，如果经历commit阶段，则会打上commit标识（此时redolog和binlog均已落盘）。

Step1. 按顺序扫描redolog，如果redolog中的事务既有prepare标识，又有commit标识，就直接提交（复制redolog disk中的数据页到磁盘数据页）

Step2 .如果redolog事务只有prepare标识，没有commit标识，则说明当前事务在commit阶段crash了，binlog中当前事务是否完整未可知，此时拿着redolog中当前事务的XID（redolog和binlog中事务落盘的标识），去查看binlog中是否存在此XID

? a. 如果binlog中有当前事务的XID，则提交事务（复制redolog disk中的数据页到磁盘数据页）

? b. 如果binlog中没有当前事务的XID，则回滚事务（使用undolog来删除redolog中的对应事务）

导致group commit失效的原因
从上面可以了解到，加了锁后，虽然保证了binlog和redolog日志的顺序性和一致性，但正因为加了锁，才导致了无法group commit，原因很简单，当一个事务在进行写日志的时候，另外一个事务拿不到锁，只能等待上一个事务日志写完后才能拿锁写日志。

MySQL5.6以后的提交
为了解决这个问题，MySQL5.6以后对提交进行了重构，放弃了锁，增加事务队列，并不需要保证所有事务的顺序性，只需要保证在一个事务组里面，进入binlog和redolog的一致性即可，并且修改为三阶段提交，分别为：flush阶段、sync阶段、commit阶段，如下图：

5.6以后的实现思路和Mariadb的思路类似，都是维护一个队列，第一个进入该队列的作为leader线程，否则作为follower线程。
leader线程收集follower的事务，并负责做sync，follower线程等待leader通知操作完成。
三个阶段中都有队列进行维护，所以三个阶段可以分开执行，也就是一个事务组在执行第二阶段的时候，另外一个事务组可执行第一阶段。但是每个阶段只能有一个事务组在执行：
flush阶段：进入队列（第一个进入队列的为leader），leader写入binlog的内存中，并pop出其他事物进行cache的写入。
sync阶段：进入sync的队列，leader将binlog刷到日志盘中进行持久化。若队列中有多个事务，那么仅一次fsync操作就完成了二进制日志的刷盘操作。这在MySQL5.6中称为BLGC(binary log group commit)。
commit阶段：进入队列，leader调用InnoDB中的提交事务，记录到redo log中，并pop出其他事物进行redo log的记录，因为InnoDB本身就支持group commit。

这样保证了一个事务组在binlog和redo log中提交的一致性和顺序性，但是多个事务组之间的顺序性不一定保证，一致性是保证的。
参数binlog_order_commits可控制多个事务组之间的顺序性，当关闭时，表示我们能接受binlog commit和innodb commit的顺序不同（这不会带来数据不一致，但可能会影响到热备份）

参考文章：
https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html#auto_id_16
https://blog.csdn.net/daijiguo/article/details/104982890
http://www.mamicode.com/info-detail-107031.html
https://blog.51cto.com/zhaowonq/1206751
https://www.cnblogs.com/sohuhome/p/11743994.html