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
如对本文有疑问, 点击进行留言回复!!
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火,普罗米修斯的崛起
网友评论