MySQL慢查询日志、通用日志

一、慢查询日志——记录时间是Querytime-Locktime（查询时间-锁等待时间）

1.1、修改my.cnf中slow log的配置

slow_query_log=1
slow_query_log_file=slow.log
long_query_time=10

查看

(root@localhost) [(none)]> show variables like 'slow%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| slow_launch_time    | 2        |
| slow_query_log      | ON       |
| slow_query_log_file | slow.log |
+---------------------+----------+
3 rows in set (0.01 sec)

查看等待时间

(root@localhost) [(none)]> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

1.2、临时修改慢查询的配置

(root@localhost) [(none)]> set global slow_query_log=off;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> set global slow_query_log=on;
Query OK, 0 rows affected (0.03 sec)

(root@localhost) [(none)]> set global slow_query_log_file='slow_new.log';
Query OK, 0 rows affected (0.01 sec)

(root@localhost) [(none)]> set global long_query_time=12;
Query OK, 0 rows affected (0.00 sec)

1.3、情况慢查询日志

(root@localhost) [(none)]> flush slow logs;
Query OK, 0 rows affected (0.07 sec)

1.4 、慢查询无法记录的情况

(root@localhost) [test]> create table aa(a int,b int);
Query OK, 0 rows affected (0.04 sec)

(root@localhost) [test]> insert into aa values(1,1);
Query OK, 1 row affected (0.04 sec)

(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> select * from aa where a=1 for update;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

另外一个回话

(root@localhost) [test]> update aa set b=b+1 where a=1;

第一个回话提交

(root@localhost) [test]> commit;
Query OK, 0 rows affected (0.00 sec)

第二个回话查询时间长，但是没有记录

(root@localhost) [test]> update aa set b=b+1 where a=1;
Query OK, 1 row affected (42.97 sec)
Rows matched: 1  Changed: 1  Warnings: 0

二、通用日志

general日志，记录数据库所有相关操作，开启后性能明显下降

在my.cnf中添加配置

general_log=1
general_log_file=general.log

重启数据库后查看状态

(root@localhost) [(none)]> show variables like 'gener%';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| general_log      | ON          |
| general_log_file | general.log |
+------------------+-------------+
2 rows in set (0.01 sec)