慢查询是什么,如何操作?
MySQL记录下查询超过指定时间的语句,超过指定时间的SQL语句查询称为“慢查询”
开启慢查询
方法一
:用命令开启慢查询
查看默认慢查询的时间(10秒)
mysql> show variables like "%long%";
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
设置成2秒,加上global,下次进mysql已然生效
mysql> set global long_query_time=2;
Query OK, 0 rows affected (0.00 sec)
查看一下慢查询是不是已经开启
mysql> show variables like "%slow%";
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| log_slow_queries | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/mysql-slow.log |
+---------------------+---------------------------------+
4 rows in set (0.00 sec)
设置开启慢查询
mysql> set slow_query_log='ON';
ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL
设置开启慢查询,加上global,不然会报错的
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.28 sec)
查看是否已经开启
mysql> show variables like "%slow%";
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/mysql-slow.log |
+---------------------+---------------------------------+
4 rows in set (0.00 sec)
开启执行sql日志
set global general_log='ON';
方法二
:修改mysql的配置文件my.cnfgeneral_log
在[mysqld]里面加上以下内容
long_query_time = 2
log-slow-queries = /usr/local/mysql/mysql-slow.log
重起一下
/usr/local/mysql/libexec/mysqld restart