日志开启配置:
log-slow-queries 目录需要可写权限
log-slow-queries=/home/tangchunhua/log/mysql/slowquery.log
long_query_time=2
log-queries-not-using-indexes
log-slow-queries 日志目录
long_query_time 超时时间
log-queries-not-using-indexes 记录未启用索引的语句
以root登陆mysql client终端开启:
show variables like "%long%"; 查看超时时间
show variables like "%slow%"; 查看慢查询配置情况
set global long_query_time=2; 设置超时时间
set global slow_query_log='ON'; 开始慢查询日志
日志查询方式
[root@aaron bin]# mysqldumpslow -h
Option h requires an argument
ERROR: bad option
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug //查错
-s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default //排序方式query次数，时间，lock的时间和返回的记录数来排序
-r reverse the sort order (largest last instead of first) //倒排序
-t NUM just show the top n queries //top 显示前NUM多个
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names //抽象的数字，至 少有n位内的名称
-g PATTERN grep: only consider stmts that include this string //配置模式
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), //mysql所以机器名或者IP
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time //总时间中不减去锁定时间
例子：
[root@aaron bin]# ./mysqldumpslow -s r -t 20 /var/log/mysql/slowquery.log
[root@aaron bin]# ./mysqldumpslow -s r -t 20 -g 'count' /var/log/mysql/slowquery.log
