mysql 慢查询开启

1.在配置文件中修改 配置 一般配置文件在my.cnf中 (有的my.cnf中没有配置信息 include 去了别的地方按照那个路径就能找到对应的配置文件)

我的配置文件里面是这样的

root@iZwz988p52tbipf6ez6o9tZ:/var/lib/mysql# vi /etc/mysql/my.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

 所以我的配置文件在 /etc/mysql/mysql.conf.d 里面

2.找到配置文件

这是我的配置文件

root@iZwz988p52tbipf6ez6o9tZ:/etc/mysql/mysql.conf.d# vi mysqld.cnf 

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

找到 [mysqld] 在下面添加几条配置

slow_query_log = 1  #表示开启慢查询，0表示关闭
slow_query_log_file = /var/lib/mysql/slow-query.log   #指定慢查询日志路径 需要MySQL对该路径有写权限
long_query_time = 1   #表示查询时间>=1秒才记录日志 默认10s


log_queries_not_using_indexes = 1   #表明记录没有使用索引的 SQL 语句

修改保存 重启 mysql

sudo service mysqld restart

然后进入mysql 验证是否成开启

show variables like 'slow_query%';
show variables like 'long_query_time';

然后测试一下

select sleep(3);

然后去 日志中可以看到有信息

/usr/sbin/mysqld, Version: 5.7.25-0ubuntu0.16.04.2-log ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2019-02-27T13:16:36.640027Z
# User@Host: root[root] @ localhost []  Id:     4
# Query_time: 3.000523  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use employees;
SET timestamp=1551273396;
select sleep(3);

成功