MySQL七种日志

半塘 2023/8/31 数据库MySQL

# 1、重做日志（redo log）

# 1.1、存在的前提

redo log属于MySQL存储引擎InnoDB的事务日志
MySQL的数据是存放在磁盘中的，每次读写数据都需做磁盘IO操作，如果并发场景下性能就会很差。为此MySQL提供了一个优化手段，引入缓存Buffer Pool。这个缓存中包含了磁盘中部分数据页（page）的映射，以此来缓解数据库的磁盘压力。
当从数据库读数据时，首先从缓存中读取，如果缓存中没有，则从磁盘读取后放入缓存；当向数据库写入数据时，先向缓存写入，此时缓存中的数据页数据变更，这个数据页称为脏页，Buffer Pool中修改完数据后会按照设定的更新策略，定期刷到磁盘中，这个过程称为刷脏页。

# 1.2、MySQL宕机

如果刷脏页还未完成，可MySQL由于某些原因宕机重启，此时Buffer Pool中修改的数据还没有及时的刷到磁盘中，就会导致数据丢失，无法保证事务的持久性。
为了解决这个问题引入了redo log，redo Log如其名侧重于重做！它记录的是数据库中每个页的修改，而不是某一行或某几行修改成怎样，可以用来恢复提交后的物理数据页，且只能恢复到最后一次提交的位置。

redo log用到了WAL（Write-Ahead Logging）技术，这个技术的核心就在于修改记录前，一定要先写日志，并保证日志先落盘，才能算事务提交完成。

有了redo log再修改数据时，InnoDB引擎会把更新记录先写在redo log中，在修改Buffer Pool中的数据，当提交事务时，调用fsync把redo log刷入磁盘。至于缓存中更新的数据文件何时刷入磁盘，则由后台线程异步处理。
注意：此时redo log的事务状态是prepare，还未真正提交成功，要等bin log日志写入磁盘完成才会变更为commit，事务才算真正提交完成。
这样一来即使刷脏页之前MySQL意外宕机也没关系，只要在重启时解析redo log中的更改记录进行重放，重新刷盘即可。

# 1.3、redo log的大小

注意：redo log日志满了，在擦除之前，需要确保这些要被擦除记录对应在内存中的数据页都已经刷到磁盘中了。擦除旧记录腾出新空间这段期间，是不能再接收新的更新请求的，此刻MySQL的性能会下降。所以在并发量大的情况下，合理调整redo log的文件大小非常重要。

# 1.4、crash-safe**

因为redo log的存在使得InnoDB存储引擎有了crash-safe的能力，如果MYSQL宕机重启，系统会自动去检查redo log，将修改还未写入磁盘的数据从redo log恢复到MySQL中。 MySQL启动的时候，不管是正常启动还是宕机重启，总会进行恢复操作。先检查数据页中的LSN，如果这个LSN小于redo log中的LSN（也就是写入的位置），就说明redo log中的包含数据页未完成的操作，接着就会从最近的检查点出发，开始同步数据。
简单理解，比如：redo log的LSN是500，数据页的LSN是300，表明重启前有部分数据未完全刷入到磁盘中，那么系统则将redo log中LSN序号300到500的记录进行重放刷盘。

# 2、回滚日志（undo log）

# 2.1、存在前提

undo log也是属于MySQL存储引擎InnoDB的事务日志。
undo log属于逻辑日志，如其名主要起到回滚的作用，它是保证事务原子性的关键。记录的是数据修改前的状态，在数据修改的流程中，同时会记录一条与当前操作相反的逻辑日志到undo log中。
我们举个栗子：假如更新ID=1记录的name字段，name原始数据为小富，现改name为程序员内点事
事务执行update X set name = 程序员内点事 where id =1语句时，先会在undo log中记录一条相反逻辑的update X set name = 小富 where id =1记录，这样当某些原因导致服务异常事务失败，就可以借助undo log将数据回滚到事务执行前的状态，保证事务的完整性。

那可能有人会问：同一个事物内的一条记录被多次修改，那是不是每次都要把数据修改前的状态都写入undo log呢？
答案是不会的！undo log只负责记录事务开始前要修改数据的原始版本，当我们再次对这行数据进行修改，所产生的修改记录会写入到redo log，undo log负责完成回滚，redo log负责完成前滚。

# 2.2、回滚

未提交的事务，即事务未执行commit。但该事务内修改的脏页中，可能有一部分脏块已经刷盘。如果此时数据库实例宕机重启，就需要用回滚来将先前那部分已经刷盘的脏块从磁盘上撤销。

# 2.3、前滚

未完全提交的事务，即事务已经执行commit，但该事务内修改的脏页中只有一部分数据被刷盘，另外一部分还在buffer pool缓存上，如果此时数据库实例宕机重启，就需要用前滚来完成未完全提交的事务。将先前那部分由于宕机在内存上的未来得及刷盘数据，从redo log中恢复出来并刷入磁盘。
注意：在MySQL数据库更新的时候，undo log、redo log、bin log三种日志都是在刷脏页之前刷盘的，相互协作最大限度地保证了用户提交的数据不丢失。

# 3、二进制日志（bin log）

# 3.1、基本介绍

bin log是一种数据库Server层（和存储引擎无关），以二进制形式存储在磁盘中的逻辑日志。bin log记录了数据库所有的DDL和DML操作（不包括SELECT 和 SHOW）
bin log也叫做归档日志，因为他不像redo log一样循环写擦除之前的记录，而是会一直记录日志。如果单个日志文件大小超过 max_binlog_size，就会创建新的文件继续写入。
bin log日志的内容格式其实就是执行SQL命令的反向逻辑，（和undo log有点类似）。
一般来说，开启bin log都会给日志文件设置过期时间（expire_logs_days变量，单位是天）

# 3.2、基本代码演示

代码演示

# 查看和binary log有关的变量
 
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------------------------------------+
| Variable_name                   | Value                                                      |
+---------------------------------+------------------------------------------------------------+
| log_bin                         | ON                                                         |
| log_bin_basename                | D:\Program Files (x86)\MySQL\Data 8.0\Data\IU077-bin       |
| log_bin_index                   | D:\Program Files (x86)\MySQL\Data 8.0\Data\IU077-bin.index |
| log_bin_trust_function_creators | OFF                                                        |
| log_bin_use_v1_row_events       | OFF                                                        |
| sql_log_bin                     | ON                                                         |
+---------------------------------+------------------------------------------------------------+
 
# 查看已有的binary log
 
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| IU077-bin.000038 |       157 | No        |
| IU077-bin.000039 |      1400 | No        |
| IU077-bin.000040 |       157 | No        |
| IU077-bin.000041 |       333 | No        |
| IU077-bin.000042 |       157 | No        |
 
# 查看日志过期天数
 
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

# 3.3、主从同步

用户在主库master执行DDL和DML操作，修改记录顺序写入bin log;
从库slave的I/O线程连接上Master，并请求读取指定位置position的日志内容;
Master收到从库slave请求后，将指定位置position之后的日志内容，和主库bin log文件的名称以及在日志中的位置推送给从库;
slave的I/O线程接收到数据后，将接收到的日志内容依次写入到relay log文件最末端，并将读取到的主库bin log文件名和位置position记录到master-info文件中，以便在下一次读取用;
slave的SQL线程检测到relay log中内容更新后，读取日志并解析成可执行的SQL语句，这样就实现了主从库的数据一致;

# 3.4、使用bin log来还原数据

bin log和redo log的区别：

层次不同：redo log 是InnoDB存储引擎实现的，bin log 是MySQL的服务器层实现的，但MySQL数据库中的任何存储引擎对于数据库的更改都会产生bin log。
作用不同：redo log 用于碰撞恢复（crash recovery），保证MySQL宕机也不会影响持久性；bin log 用于时间点恢复（point-in-time recovery），保证服务器可以基于时间点恢复数据和主从复制。
内容不同：redo log 是物理日志，内容基于磁盘的页Page；bin log的内容是二进制，可以根据binlog_format参数自行设置。
写入方式不同：redo log 采用循环写的方式记录；binlog 通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上。
刷盘时机不同：bin log在事务提交时写入；redo log 在事务开始时即开始写入。
所以，bin log 与 redo log 功能并不冲突而是起到相辅相成的作用，需要二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

# 4、错误日志（error log）

错误日志主要记录MySQL服务器每次启动和停止的时间以及诊断和出错信息。
错误日志中记录的并非全部错误信息，也包括MySQL如何启动InnoDB的表空间文件、如何初始化自己的存储引擎，初始化buffer pool等，这些也记录在错误日志中。

代码演示

mysql> show variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| binlog_error_action        | ABORT_SERVER                           |
| log_error                  | .\IU077.err                            |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+

1
2
3
4
5
6
7
8
9
10

# 5、慢查询日志（slow query log）

慢查询日志（slow query log）: 用来记录在 MySQL 中执行时间超过指定时间的查询语句，在 SQL 优化过程中会经常使用到。通过慢查询日志，我们可以查找出哪些查询语句的执行效率低，耗时严重。
出于性能方面的考虑，一般只有在排查慢SQL、调试参数时才会开启，默认情况下，慢查询日志功能是关闭的。可以通过以下命令查看是否开启慢查询日志：

代码演示

# 慢查询日志开关
 
mysql> show variables like '%slow_query%';
+---------------------+----------------+
| Variable_name       | Value          |
+---------------------+----------------+
| slow_query_log      | ON             |
| slow_query_log_file | IU077-slow.log |
+---------------------+----------------+
 
# 超过多少时间才算慢查询语句
 
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 6、一般查询日志（general log）

一般查询日志（general query log）：用来记录用户的所有操作，包括客户端何时连接了服务器、客户端发送的所有SQL以及其他事件，比如 MySQL 服务启动和关闭等等。MySQL服务器会按照它接收到语句的先后顺序写入日志文件。
由于一般查询日志记录的内容过于详细，开启后 Log 文件的体量会非常庞大，所以出于对性能的考虑，默认情况下，该日志功能是关闭的，通常会在排查故障需获得详细日志的时候才会临时开启。

代码演示

# general log的开关
 
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
 
# general log的文件
 
mysql> show variables like '%general_log_file%';
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| general_log_file | IU077.log |
+------------------+-----------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 7、中继日志（relay log）

relay log日志文件和bin log日志文件格式相同，从上边的MySQL主从复制流程可以看到，relay log起到一个中转作用，slave先从主库master中读取二进制日志数据，写入从库本地，后续再一户由SQL线程读取解析relay log成对应的SQL命令。

参考：MySQL数据库七种日志介绍 (opens new window)

MySQL分区 B-Tree和B+Tree详解

星云-全栈知识体系

Choose mode

MySQL七种日志

MySQL七种日志

# 1、重做日志（redo log）

# 1.1、存在的前提

# 1.2、MySQL宕机

# 1.3、redo log的大小

# 1.4、crash-safe**

# 2、回滚日志（undo log）

# 2.1、存在前提

# 2.2、回滚

# 2.3、前滚

# 3、二进制日志（bin log）

# 3.1、基本介绍

# 3.2、基本代码演示

# 3.3、主从同步

# 3.4、使用bin log来还原数据

# 4、错误日志（error log）

# 5、慢查询日志（slow query log）

# 6、一般查询日志（general log）

# 7、中继日志（relay log）