mysql 动态开启慢查询_MySQL：动态开启慢查询日志(Slow Query Log)

MySQL 提供单独的日志文件记录慢查询，这个文件默认文件名为 host_name-slow.log, 但可以通过初始化参数进行配置，这里记录下临时开启慢查询的方法。

一 在日志文件中开启慢查询

查看 slow_query_log 参数

1

2

3

4

5

6

7root@localhost:francs>show variables like '%slow_query_log%';

+---------------------+-----------------------------------+

|Variable_name|Value|

+---------------------+-----------------------------------+

|slow_query_log|OFF|

|slow_query_log_file|/database/mysql/data/db1-slow.log|

+---------------------+-----------------------------------+

备注： slow_query_log 参数控制慢查询功能是否打开，值为on (1)/off(0), slow_query_log_file 参数为数据库慢查询日志文件名。

查看 long_query_time

1

2

3

4

5

6root@localhost:francs>show variables like '%long_query_time%';

+-----------------+-----------+

| Variable_name | Value |

+-----------------+-----------+

| long_query_time | 10.000000 |

+-----------------+-----------+

备注： SQL 语句运行超过 long_query_time 设定的值时，语句将被记录到慢查询日志中 ，此参数单位为秒。

设置慢查询

1

2

3

4

5root@localhost:francs>set global slow_query_log=on;

Query OK, 0 rows affected (0.05 sec)

root@localhost:francs>set global long_query_time=1;

Query OK, 0 rows affected (0.00 sec)

验证

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16root@localhost:francs>show variables like '%slow_query_log%';

+---------------------+-----------------------------------+

| Variable_name | Value |

+---------------------+-----------------------------------+

| slow_query_log | ON |

| slow_query_log_file | /database/mysql/data/db1-slow.log |

+---------------------+-----------------------------------+

2 rows in set (0.00 sec)

root@localhost:francs>show variables like '%long_query_time%';

+-----------------+-----------+

| Variable_name | Value |

+-----------------+-----------+

| long_query_time | 10.000000 |

+-----------------+-----------+

1 row in set (0.00 sec)

备注： 发现 long_query_time 的值没做相应的改变，网上查了下资料说是退出当前会话，重连数据库时生效。

退出当前会话并重连

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21[mysql@db1 ~]$ mysql

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 47

Server version: 5.6.20 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

root@localhost:(none)>show variables like '%long_query_time%';

+-----------------+----------+

| Variable_name | Value |

+-----------------+----------+

| long_query_time | 1.000000 |

+-----------------+----------+

1 row in set (0.00 sec)

备注：重新登陆后， long_query_time 才生效。

验证 ，开启session1 执行以下：

1

2

3

4

5

6

7root@localhost:(none)>select sleep(1);

+----------+

| sleep(1) |

+----------+

| 0 |

+----------+

1 row in set (1.00 sec)

查看慢查询日志

1

2

3

4

5

6

7[mysql@db1 data]$ tail -f db1-slow.log

....

# Time: 140818 10:50:17

# User@Host: root[root] @ localhost [] Id: 47

# Query_time: 1.000651 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0

SET timestamp=1408330217;

select sleep(1);

备注：慢查询日志也可以记录到数据库表中。

二 在数据库表中开启慢查询

设置 log_output

1

2

3

4

5

6

7

8

9

10root@localhost:mysql>show variables like '%log_output%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output | FILE |

+---------------+-------+

1 row in set (0.00 sec)

root@localhost:mysql>set global log_output='FILE,TABLE';

Query OK, 0 rows affected (0.00 sec)

备注： log_output 参数设定日志文件的输出，可选值为 TABLE, FILE ,NONE; “TABLE” 意思为设定日志分别记录到 mysql 库的 general_log 和 slow_log 表中; “FILE” 意思为记录日志到操作系统的文件中， “NONE” 意思为取消日志记录。

验证，执行以下：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48root@localhost:mysql>show variables like '%log_output%';

+---------------+------------+

| Variable_name | Value |

+---------------+------------+

| log_output | FILE,TABLE |

+---------------+------------+

1 row in set (0.00 sec)

root@localhost:mysql>select count(*) from slow_log;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

root@localhost:mysql>select sleep(1);

+----------+

| sleep(1) |

+----------+

| 0 |

+----------+

1 row in set (1.00 sec)

root@localhost:mysql>select count(*) from slow_log;

+----------+

| count(*) |

+----------+

| 1 |

+----------+

1 row in set (0.00 sec)

root@localhost:mysql>select * from slow_logG

* 1. row *

start_time: 2014-08-18 11:21:39

user_host: root[root] @ localhost []

query_time: 00:00:01

lock_time: 00:00:00

rows_sent: 1

rows_examined: 0

db: mysql

last_insert_id: 0

insert_id: 0

server_id: 0

sql_text: select sleep(1)

thread_id: 47

1 row in set (0.00 sec)

备注： slow_log 表中果然有了一条 sleep(1) 慢查询日志，也可以将慢查询日志参数设置到 my.cnf 配置文件中，重启数据库重效。

三 参考