MySQL好好学二（日志）

大纲

1、bin log日志
2、undo log日志
3、redo log日志

bin Log日志

bin log即binary log，二进制日志文件，是MySQL中比较重要的日志，和运维息息相关。它记录了所有更新数据库的语句（如DDL和 DML语句）并以二进制的形式保存在磁盘中，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。

1、主要作用：

数据恢复：MySQL可以通过bin log恢复某一时刻的误操作的数据，是DBA常打交道的日志。
数据复制：MySQL的数据备份、集群高可用、读写分离都是基于bin log的重放实现的。

2、记录格式

binlog日志有三种格式：statement、row和mixed，对比如下：

格式	含义	优点	缺点
statement	基于SQL语句的复制，记录的是更新数据操作的SQL语句，这些语句同步时会被其他节点执行，如update T set time=NOW() where id = 1;	不需要记录数据的变化，减少了bin log文件大小，减少IO负担。	SQL中包含了每次执行结果不一致的函数、触发器时，同步数据时会造成不一致。
row	基于行的复制，5.1.5版本支持的格式，记录了数据被更改的具体值，如update T set time=1687843346000 where id = 1;	日志会清晰记录每条数据被修改的详细情况，保证了数据的一致性。	每条数据的更改被详细记录，如整表删除，alter表等操作涉及的数据行都会记录，ROW格式会产生大量日志。
mixed	混合模式，5.1.8版本开始，以上两种格式的混合版，对于DDL只对SQL语句进行记录，对DML操作则会进行判断，如果判断会造成主从不一致，就会采用row格式记录，反之则用statement格式记录。	既节省空间，又提高数据库性能，保证数据同步时的一致性。	无法对误操作数据进行单独恢复。

3、写入时机

事务执行过程中，会先把日志写到binlog cache中去，事务提交时，才把binglog cache写到binlog文件中去（刷盘）。binlog cache是为了保证一个事务的所有操作能够一次性写入bin log不被拆开而设置的缓存，binlog cache大小受binlog_cache_size参数控制。

上图binlog cache写到bin log日志文件的过程包含了write和fsync两步操作：

write是把日志写到文件系统缓存中，这一步是系统为了提高文件IO效率；
fsync是把数据持久化到日志文件中去。

写入策略受sync_binlog参数控制，默认0。sync_binlog=0：表示每次提交事务都只write，由操作系统自行判断什么时候执行fsync。

优点：性能提升；

缺点：但是如果机器宕机，page cache里的bin log会丢失.

sync_binlog=N：表示每次提交事务都只write，积攒N个事务后才执行fsync。

优点：机器宕机只丢失N个事务的bin log；

缺点：如果N设置的很小可能会出现IO瓶颈，需要适当调整N的大小

4、两阶段提交

MySQL在执行更新操作的过程中，会记录两种日志：redo log和binlog，都是以事务为单位：

redo log是物理日志，记录内容是“在某个数据页上做了什么修改”，属于InnoDB存储引擎层，在事务过程中是不断写入的。
bin log 是逻辑日志，记录内容是语句的原始逻辑，属于Server层，只在事务提交时才写入

对于如下SQL：

update T set c = 1 where id = 2;

在执行过程中一直在写redo log，但是当事务提交时，才开始写bin log，如果此时MySQL系统崩溃，bin log还未来得及写入。

此时MySQL系统重启发现redo log有记录，主库通过redo log恢复了c值为1，而从库通过bin log同步数据之后c值为2，这就造成了主从数据同步的不一致，这是不允许的

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

两阶段提交：将redo log的写入操作拆成了两个步骤prepare和commit进行，在事务执行期间，写入的redo log标记为prepare阶段，待事务提交且bin log写入成功时，才将redo log标记为commit阶段。

写入bin log时发生异常

使用两阶段提交后，写入bin log时发生异常也不会有影响，因为MySQL在使用redo log恢复时，发下redo log还处于prepare阶段，而且此时没有bin log，认为该事务操作尚未完成提交，会回滚此操作.

redo log在commit阶段发生异常

虽然MySQL重启后发现redo log是处于prepare阶段，但是能通过事务id找到了对应的bin log记录，所以MySQL认为此事务执行是完整的，就会提交事务恢复数据

5、bin log 日志配置

-- 查看bin log日志开启情况
登录mysql服务器
mysql -uroot -p 密码
show variables like'%log_bin%';

-- 开启bin log 日志
修改MySQL的 my.cnf(linux) 或my.ini(windows)文件可以设置二进制日志的相关参数：
[mysqld]
#启用二进制日志
log-bin=SC-bin
binlog_expire_logs_seconds=600
max_binlog_size=100M 

-- 查看日志文件列表和大小
MySQL服务每重新启动一次 ，文件后缀的数字就会+1，如果日志长度超过了 max_binlog_size 
的上限（默认是1GB），也会创建一个新的日志文件
mysql> SHOW BINARY LOGS;
+----------------------------+-----------+-----------+
| Log_name                   | File_size | Encrypted |
+----------------------------+-----------+-----------+
| SC-202010081028-bin.000634 |       179 | No        |
| SC-202010081028-bin.000635 |       156 | No        |
+----------------------------+-----------+-----------+

-- 查看bin log记录的默认格式
show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

undo log日志

1、了解undo log日志

undo log叫做回滚日志或撤销日志，在事务执行变更操作之前需要先将相反的操作写入undo log，通过它可以让事务回滚操作，undo log也是实现多版本控制（MVCC）的基础。

redo log保证了事务的持久性，而undo log保证了事务的原子性和一致性。

为了保证事务的原子性，如果事务执行中突发异常，如数据库出错、操作系统宕机等，对数据库记录做变更时，需要记录下来：

新增一条记录，记录下主键，回滚时直接DELETE这个主键的内容;
删除一条记录，记录下被删记录的内容，回滚时可以将内容再插入表中；
修改一条记录，记录下修改之前的旧值，回滚时直接更新为旧值；

2、undo log作用

回滚数据：undo log记录了每个操作的逆操作，可以逻辑恢复数据（注意：类似git 操作，不是物理上的恢复，既数据结构和页可能变化了）；
MVCC：在InnoDB中MVCC的实现是通过undo log来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo log读取之前的行版本信息，以此实现非锁定读取。

3、undo log类型

在InnoDB中，undo log分为两种：

insert undo log：是指在insert操作中产生的undo log。因为insert操作的记录，只对当前事务本身可见，对其他事务不可见(这是事务隔离性的要求)，因此这种undo log可以在事务提交后直接删除。不需要进行purge操作。
undate undo log：是对delete和update操作产生的undo log。该undo log可能需要提供MVCC机制使用，因此不能在事务提交时就进行删除，提交时放入undo log链表，等待purge线程进行最后的删除。

4、undo log 生命周期

假设一张表有一个字段field，A记录的初始值为1，B记录的初始值为2，现在要将A的字段修改为3，B的字段修改为4：

1、start transaction;
2、记录A的field=1到undo log;
3、update T set field = 3;
4、记录A的field=3到redo log;
5、记录B的field=2到undo log;
6、update T set field = 4;
7、记录B的field=4到redo log;
8、将redo log刷新到磁盘;
9、commit;

在上面的1-8步骤的任意一步如果发生了系统宕机，此时事务未提交，该事务不会对磁盘上的数据做任何更改；

如果8-9步骤之间出现了宕机，此时redo log已刷盘，系统恢复之后可以选择使用undo log回滚，也可以选择提交事务；

如果9之后出现了系统宕机，Buffer Pool中的变更数据还未来得及刷回磁盘，在系统恢复之后根据redo log把数据刷回磁盘

5、详细生成过程

对于InnoDB来说，每条记录不仅包括了自身的数据，还包含了几个隐藏列：

DB_ROW_ID：InnoDB为没有主键和唯一索引的表自动添加的隐藏主键；
DB_TRX_ID：更改当前记录的事务id；
DB_ROLL_PTR：回滚指针，指向undo log的指针。

新增操作的undo log

start transaction;
insert into user(name) values('Tom');
commit;

此时行记录deletemark标记为0，表示该记录并未删除，回滚指针指向了回滚编号为0的回滚日志，回滚日志记录了主键信息，说明若要回滚操作可以通过执行delete这个主键实现。

不更新主键的undo log

start transaction;
update user set name = 'Sun' where id = 1;
commit;

此时执行了更新操作，并且更新的字段不是主键。此时记录的回滚指针指向了新生成的回滚编号为1的undo log，编号为0的undo log连接在编号为1的后面，当年记录回滚时也是先通过编号1的undo log恢复到name为'Tom'，再通过编号0的undo log删除记录。

更新主键的undo log

对于更新主键的操作，会先把原来的数据deletemark标识标记为1，这时并没有真正的删除数据，真正的删除会交给purge清理线程去判断，然后在后面插入一条新的记录，新的记录也会产生undo log，并且undo log的序号会递增。

此时的事务如何回滚？

通过undo no=3的日志把id=2的数据删除；
通过undo no=2的日志把id=1的数据的deletemark还原成0；
通过undo no=1的日志把id=1的数据的name还原成Tom ；
通过undo no=0的日志把id=1的数据删除.

删除操作的undo log

记录的删除操作分为两个阶段:

将记录的deletemark标示位设置为1，其他的不做修改(实际会修改记录的trx_id，roll_pointer等隐藏列的信息)。
当该删除语句所在的事务提交之后，undo purge线程来真正的把记录删除掉。就是把记录从正常记录链表移除，加入到垃圾连表中。

删除操的undo log只需要考虑对删除操作在阶段1所做的影响进行回滚，需要把该记录的trx_id和roll_pointer的隐藏列旧值都记到对应的undo log中的trx_id和roll_pointer属性中。可以通过删除操作的undo log的roll_pointer的属性找到上一次对该记录改动产生的undo log，以此来实现回滚。

6、purge线程的作用

清理undo页和清除page里带有Delete_Bit标识的记录。在InnoDB中，事务中的delete操作并不会立刻将数据删除，而是先进行Delete Mark标记，给记录标识上Delete_bit，真正的清除工作是由purge线程在后台完成的。

7、回滚段与事务

1、每个事务只会在一个回滚段中，一个回滚段可以服务多个事务，回滚段和事务是1:N的关系；

2、在事务过程中，当数据被更改，原始的数据会被复制到回滚段中；

3、回滚段存在于undo表空间中，在数据库中可以存在多个undo表空间，但同一时刻只能使用一个undo表空间；

mysql> show variables like 'innodb_undo_tablespaces';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_undo_tablespaces | 2     |
+-------------------------+-------+
1 row in set (0.02 sec)

4、当事务提交时，InnoDB存储引擎会做以下两件事情：

将undo log放入列表中，以供之后的purge操作；
判断undo log所在的页是否可以重用，若可以分配给下个事务使用

8、回滚段中的数据分类

1、未提交的回滚数据：该回滚数据关联的事务尚未提交，要用于实现MVCC，所以不能被删除和覆盖；
2、已提交但未过期的回滚数据：该回滚数据关联的事务已提交，但仍然受到undo retention参数的影响继续保留；
3、事务已提交并过期的数据：该回滚数据属于过期数据，当回滚段满之后，会被优先覆盖掉。

redo log日志

redo log也叫重做日志，是Innodb存储引擎生成的日志，记录的是物理级别上的页修改操作，为MySQL提供了崩溃恢复的能力，实现了事务的持久性。

InnoDB的事务采用了WAL技术（Write-Ahead Logging）既先写日志，再写磁盘。MySQL每执行一条DML语句，先将记录写入内存分配的redo log buffer中，后续再一次性将redo log buffer中的内容写到redo log文件中，而被修改的数据页由checkpoint机制保证最终落盘.

1、redo log 引入的原因

首先Innodb是以页为单位来进行磁盘IO，页的大小默认为16KB。在需要访问页时，要先将页从磁盘上缓存到内存中的Buffer Pool这个缓冲池中。所有数据的变更都是要在这个缓冲池中进行处理。

变更之后的页就形成了脏页，缓冲池会以一定的频率（checkPoint机制）将脏页刷新到磁盘。缓冲池的目的是有效减轻CPU和磁盘之间的速度差带来的性能影响。

但是由于脏页刷入磁盘不是每次变更后立即触发的，而是由master线程间隔一段时间去处理的，这就可能在事务提交之后刚写完缓冲区，此时MySQL宕机了，就会丢失这部分数据，这不符合事务的持久性要求。

如果在页变更后立即刷入磁盘又会影响性能，因为修改一个字节都会刷新16KB的数据到磁盘上，而且有些事务会涉及到多个页的数据，将这些页刷入磁盘需要进行多次随机IO操作是十分耗时的。

此时的解决方案就是先将变更的内容记录下来，例如：将第2页面中偏移量为50处的字节值由0变为1，此时可以记录为：将0号表空间的2页面的偏移量为50处的值更新为1。

2、redo log 的好处

降低刷盘频率：变更日志先行存入redo log buffer，脏页刷盘和redo log buffer刷盘时机可控；
日志占用空间小：存储表空间ID、页号、偏移量和需要更新的值。

3、刷盘策略

redo log的写入为了减少刷盘频率需要先写入内存的redo log buffer中，之后以一定的频率将buffer刷入到磁盘的redo log文件中去。

InnoDB 存储引擎为 redo log 的刷盘策略提供了三种策略，使用innodb_flush_log_at_trx_commit 参数配置，有以下三种取值：

取值	含义	优点	缺点
0	表示每次事务提交时不进行刷盘操作，由master线程每隔1s进行一次刷盘。	IO效率高于1，低于2（注：0的IO频率是1s一次，2的IO频率由OS决定）	数据库或操作系统宕机都会丢失1s数据，无法保证持久性
1	表示每次事务提交时都将进行刷盘操作（默认值）。	宕机不会丢失数据，保障了持久性	效率最差
2	表示每次事务提交时都只把redo log buffer内容写入操作系统的page cache，由OS决定什么时候同步到磁盘文件中。	IO效率最高，仅数据库宕机不会丢失数据	操作系统宕机会丢失1s数据，无法保证持久性

innodb_flush_log_at_trx_commit=0

事务过程中，redo log写入redo log buffer中，由master线程每隔1s调用fsync操作将buffer中的内容写到操作系统的page cache中。MySQL宕机会造成1s的事务丢失

innodb_flush_log_at_trx_commit=1

事务提交时，主动刷盘redo log buffer的内容立刻同步到磁盘文件中，操作系统宕机不会丢失数据；
事务未提交时，操作系统宕机redo log buffer丢失也不会有损失，因为事务未提交不会持久化。

innodb_flush_log_at_trx_commit=2

事务提交就将redo log buffer中的内容写入page cache缓存，由操作系统决定什么时候将page cache的内容写入磁盘。此时MySQL宕机并不会丢失数据，但是操作系统宕机会丢失这1s的数据。

4、日志组文件

redo log文件不止一个，而是以文件组的形式出现，文件大小其实就等于所有redo log文件大小之和。它们采用循环链表的方式向redo log日志文件组写入数据。

在整个日志文件组中还有两个重要的属性：

write pos是当前记录的位置，一边写一边后移；
checkpoint是当前要擦除的位置，也是往后推移。

每次刷盘redo log记录到日志文件组中，write pos位置就会后移更新。

每次MySQL通过日志文件组恢复数据之后，这部分日志不再需要，会清空加载过的redo log记录，并把checkpoint后移更新。

write pos和checkpoint之间为空闲部分可以用来写入新的redo log记录。

当redo log组被写满时，既write pos追上了checkpoint，此时不能再写入新的redo log，需要MySQL清空一些记录，留出新的空间。

5、redo log日志配置

-- 日志缓存区大小
默认16M，最大4096M，最小值为1M
mysql> show variables like'%innodb_log_buffer_size%';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| innodb_log_buffer_size | 1048576 |
+------------------------+---------+
1 row in set (0.02 sec)  


-- 文件位置
默认值为.\，表示在数据库的数据目录（见配置datadir = C:\DevTools\mysql-5.7\data）下。
mysql> show variables like'%innodb_log_group_home_dir%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_group_home_dir | .\    |
+---------------------------+-------+
1 row in set (0.02 sec) 

-- 文件数量
默认数量为2，命名方式如下：ib_logfile0，ib_logfile1
ysql> show variables like'innodb_log_files_in_group';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+
1 row in set (0.02 sec) 

-- 日志文件大小
单个redo log文件设置大小，默认值为48M，最大值为512G既（innodb_log_files_in_group*innodb_log_file_size）不能大于512G
mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
1 row in set (0.03 sec) 


-- 刷盘策略
mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.03 sec)