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);
成功