1、重做日志-redo log

InnoDB特有的日志，物理日志

1.1、为什么会有重做日志？

需要定时刷新磁盘：缓冲池的设计师为了协调CPU速度和磁盘速度的鸿沟，所以操作都是先在缓冲池进行完成的。如果一条DML语句，update或者delete了页中的数据，那么此时页是脏的。数据库需要要新版本的页刷新到磁盘。
给数据库提供了crash-safe功能
crash-safe指：
MySQl服务器宕机重启后，能够保证：1）所有已经提交的事务的数据仍然存在。 2）所有没有提交的事务的数据自动回滚

为什么需要重做日志：如果每一个页更新都要刷新磁盘，开销大。而且在缓冲池将页的新版本刷新到磁盘时发生了宕机，那么数据就不能恢复了。
所以采用了Write Ahead Log策略，当前事务提交时，先写重做日志，再修改页。
Write Ahead Logging——先写日志，在写磁盘

1.2、redo log记录的是什么

物理日志（即数据页中的真实二级制数据，恢复速度快）。对数据页的修改逻辑以及change buffer的变更。
在事务的运行过程中被不断写入（所以一个事务有多个日志）

1.3、重做日志分为2部分

1.3.1、redo log buffer

【什么是重做日志缓冲】

InnoDB中除了缓冲池外还会有重做日志缓冲（redo log buffer），InnoDB引擎会先将重做日志放在这个缓冲区中，然后按照一定频率刷新到重做日志文件（redo log file）中。
重做日志缓冲一般不需要设置的很大，因为一般情况下，每一秒会将重做日志缓冲刷新到日志文件中，用户只需要保证每秒产生的事务量在这个缓冲大小即可，或者每个事务提交后会刷新到磁盘

【查询重做日志缓冲大小】

默认大小为8MB（跟版本有关系）
show variables like ‘innodb_log_buffer_size’;
我这查询的是2MB

【重做日志刷盘时机】

为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)。
因为MySQL是工作在用户空间的，MySQL的log buffer处于用户空间的内存中。要写入到磁盘上的log file中(redo:ib_logfileN文件,undo:share tablespace或.ibd文件)，中间还要经过操作系统内核空间的os buffer缓冲区
调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中。
参考链接

可通过innodb_flush_log_at_trx_commit 参数配置 rede log buffer 写入 redo log file的时机

• 0：日志缓存区将每隔一秒写到日志文件中，并且将日志文件的数据刷新到磁盘上。该模式下在事务提交时不会主动触发写入磁盘的操作。
• 1：每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中，并且刷新到磁盘中，该模式为系统默认。
• 2：每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中，但是并不会同时刷新到磁盘上。该模式下，MySQL会每秒执行一次刷新磁盘操作。

三种类型对应的刷盘时机

1.3.2、redo log file

InnoDB的redo log file是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么这块“粉板”总共就可以记录4GB的操作。

1.4、重做日志可以无限大吗？

如果重做日志可以无限大，能够缓冲所有数据库的数据，是不需要将缓冲池的新版本刷新磁盘的，因为宕机时，完全可以通过重做日志来恢复系统的数据到宕机那一刻，需要下面的条件。

• 缓冲池可以缓存数据库中所有数据
• 重做日志可以无限增大

但是上面是不可能的，所以提供了一个Checkpoint-检查点的技术。

1.5、Checkpoint

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么这块“粉板”总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

【check point】

表示redo log中修改记录已刷入磁盘后的LSN（LSN-Log Sequence Number标记版本，LSN是8字节的数组），每个页都有LSN
标识数据落盘的边界（当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件）。
checkpoint和write pos中间是已记录的数据页修改数据，此部分数据还未刷入磁盘

【write pos】

表示redo log当前记录的日志序列号LSN（Log Sequence Number）

• 当前记录位置，一边写一遍向后移动。记录sql的数据域修改逻辑以及change buffer的变更。
• 当write pos写完id_logfile_3后，会回到id_logfile_0循环写，如果追上checkpoint后需要等数据进行落盘，等checkpoint向后移动一段距离后再写。

• write pos到checkpoint中间是可用来记录新操作的部分（空余部分——不在使用可以覆盖重写）

  1.5.1、Checkpoint解决的问题

  
  当数据库发生宕机时，数据库不需要把所有的重做所有的日志，Checkpoint之前的页都会刷新到磁盘，数据库只对Checkpoint之后的日志继续拿给你恢复。

  1.5.2、Checkpoint的分类

  【Sharp Checkpoint】

  sharp Checkpoint在数据库关闭的时候所有脏页刷回磁盘，默认参数，innodb_fast_shutdown=1

  【Fuzzy Checkpoint】

  只刷新一部分脏页，而不是刷新所有脏页
  发生Fuzzy Checkpoint的情况

• Master Thread Checkpoint

每秒/每10s从缓存池的脏页列表中刷新一定比例的页回磁盘，异步操作

• FLUSH_LRU_LIST Checkpoint

保证LRU列表中需要有差不多100个空闲也供使用。

• Async/Sync Flush Checkpoint

重做日志不可用的时候，强制一些页刷新回磁盘，脏页是从脏页列表中选中的，将已经写入到重做日志的LSN记为redo_lsn，已经刷新回磁盘的最新也的LSN为checkpoint_lsn

• Dirty Page too much

脏页数量太多，强制进行checkpoint，参数innodb_max_dirty_pages_pct表示，75，表示缓存池中脏页占比75%

1.6、redo恢复过程

1.7、LSN—恢复阶段

数据恢复时，判断磁盘中的lsn是不是小于重做日志中的lsn，如果小于，那么就需要恢复数据。

2、回滚日志undo log

逻辑日志
Innodb专属的日志，记录每行数据事务执行前的数据。主要作用用于实现MVCC版本的控制，保证事务隔离级别的读已提交和读未提交。
undo存放在数据库内部的一个特殊段（segment）中，这个段为undo段（undo segemtn），undo段位于共享表空间中

2.1、为什么要有undo log日志

• 为了实现事务回滚和MVCC功能的实现，保证事务的原子性
• undo是逻辑日志，只是将数据库逻辑恢复到原来的样子，不是所有修改的逻辑都被取消了。

  2.2、记录内容

  2.2.1、存储内容

  记录数据修改前的状态，在数据修改流程中，同时记录一条与当前操作相反的逻辑日志。例如：当前事务是删除操作，那么undo log中记录的就是插入操作，依次可以借助undo log将数据回滚到事务执行前的状态，保证事务的完整性。
  事务提交后，undo中对应的记录会删除？？？

  2.2.2、存储位置

  存放在数据库内部的一个特殊字段（segment）中，这个字段也称为undo段（undo segment）。
  undo log 与 redo log怎么配合？
  undo log记录事务开始前要修改数据的原始版本，当我们再次对这行数据进行修改，所产生的修改记录会写入到redo log 。以此引出前滚与回滚

  2.3、undo log的格式

  2.3.1、insert undo log

  在insert操作中产生的undo log，因为Insert操作的记录，只对事务本身可见，对其他事务不可见（事务隔离要求），所以undo log可以在事务提交后，直接删除

  【下图所示为insert undo log的图示】

  | * | 表示对其进行了压缩 | | —- | —- | | next(end of record) | 记录下一个undo log的位置 | | start(start of record) | 占用两个字节，undo log的开始位置 | | type_cmpl(undo type) | 占用一个字节，记录undo类型，insert undo log，值为11 | | undo_no | 记录事务的ID | | table_id | 记录undo log对应的表对象 |

2.3.2、update undo log

update undo log记录的是对delete和update操作产生的undo log，可能需要提供MVCC机制，所以不能在事务提交时就进行删除。
提交时，放入undo log链表，等待Purge线程进行最后删除。

【下图所示为update undo log的结构图】

所占空间更大，next、start、undo_no、table_id与insert undo log部分相同
而type_cmpl（undo type）分为下面三个：

其他字段的含义

2.4、undo log日志版本链的形成

2.4.1、记录行结构

聚簇索引的记录除了会保存完整的用户数据以外，而且还会自动添加名为DB_trx_id、DB_roll_pointer的隐藏列（如果用户没有在表中定义主键以及UNIQUE键，还会自动添加一个名为DB_row_id的隐藏列）。所以一条记录在页面中的真实结构看起来就是这样的：

trx_id就是某个对这个聚簇索引记录做改动的语句所在的事务对应的事务id。

2.4.2、日志版本链的形参

roll_pointer本质上就是一个指向记录对应的undo日志的指针。
比方说我们上边向undo_demo表里插入了2条记录，每条记录都有与其对应的一条undo日志。记录被存储到了类型为FIL_PAGE_INDEX的页面中（就是我们前边一直所说的数据页），undo日志被存放到了类型为FIL_PAGE_UNDO_LOG的页面中。效果如图所示：

如果我们线程先删除一行记录，在删除一条记录，那么他所形成的版本链为，那么具体形参的版本链为：

2.4.3、undo日志的过程

1. 对 DB_ROW_ID = 1 的这行记录加排他锁
2. 把该行原本的值拷贝到 undo log 中，DB_TRX_ID 和 DB_ROLL_PTR 都不动
3. 修改该行的值这时产生一个新版本，更新 DATA_TRX_ID 为修改记录的事务 ID，将 DATA_ROLL_PTR 指向刚刚拷贝到 undo log 链中的旧版本记录，这样就能通过 DB_ROLL_PTR 找到这条记录的历史版本。如果对同一行记录执行连续的 UPDATE，Undo Log 会组成一个链表，遍历这个链表可以看到这条记录的变迁
4. 记录 redo log，包括 undo log 中的修改

undo log日志详解
undo log 日志结构详解

3、前滚和回滚

3.1、前滚

未完全提交的事务，即该事务已经被执行commit命令了
只是现在该事务修改所对应的脏数据块中只有一部分被写到磁盘上的数据文件中，还有一部分已经被置为提交标记的脏块还在内存上（脏页一部分在内存，一部分在刷新到磁盘上）
如果此时数据库实例崩溃了，则当数据库实例恢复时，就需要用前滚（这个机制）来完成事务的完全提交，即将先前那部分已经被置为提交标记且还在内存上的脏块写入到磁盘上的数据文件中。

3.2、回滚

   **未提交的事务**，即该事务**未被执行commit命令**。但是此时，该事务修改的脏块中也有可能一部分脏块写入到数据文件中了。<br />如果此时数据库实例崩溃了，则当数据库实例恢复时，就需要用**回滚（这个机制）来将先前那部分已经写入到数据文件的脏块从数据文件上撤销掉**。

3.3、实例恢复

数据库实例崩溃前最后一次检查点的那一刻到数据库实例崩溃那一刻期间所做的所有操作（无论该操作是否有提交的，这些操作可以从重做日志上读取）对该数据库实例对应的数据库（特别是数据文件部分做恢复，当然其他配合数据文件的文件，如控制文件，日志文件，也会做相关的恢复修改）先进行前滚，即将该期间的操作重做一遍。之后再将其中未提交的操作进行回滚。这里，可能就有人疑问了，为什么前滚时不只做提交的操作，未提交的操作就不要做就好了嘛？因为数据库实例崩溃前，未被执行commit命令的事务，其所修改的脏块中也有可能一部分脏块已经写入到数据文件中了，所以需要进行回滚操作。
总之，实例恢复时，先做前滚，后做回滚

参考链接

4、二进制日志bin log

所有的引擎通用，是属于mysql数据库的上层的

4.1、为什么有bin log

是一种逻辑日志
也被叫做归档日志。
会一直记录日志，默认最大容量是1GB，可以通过max_binlog_size参数修改，单个日志超过最大值，则会创建一个文件继续写。
一般会给日志文件设置过期时间（expire_logs_day）默认永久保存。
主要应用于MySQL主从模式，主从节点间的数据同步，以及基于时间点的数据还原。

4.2、记录内容

4.2.1、记录内容

记录了数据库所有DDL和DML操作，记录的是对应的SQL语句。
日志的内容格式是执行SQL命令的相反逻辑，这点和undo log 类似。
二进制日志是在事务完成后一次写入——一个事务只有一个日志

4.2.2、与redo log中记录对比

4.3、状态查看

SHOW VARIABLES LIKE ‘log_bin’

4.4、binlog的类型

binlog的格式有三种

4.4.1、ROW

日志中会记录成每一行数据被修改的情况，然后在slave端再对相同的数据进行修改。

优点：在row level情况下，bin-log中可以不记录执行的sql语句上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成说明样子。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解，而且不会出现某些特点情况下的存储过程或function，以及trigget的电泳和触发无法被正确复制的问题。

缺点：row level下，所有的执行语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如会产生大量的binlog日志。

4.4.2、STATEMENT（默认）

每一条被修改的数据的sql语句都会记录到master的binlog中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。
优点：解决了行模式的缺点，它不需要记录每一行数据的变化，减少binlog日志量，节约磁盘IO，提高性能，因为他只需要记录在Master上所执行的语句的细节，以及执行语句时候的上下文的信息。
缺点：由于它是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果，另外就是，由于MaSQL现在发展比较快，很多新功能不断的加入，使MySQL的复制遇到了不小的挑战。容易造成主从数据不一致。

4.4.3、MIXED

实际上就是钱两种模式的结合，在mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Staterment和row行模式之间选一种，当他认为数据可能会造成不一致的情况时，他就会选择行模式，当他认为这是一百万条记录只需要一条就搞定了，就会悬着选择statement模式

4.5、binlog的刷盘机制

mysql通过 sync_binlog 控制刷盘，取值范围0~n
0：不强制要求刷盘，由系统自行判断什么时候将binlog写入磁盘；
1：每次提交事务就将binlog写入磁盘；
n：每提交n个事务将binlog写入磁盘；

5、中继日志relay log

relay log日志文件与bin log日志文件具有相同的格式。主从复制的时候起到中转的作用，从库先从主库中读取二进制日志数据写入从库本地，后异步sql线程解析relay log为对应的sql命令执行

6、慢查询日志slow query log

• 慢查询日志，用来记录mysql执行时间超过指定时间的查询语句，通过该日志可以查找出来那些查询语句的执行效率低，耗时验证
• 出于性能考虑，只有在排查慢sql，调试参数时才会开启，默认情况下是关闭的

① 查看是否开启慢查询日志
SHOW VARIABLES LIKE ‘slow_query%’
② 查询指定时间—超过该时间则为慢查询
SHOW VARIABLES LIKE ‘long_query_time’
查询指定时间由参数long_query_time来设置，默认10，也就是10s
刚好等于慢查询日志设置时间的是不会被记录的。

7、查询日志general query log

• 记录用户的所有操作，包括客户端何时连接了服务器，客户端发送的所有Sql以及其他事件
• mysql服务器会按照它接收到语句的先后顺序写入日志文件
• 记录内容过于详细，开启后log文件的体量会很庞大，所有处于性能的考虑默认情况下该日志为关闭状态

查看是否开启查询日志
show variables like ‘general_log’

#开启查询日志并查询日志存放的位置
mysql> SET GLOBAL general_log=on;
Query OK, 0 rows affected

mysql> show variables like ‘general_log_file’;
+—————————+—————————————————————————-+
| Variable_name | Value |
+—————————+—————————————————————————-+
| general_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ.log |
+—————————+—————————————————————————-+

8、error log

错误日志，主要记录mysql服务器每次启动和停止的时间以及诊断和出错的信息.
默认情况下该日志功能是开启的。
mysql> SHOW VARIABLES LIKE ‘log_error’;
+———————-+————————————————————————————————+
| Variable_name | Value |
+———————-+————————————————————————————————+
| log_error | /usr/local/mysql/data/LAPTOP-UHQ6V8KP.err |
+———————-+————————————————————————————————+