[MySQL Slow log]正确安全清空在线慢查询日志slow log的流程

小灰灰 2022-05-25 14:25 135阅读 0赞

**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