MySQL 日誌維護策略全解：錯誤、慢查詢、二進制、通用日誌的生產級配置與清理

MySQL 日誌維護策略全解：錯誤、慢查詢、二進制、通用日誌的生產級配置與清理

日誌是資料庫可觀測性的基石，但不當配置會佔滿磁碟、拖慢效能。本文匯總五大日誌類型的生產級啟用策略、關鍵參數調優、歸檔清理與監控建議。

五大日誌類型與定位

關鍵策略詳解

1. 慢查詢日誌：調優雷達

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = ON
log_output = FILE

• 分析工具：mysqldumpslow -s t /var/log/mysql/slow.log、pt-query-digest。
• 生產環境建議開啟，定期審視 Top SQL。

2. 通用查詢日誌：雙刃劍

-- 動態開啟（排錯時）
SET GLOBAL general_log = 1;
-- 查看
SHOW VARIABLES LIKE 'general_log%';

⚠️ 高併發下每秒 GB 級寫入，**嚴禁長期開啟**。

3. 二進制日誌：生命線

[mysqld]
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 1G
sync_binlog = 1          -- 每提交 fsync，最安全
binlog_cache_size = 4M   -- 大事務避免磁盤臨時檔
max_binlog_cache_size = 4G

• sync_binlog=1：崩潰最多丟 1 筆事務；=0 依賴 OS 刷盤，效能高但風險大。
• 大事務系統建議調大 binlog_cache_size/max_binlog_cache_size 減少磁盤 I/O。

4. Binlog 保留與歸檔

• 本地保留 expire_logs_days=7。
• 異地同步：rsync / scp 定時推送至備份主機，或接入集中式日誌平台（ELK、Kafka）。
• 三副本原則：本地、遠程日誌主機、儲存主機（S3/NAS）。

5. 錯誤日誌與事務日誌

• 錯誤日誌預設在 datadir/hostname.err，關注 ERROR、InnoDB: Assertion failure、Out of memory。
• InnoDB innodb_log_file_size 建議 256M-1G，innodb_log_files_in_group=2，大事務系統適當調大減少 Checkpoint 衝刷壓力。

自動化清理與監控腳本

#!/bin/bash
# /etc/cron.daily/mysql-log-cleanup
# 1. 壓縮 1 天前的 slow log
find /var/log/mysql -name 'slow.log.*' -mtime +1 -exec gzip {} \;
# 2. 刪除 30 天前的 gz
find /var/log/mysql -name 'slow.log.*.gz' -mtime +30 -delete
# 3. Binlog 由 expire_logs_days 自動清理，若手動清理需 PURGE BINARY LOGS BEFORE 'date'
# 4. 告警：慢查詢日誌新增行數 > 1000/小時 發送郵件

常見坑點

1. log_output=TABLE 寫入 mysql.general_log/slow_log 表，會導致系統表膨脹、鎖爭用，**生產請用 FILE**。
2. 忘記 expire_logs_days 導致 Binlog 撐爆磁碟，主庫寫入卡死。
3. sync_binlog=0 + 無電池 RAID 卡 → 掉電丟失已提交事務，資料不一致。

將日誌配置標準化、自動化、可觀測，是資料庫穩定運行的必要條件。