MySQL中都有哪些日志文件？

拼搏现实的明天。 2022-08-30 01:43 327阅读 0赞

### 一、序言 ###

首先看一张图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hzZjE1NzY4NjE1Mjg0_size_16_color_FFFFFF_t_70_pic_center]  
上面是MySQL简化版的体系结构图，可以看到自顶向下主要分成了链接池、SQL接口、解析器、查询优化器、缓存、执行引擎、系统文件和日志文件。系统文件和日志文件共同组成了系统文件层，是Mysql实现数据持久化、MVVC、主从同步等功能的重要一层。

Mysql中存在很多不同的日志，例如：错误日志、通用查询日志、二进制日志（Binlog）、Undo/Redo Log、中继日志（Relay Log）、慢查询日志等。

### 二、错误日志 ###

记录MySQL 运行过程中较为严重的警告和错误信息，以及MySQL每次启动和关闭的详细信息，默认是开启的，可以通过`show variables like '%log_error%'`查看。  
![在这里插入图片描述][20210718121047400.png]  
其中log\_error是定义是否启用错误日志的功能和错误日志的存储位置，可以修改/etc/my.cnf配置文件，添加参数`log_error=/home/keduw/mysql/mysql.err`指定错误日志的路径。

### 三、通用查询日志 ###

用来记录用户的所有操作，包括启动和关闭 MySQL 服务、更新语句和查询语句等，默认情况下是关闭，可以通过`show variables like '%general%'`查看。  
![在这里插入图片描述][20210718120904867.png]  
一般不开启，会占用大量的磁盘空间。

### 四、二进制日志（Binlog） ###

记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行时长；但是它不记录select、show等不修改数据库的SQL。默认是关闭，可以通过`show variables like '%log_bin%'`查看是否开启，`show binary logs`查看日志文件，`show variables like '%binlog%'`查看参数配置。  
![在这里插入图片描述][2021071812112684.png]  
Binlog有两个重要的使用场景：数据库恢复和主从复制，当需要使用对应功能需要开启Binlog日志。可以修改my.cnf或my.ini配置文件，在\[mysqld\]下面增加`log_bin=mysql_bin_log`启动。

主从复制的时候，在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。数据恢复，可以通过mysqlbinlog工具来恢复数据。

Binlog有三种文件记录模式：STATEMENT、ROW和MIXED。

**STATEMENT**：操作的是执行语句，每一条修改数据的sql都会记录到master的Binlog中，slave在复制的时候会解析成sql语句再次执行，简称sql语句复制。

> 优点：日志量小，减少磁盘IO，提升存储和恢复速度  
> 缺点：在某些情况下会导致主从数据不一致，比如last\_insert\_id()、now()等函数。

**ROW**：操作的是具体的一行数据。日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

> 优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。  
> 缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。

**MIXED**：以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，Mysql会根据执行的sql语句选择写入模式。

### 五、Undo/Redo Log ###

Undo/Redo Log并不属于Mysql Server的日志，而是属于执行引擎Innodb的日志。

#### 5.1 Undo Log ####

Undo意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作，Undo Log在事务开始前产生。事务在提交时，并不会立刻删除日志，Innodb会将该事务对应的Undo Log放入到删除列表中，通过后台线程进行回收处理。

数据库事务开始之前，会将要修改的记录存放到Undo Log里，当事务回滚时，可以利用Undo Log，撤销未提交的事务。因此依赖Undo Log可以实现事务的原子性和MVCC（多版本并发控制）。

**原子性**：事务处理过程中，如果出现了错误或者用户执行了ROLLBACK语句，MySQL可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

**MVCC**：事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log中的数据可作为数据旧版本快照供其他并发事务进行快照读。事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hzZjE1NzY4NjE1Mjg0_size_16_color_FFFFFF_t_70]

#### 5.2 Redo Log ####

Redo意为重做，以恢复操作为目的。Redo Log 是为了实现事务的持久性而出现的产物，在事务执行的过程中如果发生异常（比如：数据库崩溃），在重启MySQL服务的时候，根据Redo Log进行重，进而恢复事务的状态。

Undo Log是在事务开启的时候产生，而Redo Log是在事务执行的过程产生。在事务提交时会将产生Redo Log写入Log Buffer，但并不是随着事务的提交就立刻写入磁盘，而是等到事务操作的脏页写入到磁盘之后，Redo Log占用的空间就可以重用（被覆盖写入）。

Redo Log文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hzZjE1NzY4NjE1Mjg0_size_16_color_FFFFFF_t_70_pic_center 1]  
如上图所示，Redo Log采用双指针进行维护。Write Pos是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到0号文件开头；CheckPoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；

Write Pos和CheckPoint之间还空着的部分，可以用来记录新的操作。如果Write Pos追上CheckPoint，表示写满，这时候不能再执行新的更新，会停下来先擦掉一些记录。如图：W区则是可以写入的区域，R区则是需要刷盘的内容。

#### 5.3 Redo Log相关配置参数 ####

Redo Buffer持久化有三种策略，可通过Innodb\_flush\_log\_at\_trx\_commit设置，默认是配置1。

> 0：每秒提交Redo buffer ->OS cache -> flush cache to disk，由后台Master线程每隔 1秒执行一次操作，可能丢失一秒内的事务数据。  
> 1：每次事务提交都会执行Redo buffer ->OS cache -> flush cache to disk，最安全，但是性能最差。  
> 2：每次事务提交执行Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OS cache -> flush cache to disk的操作。

一般建议选择取值2，因为MySQL挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据。

### 六、中继日志（Relay Log） ###

中继日志主要是MySQL主从同步的时候会用到，下图是主从同步的原理图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hzZjE1NzY4NjE1Mjg0_size_16_color_FFFFFF_t_70 1]  
主从同步主要分成三步：  
1：主库将数据库的变更操作记录到Binlog日志文件中  
2：从库读取主库中的Binlog日志文件信息写入到从库的Relay Log中继日志中  
3：从库读取中继日志信息在从库中进行Replay，更新从库数据信息

可以看到Relay Log中继日志在从库中担当类似中转的作用。可能会觉得从库读取主库中的Binlog日志为什么不直接执行，而是先写入到Relay Log后面由读出来。

看似多余，其实不然。其实我觉得这里还是出于性能和异常的考虑，Replay的操作要比直接文件写入慢得多，毕竟中间还要经过执行引擎的处理，而且如果从库出现异常，有Relay Log做持久化也可以确保从库恢复的时候数据的完整性。

### 七、慢查询日志 ###

记录所有执行时间超时的查询SQL，默认是10秒，方便于查询缓慢的定位和分析。可以通过`show variables like '%slow_query%'`查看是否开启和日志的位置，`show variables like '%long_query_time%'`查看慢查询的阈值。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hzZjE1NzY4NjE1Mjg0_size_16_color_FFFFFF_t_70 2]

### 八、总结 ###

以上整理了关于MySQL中存在的日志，其实日志存在意义在于定位和记录，其中包括MySQL Server本身的日志，也有Innodb自己的日志，主要前阵子跟一个朋友在讨论这个知识点，感觉有必要做个记录。如果你对文章有什么疑问或者觉得文章有什么问题，欢迎留言/私信交流~

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hzZjE1NzY4NjE1Mjg0_size_16_color_FFFFFF_t_70_pic_center]: /images/20220829/67ffed0fe64d42d6b557b686e2a7187b.png
[20210718121047400.png]: /images/20220829/82af0395ff4049fcb440af4074a8645f.png
[20210718120904867.png]: /images/20220829/5ab96e87b58546c09e78d73823c8a2a1.png
[2021071812112684.png]: /images/20220829/2f8db84924b64fcb97abcbe1faa8b423.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hzZjE1NzY4NjE1Mjg0_size_16_color_FFFFFF_t_70]: /images/20220829/65df322f76db4368a279b1748cf6bd67.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hzZjE1NzY4NjE1Mjg0_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20220829/b0db8429e7b44a6fb7190cded5941a6f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hzZjE1NzY4NjE1Mjg0_size_16_color_FFFFFF_t_70 1]: /images/20220829/541eaaf45c35471c95cb879f7430757f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hzZjE1NzY4NjE1Mjg0_size_16_color_FFFFFF_t_70 2]: /images/20220829/71ac1d08530e4beab28453e548408477.png