mysql开启慢日志

连接mysql：

mysql -h localhost -u root -p

mysql> show variables like 'slow_query%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/server-10-slow.log |　　//名字格式：一般为“主机名-slow.log”
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like 'long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

修改方法1：在mysql交互命令行设置

开启慢查询功能

mysql> set global  slow_query_log = ON;

修改慢查询日志存放路径

mysql> set global slow_query_log_file = ' /var/log/mysql/slow.log';　　//引号别忘

注意事项：

设置slow_query_log_file时，目录必须存在，并且mysql 有权读写该目录，

chown mysql:mysql /var/log/mysql

修改慢查询时间

mysql> set global long_query_time = 2;

最后检查确认（退出，重新登录查看）

测试一下：

select sleep(2);

mysql> show variables like 'slow_query%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | ON                           |
| slow_query_log_file | /var/lib/mysql/test-slow.log |
+---------------------+------------------------------+
2 rows in set (0.01 sec)

mysql> show variables like 'long_query%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

修改方法2：在配置文件my.cnf文件中添加相关参数（需要重启mysql）

lnmp默认MySQL配置文件：/etc/my.cnf

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/test-slow.log
long_query_time = 1