【MySQL】MySQL之查询日志

MySQL的查询日志有两种，一种是慢查询日志（Slow Query Log），另一种是通用查询日志（General Query Log），前者仅记录执行慢的查询，后者是记录所有执行查询的语句。MySQL的查询日志不仅可以记录到文件，而且还可以自动保存到MySQL数据库的表对象中。

慢查询日志

所谓慢查询日志，指的是所有查询语句的执行时间超过系统变量long_query_time（默认值是10秒）指定的参数值，并且访问的记录数超过系统变量 min_examined_row_limit（默认值是0）的数量的语句。

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> show variables like '%min_examined%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.01 sec)

mysql>

SQL语句执行完毕并完成对其锁定资源的释放后，mysqld进程会将符合条件的SQL语句写入慢查询日志，默认情况下慢查询日志功能是被禁用的，启用和禁用慢查询日志文件都是通过MySQL的系统参数控制，主要有两个参数，可以在MySQL服务运行时实时修改，不需要重启服务：

mysql> show variables like '%slow_query%';
+---------------------+------------------------------------+
| Variable_name       | Value                              |
+---------------------+------------------------------------+
| slow_query_log      | OFF                                |
| slow_query_log_file | /usr/local/mysql/data/db1-slow.log |
+---------------------+------------------------------------+
2 rows in set (0.01 sec)

mysql>

其中，slow_query_log指定是否输出慢查询日志，1表示输出，0表示不输出，默认值为0； slow_query_log_file指定日志文件存放路径和文件名，若不指定，则默认保存在data目录下，名称为[host_name]-slow.log。

通用查询日志

默认情况下，通用查询日志不会被启用，可以通过MySQL的系统参数进行启用或禁用该查询日志：

mysql> show variables like 'general_log%';
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log      | OFF                           |
| general_log_file | /usr/local/mysql/data/db1.log |
+------------------+-------------------------------+
2 rows in set (0.00 sec)

mysql>

如果要启用或禁用某个会话产生的普通查询日志，那么就在会话级设置sql_log_off参数来控制，它仅作用于当前会话。

mysql> show variables like 'sql_log_off';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_off   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set sql_log_off=1;
Query OK, 0 rows affected (0.04 sec)

mysql> show variables like 'sql_log_off';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_off   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

配置查询日志

MySQL中可以通过log_output设置查询日志的输出位置，该选项的可选值有3个，分别为：

TABLE：输出信息到数据库的日志表，分别为general_log和slow_log；
FILE：输出信息到日志文件，默认值即FILE；
NONE：不输出查询日志。

上述参数在设置时可以同时指定多个，相互之间以逗号分隔。

查询日志的配置可以在启动MySQL服务时设置，也可以在MySQL服务运行时设置。

1、MySQL服务启动时设置，对应的选项分别为：

--log-output
--general-log
--general-log-file
--slow-query-log
--slow-query-log-file

2、MySQL服务运行中实时修改，对应的选项分别为：

log_output
general_log
general_log_file
slow_query_log
slow_query_log_file

下面以第二种方式演示通用查询日志的配置及日志的查看：

1、启用通用查询日志；

mysql> set global general_log=1;
Query OK, 0 rows affected (0.06 sec)

mysql> show variables like '%general%';
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log      | ON                            |
| general_log_file | /usr/local/mysql/data/db1.log |
+------------------+-------------------------------+
2 rows in set (0.00 sec)

2、查看data目录，生成了日志文件；

mysql> system ls -lh /usr/local/mysql/data/db1.log
-rw-r-----. 1 mysql mysql 697 Mar 20 18:01 /usr/local/mysql/data/db1.log
mysql>

3、执行SQL操作；

mysql> select *from t_emp;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    25
Current database: test

+----+--------+---------------------+------+
| id | name   | cdate               | sex  |
+----+--------+---------------------+------+
|  1 | Alen   | 2018-03-19 15:31:00 | NULL |
|  3 | test   | 2018-03-19 15:49:50 | NULL |
|  7 | Hellp  | 2018-03-19 15:52:45 | NULL |
|  9 | Commit | 2018-03-19 16:15:22 | NULL |
+----+--------+---------------------+------+
4 rows in set (0.12 sec)

mysql> select *from t_emp;
+----+--------+---------------------+------+
| id | name   | cdate               | sex  |
+----+--------+---------------------+------+
|  1 | Alen   | 2018-03-19 15:31:00 | NULL |
|  3 | test   | 2018-03-19 15:49:50 | NULL |
|  7 | Hellp  | 2018-03-19 15:52:45 | NULL |
|  9 | Commit | 2018-03-19 16:15:22 | NULL |
+----+--------+---------------------+------+
4 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_emp          |
| t_mm           |
| t_myisam       |
+----------------+
3 rows in set (0.00 sec)

mysql>

4、查看日志文件，果然生成了相应的记录；

[root@db1 data]# tail -f db1.log 
/usr/local/mysql/bin/mysqld, Version: 5.7.21-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
2018-03-20T10:00:39.914455Z	   25 Connect	root@localhost on test using Socket
2018-03-20T10:00:39.921126Z	   25 Query	show databases
2018-03-20T10:00:39.995515Z	   25 Query	show tables
2018-03-20T10:00:39.995873Z	   25 Field List	t_emp 
2018-03-20T10:00:40.012919Z	   25 Field List	t_mm 
2018-03-20T10:00:40.013180Z	   25 Field List	t_myisam 
2018-03-20T10:00:40.014003Z	   25 Query	select *from t_emp
2018-03-20T10:01:21.924296Z	   25 Query	select *from t_emp
2018-03-20T10:01:33.098173Z	   25 Query	show tables