欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL数据库学习之查询日志

程序员文章站 2022-07-05 22:27:45
MySQL的查询日志有两种,一种是慢查询日志(Slow Query Log),另一种是通用查询日志(General Query Log),前者仅记录执行慢的查询,后者是记录所有执行...

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-file2、MySQL服务运行中实时修改,对应的选项分别为: log_outputgeneral_loggeneral_log_fileslow_query_logslow_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