MySQL 日誌檔案維護操作指南：輪轉、備份與清理

日誌維護的重要性

MySQL 日誌（錯誤日誌、一般查詢日誌、更新日誌、二進位日誌、慢查詢日誌）是資料庫恢復、稽核、效能分析的關鍵依據。隨著時間增長，日誌檔案會佔用大量磁碟空間，甚至導致磁碟寫滿服務中斷。定期輪轉、備份與清理是 DBA 必備基本功。

強制產生新日誌檔（輪轉）

以下指令均會觸發：關閉舊日誌、開啟新日誌、更新日誌序號遞增（序號模式下）：

實務範例：

# 以 root 帳號觸發輪轉
mysqladmin -u root -p flush-logs

# 在 SQL 腳本/排程中
FLUSH LOGS;

更新日誌輪轉與備份流程

1. 觸發輪轉：mysqladmin flush-logs → 產生新序號檔（如 hostname.002），舊檔（hostname.001）不再寫入
2. 移動舊檔：mv hostname.001 /backup/logs/
3. 壓縮備份：gzip /backup/logs/hostname.001
4. 異地同步：使用 rsync、S3 或備份軟體將壓縮檔同步至異地

一般查詢日誌備份流程（較複雜）

一般查詢日誌預設單一檔案（mysql.log），輪轉需手動處理：

cd /var/lib/mysql          # 進入資料目錄
mv mysql.log mysql.old     # 重命名舊檔
mysqladmin flush-logs      # 觸發輪轉，產生新 mysql.log
# 此時 mysql.old 可安全備份/壓縮/刪除

注意：flush-logs 同時會輪轉所有啟用的日誌類型。

二進位日誌（Binlog）特殊維護

• 自動過期：設定 expire_logs_days = 7（或 8.0+ 的 binlog_expire_logs_seconds），MySQL 自動清理超過天數的 binlog
• 手動清理：PURGE BINARY LOGS TO 'mysql-bin.000123'; 或 PURGE BINARY LOGS BEFORE '2024-01-01';
• 備份前必須：確認從庫已同步至該 binlog，否則清理會導致複寫中斷
• 索引檔：mysql-bin.index 記錄所有 binlog 檔名，不可手動刪除或修改

慢查詢日誌維護

• 定期分析：使用 mysqldumpslow 或 pt-query-digest 分析慢查詢
• 輪轉方式：同 FLUSH LOGS 或 mysqladmin flush-logs
• 保留策略：建議保留 30 天以上，供效能趨勢分析

自動化維護腳本範例

#!/bin/bash
# daily_log_rotate.sh - 每日輪轉並備份日誌
LOG_DIR="/var/lib/mysql"
BACKUP_DIR="/backup/mysql_logs/$(date +%Y%m)"
mkdir -p "$BACKUP_DIR"

# 觸發輪轉
mysqladmin -u root -p"$MYSQL_ROOT_PWD" flush-logs

# 等待檔案釋放
sleep 2

# 壓縮備份舊日誌（排除當前寫入中檔案）
find "$LOG_DIR" -name "*.log" -o -name "*.[0-9]*" | grep -v "$(date +%Y%m%d)" | xargs -r gzip -9
find "$LOG_DIR" -name "*.gz" -mtime +1 -exec mv {} "$BACKUP_DIR" \;

# 清理 30 天前備份
find "$BACKUP_DIR" -type f -mtime +30 -delete

關鍵監控指標

建立標準化日誌維護 SOP，納入自動化排程與監控體系，是確保 MySQL 長期穩定運行的基石。