认真学习MySQL中的那些日志文件-通用查询日志&错误日志

前面我们学习了MySQL的两种事务日志：重做日志、回滚日志。MySQL8.0下如下图所示：

下面我们学习一下MySQL的其他日志，为什么要重视日志呢？对于线上数据库应用系统，突然遭遇数据库宕机怎么办？在这种情况下，定位宕机的原因就非常关键，我们可以查看数据库的错误日志。日志中记录了数据库运行中的诊断信息，包括了错误、警告和注释信息。比如：从日志中发现某个连接中的SQL操作发生了死循环，导致内存不足，被系统强行终止了。明确了原因，处理起来也就轻松了，系统很快就恢复了运行。

除了发现错误，日志在数据复制、数据恢复、操作审计以及确保数据的永久性和一致性等方面，都有着不可替代的作用。

MySQL8.0官网日志地址：https://dev.mysql.com/doc/refman/8.0/en/server-logs.html

【1】MySQL支持的日志

① 日志类型

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为二进制日志、错误日志、通用查询日志和慢查询日志，这也是常用的4种。MySQL8又新增两种支持的日志：中继日志和数据定义语句日志。使用这些日志文件，可以查看MySQL内部发生的事情。

这6类日志分别为：

• 慢查询日志：记录所有执行时间超过 long_query_time的所有查询，方便我们对查询进行优化。
• 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
• 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。
• 二进制日志：记录所有更改数据的语句（没有查询），可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
• 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
• 数据定义语句日志：记录数据定义语句执行的元数据操作。

除二进制日志外，其他日志都是文本文件。默认情况下，所有日志创建于MySQL数据目录中。

关于慢查询日志可以参考博文：MySQL调优之慢查询日志应用

② 日志的弊端

日志功能会降低MySQL数据库的性能。例如，在查询非常频繁的MySQL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL数据库会花费很多时间记录日志。

日志会占用大量的磁盘空间。对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大。

【2】通用查询日志（general query log）

通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务器、所有用户的连接开始时间和截止时间、发给MySQL数据库服务器的所有SQL指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。

① 问题场景举例

在电商系统中，购买商品并且使用微信支付完成以后，却发现支付中心的记录并没有新增。此时用户再次使用支付宝支付，就会出现重复支付的问题。但是当去数据库中查询的时候，会发现只有一条记录存在，那么此时给到的现象就是只有一条支付记录，但是用户却支付了两次。

我们队系统进行了仔细检查，没有发现数据问题，因为用户编号和订单编号以及第三方流水号都是对的。可是用户确实支付了两次，这个时候我们就可以检查通用日志，看看当天到底发生了什么。

查看之后，发现：1月1日下午2点，用户使用微信支付完以后，但是由于网络故障，支付中心没有及时收到微信支付的回调通知，导致当时没有写入数据。1月1日下午2点30，用户又使用支付宝支付，此时记录更新到支付中心。1月1日晚上9点，微信的回调通知过来了，但是支付中心已经存在支付宝的记录，所以只能覆盖记录了。

由于网络的原因导致了重复支付，至于解决问题的方案就很多了。这里我们可以看到通用查询日志可以帮助我们了解操作发生的具体时间和操作的细节，对找出异常发生的原因极其关键。

② 查看当前状态

mysql> show VARIABLES like '%general%';
+------------------+------------------------------------+
| Variable_name    | Value                              |
+------------------+------------------------------------+
| general_log      | OFF                                |
| general_log_file | /var/lib/mysql/VM-24-14-centos.log |
+------------------+------------------------------------+
2 rows in set (0.28 sec)

说明1 ： 系统变量general_log 的值是OFF，即通用查询日志处于关闭状态。在MySQL中，这个参数的默认值是关闭的。因为一旦开启记录通用查询日志，MySQL会记录所有的连接起止和相关的SQL操作，这样会消耗系统资源并且占用磁盘空间。我们可以通过手动修改变量的值，在需要的时候开启日志。

说明2 ：通用查询日志文件的名称是VM-24-14-centos.log ，存储路径是/var/lib/mysql/，默认也是数据路径。

③ 启动日志

方式1：永久性方式

修改my.cnf 或 my.ini配置文件来设置，在[mysqld]组下加入log选项，并重启服务，格式如下：

[mysqld]
general_log=ON
general_log_file=/var/lib/mysql/VM-24-14-centos.log # 这里可自定义

如果不指定 目录和文件名，通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中，hostname表示主机名。

---

方式2：临时性方式

SET GLOBAL general_log=on;#开启通用查询日志
SET GLOBAL general_log_file='path/filename';#设置日志文件保存位置

对应的，关闭操作SQL命令如下：

SET GLOBAL general_log=off; #关闭通用查询日志

④ 查看日志

通用查询日志文件内容实例如下所示：

# 查看通用查询日志，使用其他命令more cat也可
vim /var/lib/mysql/VM-24-14-centos.log

通过通用查询日志，可以了解用户对MySQL进行的操作。比如，MySQL启动信息和用户root连接服务器和执行查询表的记录。在通用查询日志里面，我们可以清楚地看到，什么时候开启了新的客户端登录数据库，登录之后做了什么SQL操作，针对的是哪个数据表等信息。

⑤ 停止日志

方式1：永久性方式

修改my.cnf 或 my.ini文件，把[mysqld]组下的 general_log 值设置为OFF 或者将其注释掉。修改保存后，再重启MySQL服务即可生效。

[mysqld]
general_log=OFF

[mysqld]
#general_log=ON

方式2：临时性方式
使用SET语句停止MySQL通用查询日志功能：

SET GLOBAL general_log=off;

⑥ 删除/刷新日志

如果数据的使用非常频繁，那么通用查询日志会占用服务器非常大的磁盘空间。数据管理员可以删除很长时间之前的查询日志，以保证MySQL服务器上的硬盘空间。

手动删除文件

show VARIABLES like '%general%'

通过上述命令查找到日志文件所在位置，然后手动刷新。

刷新文件

使用如下命令重新生成查询日志文件，刷新MySQL数据目录会发现创建了新的日志文件（前提必须保证日志是打开状态）。

mysqladmin -uroot -p flush-logs

如果希望备份旧的通用查询日志，就先将旧的复制出来或者改名，然后执行上面的mysqladmin命令。

【3】错误日志

错误日志记录了MySQL服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。

通过错误日志可以查看系统的运行状态，便于及时发现故障、修复故障。如果MySQL服务出现异常，错误日志是发现问题、解决故障的首选。

① 启动日志

在MySQL数据库中，错误日志功能是默认开启的。而且错误日志无法被禁止。

默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为mysqld.log(linux系统)或hostname.err。如果需要定制文件名，则需要在my.cnf 或者 my.ini中做如下配置。

[mysqld]
log_error=[path/[filename]] #path为日志文件所在的目录路径，filename为日志文件名

修改配置项后，需要重启MySQL服务以生效。

查看MySQL数据目录

mysql> show VARIABLES like '%datadir%'
    -> ;
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

② 查看日志

查看错误日志文件

mysql> show VARIABLES like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| binlog_error_action        | ABORT_SERVER                           |
| log_error                  | /var/log/mysql/mysqld.log              |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
5 rows in set (0.00 sec)

MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看。如下是Windows下错误日志文件实例：

③ 删除/刷新日志

对于很久以前的错误日志，数据库管理员查看这些错误日志的可能性不大，可以将这些错误日志删除，以保证MySQL服务器上的硬盘空间。MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以直接删除。

命令删除：

rm -f /var/log/mysql/mysqld.log    

在运行状态下删除错误日志文件后，MySQL并不会自动创建日志文件。

重命名文件：

mv /var/log/mysql/mysqld.log /var/log/mysql/mysqld.log.old 

重建日志：

# 本文环境 错误日志为/var/log/mysql/mysqld.log
install -omysql -gmysql -m0644 /dev/null /var/log/mysql/mysqld.log
mysqladmin -uroot -p flush-logs

flush-logs指令操作：

• MySQL5.5.7以前的版本，flush-logs将错误日志文件重命名为filename.err_olg，并创建新的日志文件
• 从MySQL5.5.7开始，flush-logs只是重新打开日志文件，并不做日志备份和创建的操作

④ MySQL8.0新特性

MySQL8.0里的错误日志可以理解为一个全新的日志，在这个版本里，接受了来自社区的广泛批评意见，在这些意见和建议的基础上生成了新的日志。

下面这些事来自社区的意见：

• 默认情况下内容过于冗长
• 遗漏 了有用的信息
• 难以过滤某些信息
• 没有标识错误信息的子系统源
• 没有错误代码，解析消息需要识别错误
• 引导消息可能会丢失
• 固定格式

针对这些意见，MySQL做了如下改变：

• 采用组件架构，通过不同的组件执行日志的写入和过滤功能
• 写入错误日志的全部信息都具有唯一的错误代码，从10000开始
• 增加了一个新的消息分类《system》用于在错误日志中始终可见的非错误但服务器状态更改事件的消息
• 增加了额外的附加信息，例如关机时的版本信息，谁发起的关机等
• 两种过滤方式：internal和dragnet
• 三种写入形式：经典、JSON和syseventlog

通常情况下，管理员不需要查看错误日志。但是，MySQL服务器发生异常时，管理员可以从错误日志中找到发生异常的时间、原因，然后根据这些信息来解决异常。