MySQL 四大日志體系全解析：錯誤、查詢、二進制、慢查詢日志配置與運維實戰（增強版）

MySQL 四大日志體系全解析：錯誤、查詢、二進制、慢查詢日志配置與運維實戰（增強版）

日志是資料庫「黑盒錄音器」，掌握四大日志特性、配置與分析方法，是 DBA 故障定位、效能調優、資料恢復的基石。

一、日志類型速覽

二、二進制日志 — 核心配置與操作

my.cnf 關鍵參數

[mysqld]
log-bin = /var/log/mysql/mysql-bin      # 路徑+前綴，生成 .000001 序列檔
expire_logs_days = 10                   # 自動清理超過 10 天的舊日志
max_binlog_size = 100M                  # 單檔上限，超過自動滾動（4KB~1GB）

建議：日志與資料檔放置不同磁碟，減少 IO 競爭。

常用管理命令

• SHOW BINARY LOGS; — 列出所有 binlog 檔案
• mysqlbinlog binlog.000001 — 查看日志內容
• RESET MASTER; — 刪除所有 binlog，重新從 000001 開始（慎用）
• PURGE BINARY LOGS TO 'binlog.000010'; — 刪除指定檔案之前的日志
• PURGE BINARY LOGS BEFORE '2024-01-01'; — 刪除指定日期前的日志

時間點恢復 (PITR) 實戰

# 按時間範圍恢復
mysqlbinlog --start-datetime="2024-01-01 10:00:00" --stop-datetime="2024-01-01 12:00:00" binlog.000001 | mysql -u root -p

# 按位置恢復（更精確）
mysqlbinlog --start-position=12345 --stop-position=67890 binlog.000001 | mysql -u root -p

暫停/恢復 Binlog（無需重啟）

SET SQL_LOG_BIN = 0;  -- 暫停當前會話寫入 binlog
SET SQL_LOG_BIN = 1;  -- 恢復

三、錯誤日志

[mysqld]
log-error = /var/log/mysql/error.log

• 修改後需重啟生效
• 查看路徑：SHOW VARIABLES LIKE 'log_error';
• 純文本檔，可直接 tail -f 監控
• 刪除後執行 mysqladmin -u root -p flush-logs 重新開啟

四、通用查詢日志

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log

• 記錄所有操作，高併發下極大影響效能，僅建議短期除錯開啟
• 文本格式，直接刪除/輪轉

五、慢查詢日志 — 效能調優利器

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2          # 超過 2 秒記錄
log_queries_not_using_indexes = 1  # 記錄未用索引查詢

分析工具鏈

• mysqldumpslow：官方內建，按查詢時間/次數/掃描行數排序
• pt-query-digest (Percona Toolkit)：專業級分析，支援聚合、報表、歷史趨勢
• mysqlsla、mysql-log-filter：其他常用工具

六、日志維護最佳實踐

1. 磁碟分離：Binlog、錯誤日志、慢查詢日志放獨立磁碟/分區
2. 自動清理：expire_logs_days + 定期 PURGE 雙重保障
3. 權限控制：日志目錄僅 mysql 用戶可寫，防止誤刪/竄改
4. 監控告警：磁碟使用率 > 80% 告警；Binlog 停止增長告警（主從同步中斷信號）