MySQL 日志操作核心命令與實戰指南

MySQL 日志體系概覽

MySQL 日志系統是資料庫運行的「黑盒記錄儀」，主要包含五大類：錯誤日志、查詢日志、慢查詢日志、事務日志（Redo/Undo Log）與二進制日志。這些日志文件完整記錄了資料庫啟動、關閉、SQL 執行、錯誤異常、主從複制等關鍵事件。當資料庫遭遇損壞或數據不一致時，日志是排查故障、實現時間點恢復（PITR）的核心依據。

錯誤日志：不可關閉的核心監控

錯誤日志默認開啟且無法禁用，預設存放於數據目錄下，文件命名為 hostname.err。其記錄內容涵蓋：服務啟動/關閉過程（如 InnoDB 表空間初始化、存儲引擎加載）、運行期錯誤、事件調度器異常、從庫啟動信息等。可通過 log-error 指定路徑，log-warnings 控制是否記錄警告信息。

二進制日志：主從複制與數據恢復的基石

核心作用與查看方式

二進制日志記錄所有修改數據的語句（INSERT/UPDATE/DELETE/DDL），不包含 SELECT 等只讀操作。主要用於：

• 主從複制：主庫將 Binlog 事件發送給從庫重放
• 時間點恢復：基於備份集 + Binlog 增量實現 PITR

查看當前 Binlog 狀態：

mysql> SHOW MASTER STATUS;

解析 Binlog 文件內容：

shell> mysqlbinlog mysql-bin.000001
shell> mysqlbinlog mysql-bin.000001 | tail

三種日志格式深度解析

1. 基於語句模式
記錄原始 SQL 語句。優點：日志體積小。缺點：非確定性函數（如 NOW()、UUID()）在主從重放時結果不一致。
示例： UPDATE sales SET amount=amount+1000; 只記錄這條語句，而非 10 萬行變更數據。

2. 基於行模式
記錄每行數據的具體變更（前後映像）。優點：完全確定性，支持複雜觸發器、存儲過程。缺點：大批量更新時日志膨脹嚴重。
示例： INSERT INTO t SET birth=CURRENT_TIME(); 必須記錄具體寫入的時間值。

3. 混合模式
MySQL 自動判斷：確定性語句用 Statement，非確定性用 Row。兼顧性能與數據一致性，推薦生產環境使用。

關鍵配置參數

• sync_binlog=1：每次事務提交強制刷盤，最安全但性能損耗最大
• binlog_format=ROW|STATEMENT|MIXED：設置日志格式
• expire_logs_days：自動清理過期 Binlog，防止磁盤佔滿

基於位置的時間點恢復實戰

場景：全備時間 2023-10-01 10:00，故障發生於 14:30。恢復步驟：

1. 導出 Binlog 至 SQL 文件：
   

   mysqlbinlog mysql-bin.000001 > /backup/binlog.sql

2. 定位恢復區間：打開 binlog.sql 搜索故障前的位置點（如 # at 5230766 到 # at 5231104）
3. 精準重放增量：
   

   mysqlbinlog --start-position=5230766 --stop-position=5231104 mysql-bin.000001 | mysql -uroot -p

注意： 必須開啟 Binlog 且確認 server_id 唯一。基於時間恢復（--start-datetime）因秒級精度限制，生產環境強烈建議使用位置點恢復。

事務日志與存儲引擎協作

InnoDB 通過 Redo Log（重做日志）保證事務持久性，Undo Log（撤銷日志）實現 MVCC 與回滾。非事務表（MyISAM）更新即時寫入 Binlog；事務表則在 Commit 時一次性寫入，由 binlog_cache_size 控制內存緩衝，超限則落臨時文件。