MySQL其它数据库日志

第17章_其它数据库日志

我们在讲解数据库事务时，讲过两种日志：重做曰志、回滚日志

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

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

**千万不要小看日志。**很多看似奇怪的问题，答案往往就藏在日志里。很多情况下，只有通过查看日志才能发现问题的原因，真正解决问题。所以，一定要学会查看日志，养成检查日志的习惯，对提升你的数据库应用开发能力至关重要。

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

MySQL支持的日志

日志类型

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

这6类日志分别为：

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

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

日志的弊端

• 日志功能会降低MySQL数据库的性能。例如，在查询非常频繁的MySQL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL数据库会花费很多时间记录日志。
• 日志会占用大量的磁盘空间。对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大。

慢查询日志(slow query log)

前面章节《第09章_性能分析工具的使用》已经详细讲述。

通用查询日志(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/localhost.log | # 通用查询日志的文件名
+------------------+------------------------------+
2 rows in set (0.02 sec)

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

说明2：通用查询日志文件的名称是localhost.log。存储路径是/var/lib/mysql/，默认也是数据路径。这样我们就知道在哪里可以查看通用查询日志的内容了。

启动日志

方式1：永久性方式

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

[mysqld]
general_log=ON
genelal_log_file=[path[filename]]#日志文件所在目录路径，filename为日志文件名

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

方式2：临时性方式

SET GLOBAL general_log=on;#开启通用查询日志

SET GLOBAL general_log_file='path/filename'; #设置日志文件保存位置

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

SET GLOBAL general_log=off;

查看设置后的情况：

SET GLOBAL general_log_file='path/filename'; #设置日志文件保存位置

查看日志

通用查询曰志是以文本文件的形式存储在文件系统中的，可以使用文本编辑器直接打开日志文件。每台MySQL服务器的通用查商日志内容是不同的。

• 在Windows操作系统中，使用文本文件查看器；
• 在Linux系统中，可以使用vi工具或者gedit工具查看；
• 在Mac OSx系统中，可以使用文本文件查看器或者vi等工具查看。

从SHOW VARIABLES LIKE 'general_log%';结果中可以看到通用查询日志的位置。

通过通用查询日志，可以了解用户对MySQL进行的操作。比如，MySQL启动信息和用户root连接服务器和执行查询表的记录。

/usr/sbin/mysqld, Version: 8.0.28 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2022-04-11T11:21:43.407259Z        86 Query     SHow VARIABLES LIKE '%general%'
2022-04-11T11:21:51.815051Z        86 Quit
2022-04-11T11:23:27.470332Z        87 Connect   root@localhost on  using Socket
2022-04-11T11:23:27.470939Z        87 Query     select @@version_comment limit 1
2022-04-11T11:23:31.770713Z        87 Query     SET GLOBAL general_log=on
2022-04-11T11:23:34.351656Z        87 Query     SHow VARIABLES LIKE '%general%'
2022-04-11T11:23:42.545215Z        87 Query     SHOW DATABASES
2022-04-11T11:23:54.051197Z        87 Query     SELECT DATABASE()
2022-04-11T11:23:54.051463Z        87 Init DB   atguigudb3
2022-04-11T11:23:54.053621Z        87 Query     show databases
2022-04-11T11:23:54.054331Z        87 Query     show tables
2022-04-11T11:23:54.060986Z        87 Field List        account
2022-04-11T11:23:54.061316Z        87 Field List        class_comment
2022-04-11T11:23:54.062038Z        87 Field List        class_comment1
2022-04-11T11:23:54.062537Z        87 Field List        mylock
2022-04-11T11:23:54.062991Z        87 Field List        student
2022-04-11T11:23:54.063433Z        87 Field List        t1
2022-04-11T11:23:54.063838Z        87 Field List        t2
2022-04-11T11:23:54.064440Z        87 Field List        teacher
2022-04-11T11:23:54.064813Z        87 Field List        test1
2022-04-11T11:23:54.065399Z        87 Field List        test2
2022-04-11T11:23:54.065893Z        87 Field List        test_load
2022-04-11T11:23:54.066478Z        87 Field List        user1
2022-04-11T11:23:54.066936Z        87 Field List        user3
2022-04-11T11:24:15.732440Z        87 Query     select * from student

停止日志

方式1：永久性方式

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

举例1：

[mysqld]
general_log=OFF

举例2：

[mysqld]
general_log=ON

方式2：临时性方式

使用SET语句停止MySQL通用查询日志功能：

SET GLOBAL general_log=off;

查询通用日志功能：

SHOW VARIABLES LIKE 'general_log%';

删除\刷新日志

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

手动删除文件

SHOW VARIABLES LIKE 'general_log%';

可以看出，通用查询日志的目录默认为MySQL数据目录。在该目录下手动删除通用查询日志localhost.log。

使用如下命令重新生成查询日志文件，具体命令如下。刷新MySQL数据目录，发现创建了新的日志文件。前提一定要开启通用日志。

mysqladmin -uroot -p flush-logs

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

cd mysql-data-directory #输入自己的通用日志文件所在目录
mv mysql.general.log mysql.general.log.old #指名就的文件名以及新的文件名
mysqladmin -uroot -p flush-logs

错误日志（error log）

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

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

启动日志

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

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

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

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

查看日志

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

查询错误日志的存储路径：

mysql> SHOW VARIABLES LIKE 'log_err%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
4 rows in set (0.00 sec)

执行结果中可以看到错误日志文件是mysqld.log，位于MySQL默认的数据目录下。

下面我们查看一下错误日志的内容。

[root@localhost log]# vim mysqld.log.old 

2022-03-04T05:26:56.045062Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.28) initializing of server in progress as process 5533
2022-03-04T05:26:56.053735Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-03-04T05:26:56.768551Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has