Mysql correctly and securely clears the online slow query log slowlog process and shares bitsCN.com
1. see the slow log status;
Mysql> show variables like '% slow % ';
+ --------------------- + ------------------------------------------ +
| Variable_name | Value |
+ --------------------- + ------------------------------------------ +
| Log_slow_queries | ON |
| Slow_launch_time | 2 |
| Slow_query_log | ON |
| Slow_query_log_file |/mysqllog/slow_log/slow_queries_3306.log |
+ --------------------- + ------------------------------------------ +
4 rows in set (0.00 sec)
2. stop the slow log server.
Mysql> set global slow_query_log = 0;
Query OK, 0 rows affected (0.27 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 |/mysqllog/slow_log/slow_queries_3306.log |
+ --------------------- + ------------------------------------------ +
4 rows in set (0.00 sec)
Mysql>
Mysql> show variables like '% slow %'; -- check slow log status
+ --------------------- + ------------------------------------------ +
| Variable_name | Value |
+ --------------------- + ------------------------------------------ +
| Log_slow_queries | OFF |
| Slow_launch_time | 2 |
| Slow_query_log | OFF |
| Slow_query_log_file |/mysqllog/slow_log/slow_queries_3306.log |
+ --------------------- + ------------------------------------------ +
4 rows in set (0.00 sec)
3, reset the new path of slow log
Mysql> set global slow_query_log_file = '/mysqllog/slow_log/slow_queries_3306_new.log ';
Query OK, 0 rows affected (0.03 sec)
4. start the slow log server
Mysql>
Mysql>
Mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.01 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 |/mysqllog/slow_log/slow_queries_3306_new.log |
+ --------------------- + ---------------------------------------------- +
4 rows in set (0.00 sec)
5. check the slow SQL in the new slow log file.
Mysql> select sleep (10) as a, 1 as B;
+ --- +
| A | B |
+ --- +
| 0 | 1 |
+ --- +
1 row in set (10.00 sec)
Mysql>
[Mysql @ xxx-xxx ~] $ More/mysqllog/slow_log/slow_queries_3306_new.log
......
Time Id Command Argument
# Time: 140213 6:44:24
# User @ Host: root [root] @ localhost []
# Query_time: 10.000365 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp = 1392273864;
Select sleep (10) as a, 1 as B;
6. backup the old big slow log file to other directory.
Mv/mysqllog/slow_log/slow_queries_3306.log/mysqlbackup/slow_log/slow_queries_3306.log.bak.20140213
BitsCN.com