mysql5.1及以上版本动态关闭和开启慢日志功能

在mysql5.1版本以前，慢日志(slow.log 根据参数的设定记录慢sql)和查询日志(general.log 这个会记录所有sql)都只能通过修改参数文件，并重启以使修改生效。在mysql5.1以上版本中，可以实现动态开启，无需重启即可生效。

慢日志开启和关闭的语句如下：

set global slow_query_log='off';
set global slow_query_log='on';

general.log的动态开启方法可参考

http://www.taobaodba.com/html/477_动态开启mysql-general-log.html

在有些场合，考虑到性能等因素，可以动态关闭这些日志功能，排查问题的时候再动态打开而不需要重启mysql。

测试：

====5.0版本===

$mysql -utestuser -ptestpswd

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

Your MySQL connection id is 16095061

Server version: 5.0.67-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

root@(none) 02:12:13>show variables like '%slow%';

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

| Variable_name    | Value |

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

| log_slow_queries | ON    | 

| slow_launch_time | 2     | 

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

2 rows in set (0.00 sec)

root@(none) 02:12:43>set global slow_query_log='off';

ERROR 1193 (HY000): Unknown system variable 'slow_query_log'

在mysql5.0版本中没有slow_query_log这个系统变量

///

===5.1版本===

$mysql -utestuser -ptestpswd

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

Your MySQL connection id is 28891

Server version: 5.1.48-log Source distribution

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

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

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

root@(none) 02:07:47>show variables like '%slow%';

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

| Variable_name       | Value                   |

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

| log_slow_queries    | ON                      |

| slow_launch_time    | 2                       | 

| slow_query_log      | ON                      |                          //在mysql5.1以上版本中，添加了这个系统变量

| slow_query_log_file | /u01/mysql/log/slow.log |

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

4 rows in set (0.00 sec)

root@(none) 02:08:02>set global slow_query_log='off';   //关闭慢日志标志

Query OK, 0 rows affected (1.53 sec)

root@(none) 02:08:15>show variables like '%slow%';

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

| Variable_name       | Value                   |

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

| log_slow_queries    | OFF                     |

| slow_launch_time    | 2                       |

| slow_query_log      | OFF                     |

| slow_query_log_file | /u01/mysql/log/slow.log |

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

4 rows in set (0.00 sec)

root@(none) 02:24:02>set global slow_query_log='on';  //开启慢日志标志
Query OK, 0 rows affected (0.00 sec)
root@(none) 02:36:15>show variables like '%slow%';
+---------------------+-------------------------+
| Variable_name       | Value                   |
+---------------------+-------------------------+
| log_slow_queries    | ON                      | 
| slow_launch_time    | 2                       | 
| slow_query_log      | ON                      | 
| slow_query_log_file | /u01/mysql/log/slow.log | 
+---------------------+-------------------------+
4 rows in set (0.00 sec)