MySQL日志系統全解析：錯誤、慢查詢、Binlog、Redo/Undo機制

MySQL日志系統全解析：錯誤、慢查詢、Binlog、Redo/Undo機制

MySQL日志系統是數據庫可靠性、可觀測性、可恢復性的基石。本文系統梳理服務器日志（錯誤、慢查詢、通用查詢）與事務日志，深度解析Binlog格式、Redo/Undo機制、InnoDB日志重用策略，為運維排查、性能調優、主從同步、數據恢復提供理論支撐。

一、日志體系分類

二、服務器日志詳細配置與使用

2.1 錯誤日志

-- 配置
log_error = /var/log/mysql/error.log

-- 查看
SHOW GLOBAL VARIABLES LIKE 'log_error';
-- 實時監控
tail -f /var/log/mysql/error.log

• 記錄實例啟動、關閉、異常錯誤、啟動警告等
• 非全是錯誤，含信息級別消息
• MySQL無法啟動時，首查錯誤日志

2.2 慢查詢日志

-- 核心參數
slow_query_log = 1                    -- 啟用
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1                   -- 閾值(秒)，建議1-2秒
log_queries_not_using_indexes = 1     -- 記錄未用索引查詢(可選)

-- 動態調整
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

-- 查看狀態
SHOW GLOBAL VARIABLES LIKE '%slow%';

• 核心性能調優工具，配合pt-query-digest、mysqldumpslow分析
• 生產環境建議常開，閾值設1-2秒
• 5.7+支持微秒級精度

2.3 通用查詢日志

general_log = 1
general_log_file = /var/log/mysql/general.log

• 記錄所有客戶端連接/斷開/執行的SQL
• 僅用於臨時診斷（如應用行為分析），長期開啟嚴重影響性能
• 輸出目標可控：log_output = FILE|TABLE|NONE

2.4 日志輸出與輪轉

-- 輸出目標
log_output = FILE  -- 默認文件；TABLE寫mysql.general_log/slow_log表；NONE不輸出

-- 手動輪轉（文件過大時）
FLUSH LOGS;  -- 關閉舊文件，開啟新文件

-- 自動輪轉需配合logrotate或腳本

三、事務日志：Redo Log與Undo Log

3.1 Redo Log：WAL機制核心

• InnoDB/TokuDB等WAL引擎特有
• 記錄物理頁修改（如：頁號、偏移量、數據變更）
• 事務提交前強制刷盤，保證持久性
• 順序寫入，性能極高
• 崩潰恢復時：掃描Redo Log，重放未刷盤的已提交事務

3.2 InnoDB Redo Log循環寫入機制

採用固定大小的多組文件循環覆蓋（默認2個ib_logfile0/1，每48MB）。

關鍵指標：

• innodb_log_file_size：單文件大小，建議1-4GB
• innodb_log_files_in_group：文件組數，通常2-4
• 總容量 = size × group，需容納檢查點推進週期內的所有寫入量

兩種邊界情況：

1. 正常： Checkpoint推進速度 ≥ 日志產生速度，循環寫入無阻塞
2. 阻塞： 寫入高峰期，日志產生快於Checkpoint推進，需等待舊日志被覆蓋前刷盤完成 → 用戶線程阻塞

優化建議： 高寫入業務調大innodb_log_file_size（如2-4GB），延長檢查點週期，減少阻塞概率，提升並發寫入吞吐。

3.3 Undo Log：回滾與MVCC基石

• 記錄邏輯逆操作（如INSERT記錄DELETE，UPDATE記錄舊值）
• 事務回滾時按Undo Log逆向執行
• 支持MVCC：SELECT讀取歷史版本，不加鎖
• 存放於Undo表空間（5.7+可獨立innodb_undo_tablespaces）
• 定期Purge線程清理不再需要的Undo Log

四、二進制日志：Binlog

4.1 核心作用

• 主從復制： Master Binlog → Slave Relay Log → Replay
• 備份恢復： 全備 + Binlog增量 = 時間點恢復
• 審計/異構同步： 解析Binlog推送到Kafka/ES/數據倉庫

4.2 關鍵參數配置

-- 啟用 (靜態參數，需重啟)
log_bin = /data/mysql/binlog/mysql-bin
# 或 log_bin = 1 (數據目錄下)

-- Session級控制
sql_log_bin = 1  -- 當前會話是否記錄

-- 持久化策略 (關鍵！)
sync_binlog = 1  -- 每次提交fsync，最安全，性能損耗~25%
# sync_binlog = 0  依賴OS刷盤，性能最好，崩潰丟數據
# sync_binlog = N  每N次提交fsync

-- 文件管理
max_binlog_size = 100M        -- 單文件上限
expire_logs_days = 7          -- 自動過期清理(天)
binlog_expire_logs_seconds    -- 5.7+ 秒級過期

4.3 Binlog三種格式對比

關鍵參數： binlog_format = ROW|STATEMENT|MIXED（動態可調）

4.4 Binlog查看與解析

-- SQL方式
SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mysql-bin.000011' FROM 60 LIMIT 3;

-- mysqlbinlog工具 (推薦)
mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001
# --start-datetime/--stop-datetime 按時間範圍
# --start-position/--stop-position 按位置範圍

-- ROW格式必加 --base64-output=decode-rows -v 才可讀

4.5 Binlog管理操作

-- 手動清理 (謹慎！確認從庫已同步)
PURGE BINARY LOGS TO 'mysql-bin.000009';
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

-- 避免誤刪：expire_logs_days 自動清理更安全

4.6 Binlog局限與ROW格式必要性

STATEMENT格式陷阱： INSERT INTO t VALUES(UUID()) → Binlog記錄UUID()函數調用 → 回放時每次生成不同值 → 主從數據不一致。

解決： 使用binlog_format = ROW，記錄實際生成的UUID值，回放幂等。

五、總結：日志體系協同工作圖譜

理解各日志定位、交互與配置權衡，是DBA從「會用」進階到「精通」的必經之路。生產環境建議：Error/Slow/Binlog常開，General臨時開，Redo/Undo參數調優，建立日志歸檔與告警機制。