MySQL 線上安全清空慢查詢日誌完整操作流程

前言

MySQL 慢查詢日誌對於效能調優至關重要，但隨著時間推移，日誌檔案會佔用大量磁碟空間。本文詳細記錄如何在線上環境安全地清空慢查詢日誌，且無需重啟 MySQL 服務。

操作步驟

1. 確認慢查詢日誌當前狀態

首先檢查慢查詢日誌的相關變數：

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

2. 暫時關閉慢查詢日誌

使用全域變數動態關閉，避免寫入衝突：

mysql> set global slow_query_log=0;
Query OK, 0 rows affected (0.27 sec)

再次確認狀態已變為 OFF：

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

3. 變更慢查詢日誌檔案路徑

指定新的日誌檔案路徑，實現日誌切割：

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

4. 重新啟用慢查詢日誌

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

5. 測試新日誌檔案記錄功能

執行一條耗時查詢驗證：

mysql> select sleep(10) as a, 1 as b;
+---+---+
| a | b |
+---+---+
| 0 | 1 |
+---+---+
1 row in set (10.00 sec)

檢查新日誌檔案內容：

[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. 備份舊日誌檔案

將原大日誌檔案移動至備份目錄：

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

注意事項

• 整個過程無需重啟 MySQL，業務零中斷
• 建議定期執行此操作，防止單一檔案過大影響效能
• 備份檔案命名建議包含日期，便於歸檔管理