开启mysql的慢查询

1.首先要安装好mysql与客户端。

2.连接mysql:

  mysql -uroot -p123

3.查询是否开启了慢查询：

mysql> show variables like '%slow%';（mysql默认是关闭的）

+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_query_log      | OFF                                    |
| slow_query_log_file | /var/lib/mysql/VM-0-11-ubuntu-slow.log |
+---------------------+----------------------------------------+

 

4.开启慢查询：

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%slow%';
+---------------------------+----------------------------------------+
| Variable_name             | Value                                  |
+---------------------------+----------------------------------------+
| log_slow_admin_statements | OFF                                    |
| log_slow_slave_statements | OFF                                    |
| slow_launch_time          | 2                                      |
| slow_query_log            | ON                                     |
| slow_query_log_file       | /var/lib/mysql/VM-0-11-ubuntu-slow.log |
+---------------------------+----------------------------------------+
5 rows in set (0.00 sec)
 

5.查看统计的时长：

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

6.修改统计的时长为一秒：

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
7.测试是否成功：

执行select sleep(1) as a, 1 as b;

查看log文件

mysql> show variables like '%slow%';
+---------------------------+----------------------------------------+
| Variable_name             | Value                                  |
+---------------------------+----------------------------------------+
| log_slow_admin_statements | OFF                                    |
| log_slow_slave_statements | OFF                                    |
| slow_launch_time          | 2                                      |
| slow_query_log            | ON                                     |
| slow_query_log_file       | /var/lib/mysql/VM-0-11-ubuntu-slow.log |
+---------------------------+----------------------------------------+
5 rows in set (0.00 sec)

vim /var/lib/mysql/VM-0-11-ubuntu-slow.log

# Time: 2018-11-02T08:06:36.206036Z
# User@Host: root[root] @  [124.127.244.29]  Id:   100
# Query_time: 1.000249  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1541145996;
select sleep(1) as a, 1 as b;