前言：日誌是 MySQL 的「黑盒記錄儀」
MySQL 維護五大核心日誌體系：**錯誤日誌、通用查詢日誌、慢查詢日誌、二進制日誌、中繼日誌**。每種日誌服務於不同場景（故障診斷、審計、調優、復制、時間點恢復）。本文系統梳理各日誌類型、配置參數、文件管理、輪轉策略、分析工具與故障排查實戰，構建 DBA 日誌治理的標準化操作手冊。
一、五大日誌類型總覽
| 日誌類型 | 記錄內容 | 核心用途 | 性能影響 | 默認狀態 |
|---|
| 錯誤日誌 | 啟動/停止/異常/崩潰堆棧 | 故障診斷、崩潰分析、監控告警 | 極低 | 開啟 |
| 通用查詢日誌 | 連接/斷開/所有 SQL 語句（含 SELECT） | 審計、應用行為分析、疑難雜症復現 | 高（IO+CPU），生產**慎開** | 關閉 |
| 慢查詢日誌 | 耗時 > long_query_time / 無索引查詢 | SQL 調優、索引缺失發現、容量規劃 | 低（僅記錄慢語句） | 關閉（建議開啟） |
| 二進制日誌 | 所有數據變更 (DML/DDL)、事務事件、GTID | 主從復制、時間點恢復 (PITR)、審計 | 低 (~1%)，生產**必開** | 關閉（建議開啟） |
| 中繼日誌 | 從庫接收的主庫 Binlog 事件 | 復制 SQL 線程回放、斷點續傳 | 低 | 從庫自動開啟 |
二、錯誤日誌：故障診斷首選
1. 配置
[mysqld]
log-error = /data/mysql/3306/mysql_error.log
# 未指定文件名：datadir/hostname.err
# Windows --console：輸出到 stderr (控制台)
2. 關鍵內容識別
- 啟動/關閉：
mysqld: ready for connections / Normal shutdown。
- 崩潰恢復：
InnoDB: Starting crash recovery、redo log 回放進度。
- 異常關閉：
mysqld_safe: restarted mysqld（守護進程重啟）。
- 堆棧跟蹤：Linux 崩潰時生成，定位崩潰函數棧。
- 表損壞/自動修復：
Checking table...、Repairing table...。
3. 輪轉
-- 手動輪轉（重命名舊文件，mysqld 創建新文件）
mv mysql_error.log mysql_error.log.old
mysqladmin flush-logs -- 或 FLUSH LOGS / FLUSH ERROR LOGS
FLUSH LOGS 會將錯誤日誌重命名為 .old 後綴並創建新文件（若指定 log-error）。- 建議配合
logrotate 系統級輪轉（每日/按大小、保留 30 天、壓縮）。
三、通用查詢日誌：審計與復現的「全記錄儀」
1. 配置
[mysqld]
general_log = 1
general_log_file = /data/mysql/3306/general.log
# log_output = FILE (默認) / TABLE (寫 mysql.general_log 表) / FILE,TABLE
2. 特性與避坑
- 記錄**接收順序**，非執行順序（Binlog 記錄提交順序）。
- 包含**所有語句**（含 SELECT），Binlog 僅記錄變更。
- 生產高併發嚴禁長期開啟：IO 暴增、磁碟爆滿、性能下降 20%+。
- 臨時開啟排查：
SET GLOBAL general_log=1; 定位問題 → SET GLOBAL general_log=0; 關閉。
- 表輸出模式 (
log_output=TABLE) 可用 SQL 查詢過濾，但寫入開銷更大。
3. 手動輪轉
mv general.log general.log.old
mysqladmin flush-logs
四、慢查詢日誌：調優的「金礦」
1. 核心配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /data/mysql/3306/slow.log
long_query_time = 1 -- 秒，建議 0.5~1，支援微秒 (0.05)
log_queries_not_using_indexes = 1 -- 記錄全表掃描（無索引/索引失效）
log_slow_admin_statements = 1 -- 記錄 OPTIMIZE/ANALYZE/ALTER 等管理語句
log_throttle_queries_not_using_indexes = 60 -- 限流：每分鐘最多記錄 60 條無索引查詢 (5.6+)
min_examined_row_limit = 100 -- 忽略掃描行數 < 100 的查詢 (5.6+)
2. 記錄格式解讀
# Time: 2024-01-15T10:30:45.123456Z
# User@Host: app_user[app_user] @ 192.168.1.100 [Id: 12345]
# Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 1000000
# Rows_affected: 0 Bytes_sent: 1234
SET timestamp=1705314645;
SELECT * FROM large_table WHERE non_index_col = 'value';
Query_time：總耗時（含鎖等待、IO、CPU）。Lock_time：等待鎖時間。Rows_examined：存儲引擎層掃描行數（核心指標）。Rows_sent：返回客戶端行數。
3. 分析神器：mysqldumpslow / pt-query-digest
# mysqldumpslow 內建工具
mysqldumpslow -s t -t 10 /data/mysql/3306/slow.log # 按耗時排序 Top 10
mysqldumpslow -s c -t 20 slow.log # 按次數排序 Top 20
mysqldumpslow -g "SELECT.*large_table" slow.log # 正則過濾
# pt-query-digest (Percona Toolkit) - 專業級
pt-query-digest --limit 20 --filter '$event->{Query_time} > 1' /data/mysql/3306/slow.log
pt-query-digest --since "2h ago" --until "now" --output json slow.log > report.json
pt-query-digest 支援聚合指紋、統計分佈、EXPLAIN 計劃關聯、JSON 輸出對接 Grafana。
4. 輪轉
mv slow.log slow.log.old
mysqladmin flush-logs
-- 或配置 logrotate
五、二進制日誌：復制與恢復的「生命線」
1. 核心配置
[mysqld]
log-bin = /data/mysql/3306/binlog/binlog -- 基礎文件名，自動添加序號 .000001
binlog_format = MIXED -- ROW/STATEMENT/MIXED (推薦 MIXED/ROW)
binlog_cache_size = 4M -- 事務緩存，大事務增大防臨時文件
max_binlog_size = 1G -- 單文件上限，達到自動切換
expire_logs_days = 30 -- 自動清理過期文件 (5.7+)
binlog_expire_logs_seconds = 2592000 -- 8.0+ 秒級精度替代 expire_logs_days
sync_binlog = 1 -- 每次提交 fsync (最安全，性能損 ~10%)
innodb_flush_log_at_trx_commit = 1 -- 配合 sync_binlog=1 實現全持久化
binlog_rows_query_log_events = 1 -- ROW 格式下記錄原始 SQL (5.6+，便審計)
gtid_mode = ON -- GTID 復制模式 (推薦)
enforce_gtid_consistency = ON
2. 格式選擇：ROW vs STATEMENT vs MIXED
| 格式 | 記錄內容 | 優點 | 缺點 | 適用 |
|---|
STATEMENT | 原始 SQL | 文件小、易讀 | 非確定性函數/觸發器/UDF 導致主從不一致 | 簡單業務、跨版本復制 |
ROW | 行級變更 (前後映像) | 絕對一致性、無非確定性問題 | 文件大、大批量更新膨脹、無法直接看到 SQL | 核心業務、金融、高一致性要求 |
MIXED | 智能切換 | 兼顧性能與一致性 | 極少數邊界情況判斷偏差 | **推薦默認** |
3. 過濾規則：binlog-do-db / binlog-ignore-db
- 基於**當前默認數據庫** (
USE db) 判斷，**非**操作對象數據庫。
CREATE/ALTER/DROP DATABASE 例外：按操作對象庫判斷。- 復制場景慎用：易導致主從數據不一致，推薦從庫端
replicate-do-db 過濾。
4. 文件結構與索引
- 數據文件：
binlog.000001, binlog.000002... 序號遞增。
- 索引文件：
binlog.index 記錄所有文件名，**運行時不可手動編輯**。
- 大事務：單事務**不拆分**跨文件，可能超過
max_binlog_size。
5. 時間點恢復 (PITR) 實戰
# 1. 定位備份點位置
mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000005 | grep -A 20 "SET TIMESTAMP=1705314645"
# 2. 恢復：從備份時間點 到 故障前一秒
mysqlbinlog --start-datetime="2024-01-15 10:30:00" --stop-datetime="2024-01-15 10:30:45" \
/data/mysql/3306/binlog/binlog.000005 | mysql -u root -p
# 3. 基於 Position 精準恢復
mysqlbinlog --start-position=12345 --stop-position=67890 binlog.000005 | mysql -u root -p
6. 清理策略
- 自動：
expire_logs_days=30 / binlog_expire_logs_seconds。
- 手動：
PURGE BINARY LOGS TO 'binlog.000010'; 或 PURGE BINARY LOGS BEFORE '2024-01-10 00:00:00';。
- 前提：確認從庫已處理至該位置 (
SHOW SLAVE STATUS\G 中 Relay_Master_Log_File / Exec_Master_Log_Pos)。
六、中繼日誌：從庫的「緩衝倉」
1. 配置
[mysqld]
relay_log = /data/mysql/3306/relaylog/relaylog
relay_log_index = /data/mysql/3306/relaylog/relaylog.index
relay_log_info_file = /data/mysql/3306/relaylog/relaylog.info
# 5.6+ 信息默認存表 mysql.slave_relay_log_info (TABLE 模式)
relay_log_info_repository = TABLE
relay_log_recovery = 1 -- 啟動自動修復損壞中繼日誌 (5.6+)
2. 關鍵參數
relay_log_purge=1 (默認)：SQL 線程處理完自動清理舊中繼日誌。max_relay_log_size=0：0 表示跟隨 max_binlog_size。
七、日誌綜合運維檢查清單
| 頻率 | 檢查項目 | 工具/命令 |
|---|
| 實時/分鐘 | 錯誤日誌異常關鍵字 (Crash/Assertion/Signal/Deadlock/Aborted) | ELK/Fluentd + 告警規則 |
| 每日 | 慢查詢 Top 20 分析、無索引查詢趨勢、Binlog 磁碟佔用、中繼日誌延遲 | pt-query-digest、腳本巡檢 |
| 每周 | 錯誤日誌輪轉完整性、Binlog 清理生效、通用日誌未誤開 | logrotate 狀態、ls -lh |
| 變更前 | Binlog 位置備份、從庫同步位置確認 | SHOW MASTER/SLAVE STATUS |
| 故障時 | 錯誤日誌堆棧、慢查詢關聯、Binlog 事件回放驗證 | mysqlbinlog、pt-query-digest |
結語
日誌非「開啟即安心」，乃「配置精準、輪轉可控、分析及時、恢復演練」的系統工程。從錯誤日誌的崩潰堆棧，到慢查詢的指紋聚合；從 Binlog 的 ROW/MIXED 抉擇，到 PITR 的秒級精準恢復；從中繼日誌的自動修復，到通用日誌的「臨時開啟、用完即關」——每一個日誌決策，都在為**可觀測性、可恢復性、可審計性**投票。願本文手冊助您構建「日誌全生命周期精益治理」體系，讓 MySQL 的每一次心跳，都有據可查、有據可循、有據可恢復。
