mysql 正确安全清空在线慢查询日志slow log的流程

参考：http://blog.csdn.net/mchdba/article/details/19161097

1, see the slow log status;

1. mysql> show variables like '%slow%';  
2. +---------------------+------------------------------------------+  
3. | Variable_name       | Value                                    |  
4. +---------------------+------------------------------------------+  
5. | log_slow_queries    | ON                                       |  
6. | slow_launch_time    | 2                                        |  
7. | slow_query_log      | ON                                       |  
8. | slow_query_log_file | /mysqllog/slow_log/slow_queries_3306.log |  
9. +---------------------+------------------------------------------+  
10. 4 rows in set (0.00 sec)  
11.   
12. -- see the query time  
13. mysql> show variables like '%long_query_time%';  
14. +-----------------+----------+  
15. | Variable_name   | Value    |  
16. +-----------------+----------+  
17. | long_query_time | 0.500000 |  
18. +-----------------+----------+  
19. 1 row in set (0.00 sec)  
20.   
21.   
22. mysql>   

2, stop the slow log server.

1. mysql> set global slow_query_log=0;  
2. Query OK, 0 rows affected (0.27 sec)  
3.   
4.   
5. mysql> show variables like '%slow%';  
6. +---------------------+------------------------------------------+  
7. | Variable_name       | Value                                    |  
8. +---------------------+------------------------------------------+  
9. | log_slow_queries    | OFF                                      |  
10. | slow_launch_time    | 2                                        |  
11. | slow_query_log      | OFF                                      |  
12. | slow_query_log_file | /mysqllog/slow_log/slow_queries_3306.log |  
13. +---------------------+------------------------------------------+  
14. 4 rows in set (0.00 sec)  
15.   
16.   
17. mysql>   
18. mysql> show variables like '%slow%'; -- check slow log status  
19. +---------------------+------------------------------------------+  
20. | Variable_name       | Value                                    |  
21. +---------------------+------------------------------------------+  
22. | log_slow_queries    | OFF                                      |  
23. | slow_launch_time    | 2                                        |  
24. | slow_query_log      | OFF                                      |  
25. | slow_query_log_file | /mysqllog/slow_log/slow_queries_3306.log |  
26. +---------------------+------------------------------------------+  
27. 4 rows in set (0.00 sec)  

3, reset the new path of slow log

1. mysql> set global slow_query_log_file='/mysqllog/slow_log/slow_queries_3306_new.log';  
2. Query OK, 0 rows affected (0.03 sec)  

4, start the slow log server

1. mysql>   
2. mysql>   
3. mysql> set global slow_query_log=1;  
4. Query OK, 0 rows affected (0.01 sec)  
5.   
6.   
7. mysql> show variables like '%slow%';  
8. +---------------------+----------------------------------------------+  
9. | Variable_name       | Value                                        |  
10. +---------------------+----------------------------------------------+  
11. | log_slow_queries    | ON                                           |  
12. | slow_launch_time    | 2                                            |  
13. | slow_query_log      | ON                                           |  
14. | slow_query_log_file | /mysqllog/slow_log/slow_queries_3306_new.log |  
15. +---------------------+----------------------------------------------+  
16. 4 rows in set (0.00 sec)  

5, check the slow sql in the new slow log file.

1. mysql> select sleep(10) as a, 1 as b;  
2. +---+---+  
3. | a | b |  
4. +---+---+  
5. | 0 | 1 |  
6. +---+---+  
7. 1 row in set (10.00 sec)  
8.   
9.   
10. mysql>   
11. [mysql@xxx-xxx ~]$ more /mysqllog/slow_log/slow_queries_3306_new.log  
12. ......  
13. Time                 Id Command    Argument  
14. # Time: 140213  6:44:24  
15. # User@Host: root[root] @ localhost []  
16. # Query_time: 10.000365  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0  
17. SET timestamp=1392273864;  
18. select sleep(10) as a, 1 as b;  

6, backup the old big slow log file to other directory.

1. mv /mysqllog/slow_log/slow_queries_3306.log /mysqlbackup/slow_log/slow_queries_3306.log.bak.20140213