一文搞懂 MySQL 日志

前言

MySQL 的日志记录了运行的各种信息，是 MySQL 事务、性能、数据容灾、异常排查等的基础。本文将介绍 MySQL 一些关键日志的作用和原理。

---

MySQL InnoDB 引擎重要的三个日志：

日志	说明
redo log	重做日志，保证事务的持久性
undo log	回滚日志，来保证事务的原子性
binlog	MySQL 的主从复制中同步数据

一、binlog

1. 简介

概述

binlog记录DDL 和 DML语句，但不包括SELECT、SHOW 等语句，简单说只要发上了表结构变化或表数据更新，都会产生binlog日志。

特点

undo log是二进制逻辑日志，记录内容是语句的原始逻辑，属于Server层，和引擎无关。只在事务提交时才写入，适用于数据备份和主从复制。

作用

1. 灾难时的数据恢复；
2. MySQL 的主从复制。

所在位置

通常默认的MySQL数据目录为/var/lib/mysql。

2. 记录格式

日志格式	记录内容
Statement	记录进行数据修改 SQL 语句。
Row	记录每一行的数据变更，占用较多空间。（默认）
Mixed	前两者混合，判断是否可能引起数据不一致：可能则用Row 否则用Statement

3. 写入机制

事务执行过程中，先把日志写到binlog cache。
事务提交的时候，再把binlog cache写到binlog文件中。

binlog cache是为了保证一个事务的所有操作能够不被拆开，一次性写入bin log。
binlog cache大小受binlog_cache_size参数控制。
binlog cache写入策略受sync_binlog参数控制。

4. 日志操作命令

4.1 查看启动情况

show variables like'%log_bin%';

4.2 日志查看

命令

日志是二进制存储的，无法直接读取，需要通过mysqlbinlog命令查看。

语法

mysqlbinlog [参数选项] logfilename

选项含义

• -d：指定数据库名称，只列出指定的数据库相关操作。；
• -o：忽略掉日志中的前n行命令；
• -v：将行事件（数据变更）重构为SQL语句；
• -w：将行事件（数据变更）重构为SQL语句，并输出注样信息；

4.3 日志删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

指令	含义
reset master	删除全部日志
purge master logs to 'binlog.xxx'	删除xxx编号之前的日志
purge master logs before 'yyyy-mm-dd hh:mm:ss'	删除引号时间之前产生的日志
show variables like '%binlog_expire_logs_seconds%';	配置日志过期时间，到期自动删除

二、redo log

1. 简介

概述

redo log，重做日志，记录的是事务提交时数据页的物理修改。

特点

物理日志，InnoDB存储引擎独有的，保证数据的持久性与完整性。记录内容是“在某个数据页上做了什么修改”，在事务过程中是不断写入。
大小是固定的，前面的内容会被覆盖。

2. 写入机制

1. 当客户端提交数据修改时，会先去Buffer Pool获取数据，若没有则查询出来放入Buffer Pool；

2. 生成redo log放入Redolog Buffer，记录数据页的物理变化，此时redo log的状态是prepare；

3. 事务提交后，将Redolog Buffer中的redo log刷新到磁盘持久化存储，此时redo log的状态是commit。

这样即使Buffer Pool中的脏页刷新到磁盘时出错，恢复时也可以通过redo log日志进行重新刷新。

脏页：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。

WAL：先写日志，再写磁盘的思想，叫做WAL(Write Ahead Logging)。

3. 对比 binlog

对比维度	redo log	bin log
日志类型	物理日志	二进制逻辑日志
写入时机	事务过程中是不断写入	只在事务提交时才写入
位置	InnoDB 磁盘中	MySQL 的 Server 层
空间	固定空间，超出则覆盖	追加写入，可生成多份文件

4. 两阶段提交

了解了上面的binlog和redo log以后，你会发现， MySQL在执行更新操作的过程中，一次事务的完成均会记录着两个文件，区别见上面的对比表格。那么问题来了，两个文件到底是哪个先存？以及写入的时机有什么不同？

回答这两个问题之前，需要先考虑另外一个问题，这两个文件能否各存各的，会出问题吗？

答案是：不可以，会出现两个文件中数据不一致的问题，可能导致主从数据库数据不一致。

根据redo log的特点，在事务过程中是不断写入，而binlog只在事务提交时才写入，如果我们对某条数据执行了age 更改为 18的操作，此时原 age 为 17，redo log已经写入了数据，而undolog还没写入之前数据库崩溃了。

紧接着数据库重启后进行恢复，主数据库根据redo log恢复数据为age = 18，而从数据库根据binlog日志进行同步age = 17，这时就出现了不一致问题。

接着我们回答一下开始的两个问题，为了避免上述问题的产生，InnoDB存储引擎使用两阶段提交方案：

1. 生成redo log放入Redolog Buffer，记录数据页的物理变化，此时redo log的状态是prepare；

2. 事务提交后，并且，binlog写入成功后，再将Redolog Buffer中的redo log刷新到磁盘持久化存储，此时redo log的状态commit；

3. 进行数据恢复时，若redo log的状态是prepare，则有两种情况：

   1. binlog为空则进行数据回滚；
   2. binlog不为空，代表事务已commit，进行数据恢复，这个一般发生在binlog写入成功，但是redo log更改状态失败时。

三、undo log

1. 简介

概述

undo log，回滚日志，事务执行时，用于记录数据被修改前的信息，在异常发生时，会对已经执行的操作进行回滚。

作用

1. 异常回滚，保证事务的原子性；
2. 版本链用于MVCC机制中；

特点

undo log是逻辑日志，可以认为：

1. 当delete一条数据时，它会插入一条对应的insert记录；
2. 当update一条记录时，它会插入一条对象相反的记录。

当执行回滚时，就可以读取其中的记录进行操作。

分类

1. 新增时 ： 指在insert中产生的日志。这样的记录只对事务本身可见，对其他事务不可见，故可以在事务提交后直接删除。
2. 修改时：update或delete中产生的日志。该日志可能要作用于MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log版本链，使用后删除。

2. 版本链

不同事务或者相同事务对同一条记录进行修改，会使该记录的undo log生成一条记录版本的链表，链表头部是最新的旧记录，链表尾部是最早的旧记录。

隐藏字段	含义
DB_TRX_ID	表示最后一次插入或修改该行的事务 ID
DB_ROLL_PTR	回滚指针，指向该行的 undo log，若该行未被更新，则为空

上述事务能够看到的版本链上的哪条历史数据，是由MVCC的ReadView来决定。

四、错误日志

最重要的日志之一，记录了当mysqld.log启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息，当数据库出现故障无法使用时，建议先看此日志。

日志默认打开，默认存放目录/var/log/，默认文件名mysqld.log。

如果找不到，可执行show variables like '%log_error%'查看。

五、查询日志

该日志记录了客户端所有的操作语句，默认关闭，开启需做以下配置：

1. 修改/etc/my.cnf文件；
2. 设置general_log = 1，1 表示开启，0 表示关闭；
3. 设置日志的文件名，general_log_file = mysql_query.log，未指定默认为host_name.log。

六、慢查询日志

该日志记录了所有执行时间超过参数long_query_time，且所记录数不小于min_examined_row_limit的所有 SQL 语句。默认关闭，开启需以下配置（根据所需）：

1. 修改/etc/my.cnf文件；
2. 设置show_query_log = 1，1 表示开启，0 表示关闭；
3. 设置long_query_time = 2，未指定默认为 10 秒；
4. 设置long_show_admin_statements = 1，开启记录执行慢的管理语句；
5. 设置long_queries_not_using_indexes = 1，开启记录执行较慢且未使用索引的语句；

---

参考

[1] B 站. 黑马邓老师. MySQL数据库入门到精通.