Mysql 重做日志

innodb事务日志包括redo log和undo log。redo log是重做日志，用来做数据库的crash recovery前滚操作，undo log是回滚日志，提供回滚操作。
redo log保存了对InnoDB表中的数据的修改记录，所以也叫日志文件
undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志：
1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行怎么修改，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。目的：万一实例或者介质失败，重做日志文件就能派上用场
2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录
mysql执行crash 恢复大致过程：先根据redo log做前滚，等到物理的数据库操作之后，才能在物理数据一致的基础上做逻辑操作，也就是undo回滚操作

1，redo log

1.1,重做日志与二进制日志的区别

1）二进制日志是存储引擎上层产生的，不管什么引擎，对数据库进行了修改就会产生，而重做日志是INNODB层产生的，只记录改存储引擎中表的修改

1）二进制日志会记录所有与mysql有关的日志记录，包括InnoDB等其他存储引擎的日志，而InnoDB存储引擎的重做日志只记录有关其本身的事务日志，

2）记录的内容不容，不管你将二进制日志文件记录的格式设为哪一种，其记录的都是关于一个事务的具体操作内容，本质还是逻辑的SQL，而InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况；

3）写入的时间也不同，二进制日志文件是在事务提交时一次性写入缓存中的日志“文件”，而在事务进行的过程中，不断有重做日志条目被写入重做日志文件中。也就是二进制产生于REDO LOG之前

4）二进制日志记录方式是跟提交顺序有关，一次提交对应一条记录。redo log中的是物理页的修改，同一个事务如果出现多次修改情况下，后边的修改（最后提交的修改）会覆盖所有未提交的记录；并且redo log是并发写入，不同是物质间的不同版本记录会穿插写入到redo log文件

5）事务日志记录的是物理页的情况，它具有幂等性，比如插入一行又删除了，多次操作但状态没变；二进制日志记录的是所有影响数据的操作，记录的内容较多。

1.2 redo log 基本概念

redo log是重做日志，保障数据库安全的重要功能之一

redo log包括两部分，一个是内存中的日志缓冲buffer，一个是磁盘上的重做日志文件redo log file

通过force log at commit；数据库在commit时候必须将该事务的所有事务日志写入redo log file和undo log file

为了确保每次日志在提交时候的写入文件，每次写日志过程都会调用系统的fsync()操作，因为MySQL是工作在用户空间的，MySQL的log buffer处于用户空间的内存中。要写入到磁盘上的log file中(redo:ib_logfileN文件,undo:share tablespace或.ibd文件)，中间还要经过操作系统内核空间的os buffer，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中
从redo log buffer写日志到磁盘的redo log file中的过程如下：

 

从日志缓冲写入磁盘上的重做日志文件的条件：

在主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否提交。另一个触发这个过程是由参数innodb_flush_log_at_trx_commit控制（用户定义控制），表示在提交时，处理重做日志的方式。

参数innodb_flush_log_at_trx_commit可设的值有0、1、2,

• 0，事务提交时不会将log buffer中日志写入到os buffer，而是主线程每秒将log buffer写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
• 1，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file ondisk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
• 2，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk

 值得注意的一点：因为重做日志有个capacity变量，该值代表了最后的检查点不能超过这个阀值。

在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置为如下：

•     启用二进制日志，设置sync_binlog=1，每提交一次事务同步写到磁盘中
•     总是设置innodb_flush_log_at_trx_commit=1，即每提交一次事务都写到磁盘中

这两项变量的设置保证了：每次提交事务都写入二进制日志和事务日志，并在提交时将它们刷新到磁盘中

测试：

mysql> show variables like '%trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> create table test(id int,name char(50)) engine=innodb;
ERROR 1046 (3D000): No database selected
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test(id int,name char(50)) engine=innodb;
Query OK, 0 rows affected (0.37 sec)

mysql> delimiter $$ 
mysql> create procedure proc1(i int)
    -> begin
    ->     declare s int default 1;
    ->     declare c char(50) default repeat('A',50);
    ->     while s<=i do
    ->         start transaction;
    ->         insert into test values(1,c);
    ->         -- commit;
    ->         set s=s+1;
    ->     end while;
    -> commit;
    -> end$$
delimiter ;Query OK, 0 rows affected (0.27 sec)

mysql> delimiter ;
mysql> call proc1(10000);
Query OK, 0 rows affected (55.26 sec)

mysql> set @@global.innodb_flush_log_at_trx_commit=2;  
Query OK, 0 rows affected (0.23 sec)

mysql> truncate table test;
Query OK, 0 rows affected (0.46 sec)

mysql> call proc1(10000);
Query OK, 0 rows affected (26.32 sec)

1.3 redo log file/log group

log group表示的是redo log group，一个组内由多个大小完全相同的redo log file组成。组内redo log file的数量由变量 innodb_log_files_group 决定，默认值为2，即两个redo log file（这两个文件就是重做日志文件，如果在启动数据库时这两个文件不存在，则InnoDB会根据配置或者默认值，重新创建日志文件）。这个组是一个逻辑的概念，并没有真正的文件来表示这是一个组，但是可以通过变量 innodb_log_group_home_dir 来定义组的目录，redo log file都放在这个目录下，默认是在datadir下

Mysql默认情况下会有两个文件：ib_logfile0和ib_logfile1，，或者事务日志。

 

每个InnoDB存储引擎至少有一个重做日志文件组，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0、ib_logfile1。InnoDB存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，当重做日志文件2也被写满时，会再被切换到重做日志文件1中。

mysql> show global variables like "innodb_log%"
    -> ;
+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| innodb_log_buffer_size             | 16777216 |
| innodb_log_checksums               | ON       |
| innodb_log_compressed_pages        | ON       |
| innodb_log_file_size               | 50331648 |
| innodb_log_files_in_group          | 2        |
| innodb_log_group_home_dir          | ./       |
| innodb_log_spin_cpu_abs_lwm        | 80       |
| innodb_log_spin_cpu_pct_hwm        | 50       |
| innodb_log_wait_for_flush_spin_hwm | 400      |
| innodb_log_write_ahead_size        | 8192     |
+------------------------------------+----------+

[root@centos7 data]# ls ib* -l
-rw-r----- 1 mysql mysql     3373 Sep 20 15:24 ib_buffer_pool
-rw-r----- 1 mysql mysql 50331648 Mar 10 14:25 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Mar 10 14:25 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Mar 10 14:25 ibdata1

有两个ib_logfile开头的文件，它们就是log group中的redo log file，而且它们的大小完全一致且等于变量 innodb_log_file_size 定义的值。第一个文件ibdata1是在没有开启 innodb_file_per_table 时的共享表空间文件，对应于开启innodb_file_per_table 时的.ibd文件

在innodb将log buffer中的redo log block刷到这些log file中时，会以追加写入的方式循环轮训写入。即先在第一个log file（即ib_logfile0）的尾部追加写，直到满了之后向第二个log file（即ib_logfile1）写。当第二个log file满了会清空一部分第一个log file继续写入。

由于是将log buffer中的日志刷到log file，所以在log file中记录日志的方式也是log block的方式

1.4 LSN

LSN称为日志的逻辑序列号(log sequence number)，在innodb存储引擎中，lsn占用8个字节。LSN的值会随着日志的写入而逐渐增大，增长量是根据一个MTR（mini-transaction）写入的日志量来计算的，写多少日志（单位字节），LSN就增长多少。日志文件轮训一圈，LSN的增长量大约就是整个日志文件的大小（日志文件头占一部分），所以他是一个逻辑物理意义于一身的概念，去别的其他数据库的逻辑概念，并且没提交一个事务LSN就+1

逻辑事务具有ACID特性，要么全做，要么全部做。物理事务这里这么称为可能是由于，在InnoDB存储引擎中，只要涉及文件修改，文件读取等物理操作，都离不开这个物理事务，可以说物理事物是Buffer pool中的内存page与文件之间的一个桥梁

可以看到，无论读写，只要用到底层的buffer pool页面都会使用到MTR，他是上面逻辑层和下面物理层的交互窗口，也是用来保证下层物理数据正确，完整、持久的机制

在访问一个文件页面的时候，系统都会要将访问的页面装载到Buffer pool，才能访问这个页面，然后就可以读取更新这个页面，在这个页面变化的过程，日志系统会不断记录日志来保证事物的ACID，当然InnoDB采用的也是LOGWRITE-AHEAD

写日志底层也是需要写页头和页尾，这样才能保证事务的完整性，也是有物理事物保证的。

由LSN，可以获取几个有用的信息：
1.数据页的版本信息。
2.写入的日志总量，通过LSN开始号码和结束号码可以计算出写入的日志量。
3.可知道检查点的位置。
还可以获得很多隐式的信息

LSN不仅存在于redo log中，还存在于数据页中，在每个数据页的头部，有一个fil_page_lsn记录了当前页最终的LSN值是多少。通过数据页中的LSN值和redo log中的LSN值比较，如果页中的LSN值小于redo log中LSN值，则表示数据丢失了一部分，这时候可以通过redo log的记录来恢复到redo log中记录的LSN值时的状态

mysql> show engine innodb status;
---
LOG
---
Log sequence number          70823109
Log buffer assigned up to    70823109
Log buffer completed up to   70823109
Log written up to            70823109
Log flushed up to            70823109
Added dirty pages up to      70823109
Pages flushed up to          70823109
Last checkpoint at           70823109
284768 log i/o's done, 0.00 log i/o's/second

其中：

• log sequence number就是当前的redo log(in buffer)中的lsn；
• log flushed up to是刷到redo log file on disk中的lsn；
• pages flushed up to是已经刷到磁盘数据页上的LSN；
• last checkpoint at是上一次检查点所在位置的LSN 

1.5 日志的刷盘规则

log buffer中未刷到磁盘的日志称为脏日志(dirty log)

刷日志到磁盘有以下几种规则：

• 发出commit提交逻辑事务动作时。已经说明过，commit发出后是否刷日志由变量 innodb_flush_log_at_trx_commit 控制
• Master线程在后台每秒刷一次。这个刷日志的频率由变量 innodb_flush_log_at_timeout 值决定，默认是1秒。这个刷日志频率和commit动作无关
• 当log buffer中空间用完了，便将已经产生的log buffer中的日志刷新到磁盘中。
• 当有checkpoint时，checkpoint在一定程度上代表了刷到磁盘时日志所处的LSN位置，要保证所有要刷的数据页面中的LSN值最小--最旧的日志已经刷入到磁盘。否则，此时数据库挂了，日志不存在，但是数据页已经被修改，从而导致数据不一致，违背了先写日志的原则
• 每次执行DML操作的时候，都会主动检查日志空间是否够用，如果使用空间量已经超过了一个预设的经验值，就会去主动刷新日志，保证后边真正执行时，不会在执行过程中被动等待刷盘，但这里只会写入OS缓存中，不会刷盘

1.6 数据页刷盘的规则及checkpoint

内存中(buffer pool)未刷到磁盘的数据称为脏数据(dirty data)。由于数据和日志都以页的形式存在，所以脏页表示脏数据和脏日志。

在innodb中，数据刷盘的规则只有一个：checkpoint。但是触发checkpoint的情况却有几种。不管怎样，checkpoint触发后，会将buffer中脏数据页和脏日志页都刷到磁盘。

innodb存储引擎中checkpoint分为两种：

•     sharp checkpoint：在重用redo log文件(例如切换日志文件)的时候，将所有已记录到redo log中对应的脏数据刷到磁盘。
•     fuzzy checkpoint：一次只刷一小部分的日志到磁盘，而非将所有脏日志刷盘。有以下几种情况会触发该检查点：

1.         master thread checkpoint：由master线程控制，每秒或每10秒刷入一定比例的脏页到磁盘。
2.         flush_lru_list checkpoint：通过 innodb_page_cleaners 变量指定专门负责脏页刷盘的page cleaner线程的个数，该线程的目的是为了保证lru列表有可用的空闲页
3.         async/sync flush checkpoint：同步刷盘还是异步刷盘。例如还有非常多的脏页没刷到磁盘(非常多是多少，有比例控制)，这时候会选择同步刷到磁盘，但这很少出现；如果脏页不是很多，可以选择异步刷到磁盘，如果脏页很少，可以暂时不刷脏页到磁盘
4.         dirty page too much checkpoint：脏页太多时强制触发检查点，目的是为了保证缓存有足够的空闲空间。too much的比例由变量 innodb_max_dirty_pages_pct 控制

 

1.7 innodb的恢复行为

在启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。

因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如二进制日志)要快很多。而且，innodb自身也做了一定程度的优化，让恢复速度变得更快。

重启innodb时，checkpoint表示已经完整刷到磁盘上data page上的LSN，因此恢复时仅需要恢复从checkpoint开始的日志部分。例如，当数据库在上一次checkpoint的LSN为10000时宕机，且事务是已经提交过的状态。启动数据库时会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从检查点开始恢复。

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

另外，事务日志具有幂等性，所以多次操作得到同一结果的行为在日志中只记录一次。而二进制日志不具有幂等性，多次操作会全部记录下来，在恢复的时候会多次执行二进制日志中的记录，速度就慢得多。例如，某记录中id初始值为2，通过update将值设置为了3，后来又设置成了2，在事务日志中记录的将是无变化的页，根本无需恢复；而二进制会记录下两次update操作，恢复时也将执行这两次update操作，速度比事务日志恢复更慢

 

1.8 redo log有关的几个变量

• innodb_flush_log_at_trx_commit={0|1|2} # 指定何时将事务日志刷到磁盘，默认为1。
• innodb_log_buffer_size：# log buffer的大小
• innodb_log_file_size：# 事务日志的大小
• innodb_log_files_group =2：# 事务日志组中的事务日志文件个数，默认2个
• innodb_log_group_home_dir =./：# 事务日志组路径，当前目录表示数据目录

2 undo log

2.1 基本概念

undo log有两个作用：提供回滚和多个行版本控制(MVCC)。
在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚

UNDO日志类型有两种，也就是两种回滚段类型，一种是INSERT的UNDO记录，一种是UNDATE的UNDO记录，分类的依据是事务提交后要不要做PURGE操作，因为INSERT操作时不需要PURGE操作，事务提交了这个回滚记录就不需要可以丢了，而对于更新和删除操作，事务提交了还需要为MVCC服务，就需要讲这些日志放到History List中，等待做PURGE，以及MVCC的多版本查询等

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。

另外，undo log也会产生redo log，因为undo log也要实现持久性保护

2.2 undo log的存储方式

innodb存储引擎对undo的管理采用段的方式。rollback segment称为回滚段

undo log默认存放在共享表空间中

[root@centos7 data]# ll ib*
-rw-r----- 1 mysql mysql     3373 Sep 20 15:24 ib_buffer_pool
-rw-r----- 1 mysql mysql 50331648 Mar 10 14:25 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Mar 10 14:25 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Mar 10 14:25 ibdata1 #共享表空间

如果开启了innodb_file_per_table，将放在每个表的.ibd文件中，

说明：启用innodb_file_per_table后，每张表的表空间只存放自己的：数据，索引和插入缓冲BITMAP页。其它信息仍放在默认表空间。
其它信息：回滚(undo)信息、插入缓冲索引页、系统的事务信息、二次写缓冲(Double write buffer)等

 

2.3 undo log相关的变量

 

mysql> show variables like "%undo%";
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_encrypt  | OFF        |
| innodb_undo_log_truncate | ON         |
| innodb_undo_tablespaces  | 2          |
+--------------------------+------------+

 

2.4 delete/update操作的内部机制

当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除

但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

通过undo log记录delete和update操作的结果发现：(insert操作无需分析，就是插入行而已)

•     delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
•     update分为两种情况：update的列是否是主键列。

        如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
        如果是主键列，update分两部执行：先删除该行，再插入一行目标行

2.5 binlog和事务日志的先后顺序及group commit

如果事务不是只读事务，即涉及到了数据的修改，默认情况下会在commit的时候调用fsync()将日志刷到磁盘，保证事务的持久性。

但是一次刷一个事务的日志性能较低，特别是事务集中在某一时刻时事务量非常大的时候。innodb提供了group commit功能，可以将多个事务的事务日志通过一次fsync()刷到磁盘中。

因为事务在提交的时候不仅会记录事务日志，还会记录二进制日志，但是它们谁先记录呢？二进制日志是MySQL的上层日志，先于存储引擎的事务日志被写入。

在MySQL5.6以前，当事务提交(即发出commit指令)后，MySQL接收到该信号进入commit prepare阶段；进入prepare阶段后，立即写内存中的二进制日志，写完内存中的二进制日志后就相当于确定了commit操作；然后开始写内存中的事务日志；最后将二进制日志和事务日志刷盘，它们如何刷盘，分别由变量 sync_binlog 和 innodb_flush_log_at_trx_commit 控制。

但因为要保证二进制日志和事务日志的一致性，在提交后的prepare阶段会启用一个prepare_commit_mutex锁来保证它们的顺序性和一致性。但这样会导致开启二进制日志后group commmit失效，特别是在主从复制结构中，几乎都会开启二进制日志。

在MySQL5.6中进行了改进。提交事务时，在存储引擎层的上一层结构中会将事务按序放入一个队列，队列中的第一个事务称为leader，其他事务称为follower，leader控制着follower的行为。虽然顺序还是一样先刷二进制，再刷事务日志，但是机制完全改变了：删除了原来的prepare_commit_mutex行为，也能保证即使开启了二进制日志，group commit也是有效的。

MySQL5.6中分为3个步骤：flush阶段、sync阶段、commit阶段。

•     flush阶段：向内存中写入每个事务的二进制日志。
•     sync阶段：将内存中的二进制日志刷盘。若队列中有多个事务，那么仅一次fsync操作就完成了二进制日志的刷盘操作。这在MySQL5.6中称为BLGC(binary log group commit)。
•     commit阶段：leader根据顺序调用存储引擎层事务的提交，由于innodb本就支持group commit，所以解决了因为锁 prepare_commit_mutex 而导致的group commit失效问题。

在flush阶段写入二进制日志到内存中，但是不是写完就进入sync阶段的，而是要等待一定的时间，多积累几个事务的binlog一起进入sync阶段，等待时间由变量 binlog_max_flush_queue_time 决定，默认值为0表示不等待直接进入sync，设置该变量为一个大于0的值的好处是group中的事务多了，性能会好一些，但是这样会导致事务的响应时间变慢，所以建议不要修改该变量的值，除非事务量非常多并且不断的在写入和更新。

进入到sync阶段，会将binlog从内存中刷入到磁盘，刷入的数量和单独的二进制日志刷盘一样，由变量 sync_binlog 控制。

当有一组事务在进行commit阶段时，其他新事务可以进行flush阶段，它们本就不会相互阻塞，所以group commit会不断生效。当然，group commit的性能和队列中的事务数量有关，如果每次队列中只有1个事务，那么group commit和单独的commit没什么区别，当队列中事务越来越多时，即提交事务越多越快时，group commit的效果越明显

本文参考了：https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html#auto_id_6