MySQL通用查询日志、慢查询日志

MySQL日志有：错误日志、二进制日志、通用查询日志、慢查询日志，这里只分析后两种。

1）通用查询日志（general_log）：mysql中的所有操作将会记录下来。

2）慢查询日志（slow_query_log）：记录所有执行时间超过long_query_time秒的所有查询或者不使用索引的查询。

 

通用查询日志

查看通用日志查询是否开启

show variables like 'general_log'; 

开启通用查询日志功能

set global general_log=on;       （set global general_log=off;  关闭）

永久开启需修改my.cnf

general_log=1     #为1表示开启通用日志查询，值为0表示关闭通用日志查询

看看通用日志文件保存位置
show variables like 'general_log_file';

临时更改日志文件保存位置

set global general_log_file='/opt/mysql/data/localhost-222.log';

永久开启需修改my.cnf 

general_log_file = /opt/mysql/data/localhost-222.log    #注意文件路径权限，

 

慢查询日志

查看慢查询日志是否开启及其的存放路径：

show variables like '%slow_query%'; 

（默认关闭，路径默认data目录下）

开启慢查询日志功能

set global slow_query_log=on;

（set  global  slow_query_log=off; 为关闭）

永久开启需修改my.cnf 

slow_query_log =1

临时更改日志文件保存位置

set global slow_query_log_file='/tmp/mysql_slow.log';

修改日志存储路径，使永久开生效需修改my.cnf 

slow_query_log_file=/tmp/mysql_slow.log

查询超过多少秒才记录

show  variables  like  'long_query_time';

默认为10s

修改为超2秒记录

set global long_query_time=2;

（需要重新连接或新开一个会话才能看到修改值）

永久开生效需修改my.cnf 

long_query_time=3

执行一条慢查询SQL语句 

mysql>  select sleep(10);

查看生成慢查询日志

tail    /opt/mysql/data/localhost-slow.log

显示统计慢查询次数

show status like 'slow_queries';

 

log-queries-not-using-indexes

没有使用索引的SQL语句记录到慢查询日志，默认关闭。

查询是否开启

show variables like 'log_queries_not_using_indexes';

开启

set global log_queries_not_using_indexes=1;

关闭

set global log_queries_not_using_indexes=0;

永久开生效需修改my.cnf 添加

log-queries-not-using-indexes

 

log-slow-admin-statements （这里不详细说明）

将慢管理语句例如OPTIMIZE TABLE、ANALYZE TABLE和ALTER TABLE记入慢查询日志。

查看更多慢查询设置信息

show  variables like '%quer%';

查询日志时间时区

5.7.2之后 记录日志显示时间默认为UTC，即比系统的北京时间慢8小时，

 show global variables like 'log_timestamps';

修改为系统时间：

set global log_timestamps = system;

永久生效需在my.cnf 中[mysqld] 添加

log_timestamps = system

日志输出类型

查询输出类型

show variables like 'log_output';

mysql支持查询日志输出类型 FILE或TABLE （不建议，消耗更多性能）也可同时FILE，TABLE

log_output=’FILE’ 表示将日志存入文件，默认存储方式FILE

log_output=’TABLE’表示将日志存入数据库表中，

通用查询日志写入到mysql.库general_log表中，

 慢查询日志写入到mysql库slow_log表中，

更改输出类型为数据表。

set global log_output='TABLE';   

更改输出类型为文件和数据表。

set global log_output='FILE,TABLE'; 

永久开生效需修改my.cnf 

log_output=FILE,TABLE

查看数据库表记录的日志

use mysql ; select * from slow_log;   

（表太大不便使用这方式，通过Navicat查看是需调整为“原始数据模式”）

慢查询日志分析mysqldumpslow 

访问次数最多的 20个sql 语句

mysqldumpslow -s c -t 20  host-slow.log

返回记录集最多的 20个sql语句

mysqldumpslow -s r -t 20  host-slow.log 

按照时间返回前 10 条里面含有left join的 sql 语句。

mysqldumpslow -t 10 -s t -g "left join"  host-slow.log

这会输出记录次数最多的10条SQL语句，

mysqldumpslow -s c -t 10  host-slow.log