1.首先在mysql数据库中查看是否开启慢查询
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.01 sec)
2.设置slow_query_low 的value为on
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.03 sec)
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.01 sec)
3.查看变量
mysql> show variables like '%log%';
log_queries_not_using_indexes | OFF
mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)
4.查询慢查询的时间变量
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
5.修改long_query_time为0秒来做测试
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 0.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
6.查询我们的慢查询的日志在哪里?
mysql> show variables like 'slow%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/sheom-slow.log |
+---------------------+-------------------------------+
7.打开慢查询的日志
# Query_time: 0.000217 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1492405081;
shwo tables;
# Time: 2017-04-17T04:58:04.661374Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 0.000603 Lock_time: 0.000174 Rows_sent: 4 Rows_examined: 4
SET timestamp=1492405084;
show tables;
# Time: 2017-04-17T04:58:11.244499Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 0.015909 Lock_time: 0.000266 Rows_sent: 10 Rows_examined: 10
SET timestamp=1492405091;
select * from emp limit 10;
# Time: 2017-04-17T04:58:36.599922Z
# User@Host: root[root] @ localhost [] Id: 5
# Query_time: 0.008275 Lock_time: 0.000379 Rows_sent: 3 Rows_examined: 1014
SET timestamp=1492405116;
show variables like 'slow%';