日誌系統#

MySQL 的日誌系統是保證資料可靠性和可恢復性的核心機制。理解日誌系統對於資料庫運維和故障排查至關重要。

三大日誌概覽#

redo log（重做日誌）#

設計思想：WAL#

WAL（Write-Ahead Logging）：先寫日誌，再寫磁碟。

類比酒店掌櫃的粉板和帳本：

• 粉板（redo log）：快速記錄，空間有限
• 帳本（磁碟）：永久儲存，操作較慢
• 打烊時（系統空閒）：將粉板內容更新到帳本

工作機制#

flowchart TB
    subgraph Buffer["redo log buffer"]
        B["記憶體緩衝區"]
    end

    B -->|刷盤| Disk

    subgraph Disk["磁碟檔案（循環寫）"]
        direction LR
        F0["ib_0"] --> F1["ib_1"] --> F2["ib_2"] --> F3["ib_3"]
        F3 -.->|循環| F0
    end

    CP["checkpoint"] -.-> F0
    WP["write_pos"] -.-> F2

    style Buffer fill:#e3f2fd
    style CP fill:#c8e6c9
    style WP fill:#fff3e0

crash-safe 能力#

有了 redo log，即使資料庫異常重啟，已提交的事務資料不會丟失。這就是 crash-safe 能力。

原理：只要 redo log 和 binlog 保證持久化，崩潰恢復時可以重放日誌恢復資料。

關鍵參數#

-- 控制 redo log 刷盤時機
innodb_flush_log_at_trx_commit = 1  -- 推薦：每次事務提交都刷盤

undo log（回滾日誌）#

作用#

1. 事務回滾：記錄資料修改前的狀態，回滾時恢復
2. MVCC：提供資料的歷史版本，實作一致性讀（詳見「03 事務與鎖」章節）

工作機制#

事務修改資料時：
1. 將舊值寫入 undo log
2. 修改資料頁
3. 寫 redo log

事務回滾時：
1. 讀取 undo log
2. 恢復舊值

與 MVCC 的關係#

undo log 形成版本鏈，支援不同隔離級別下的一致性讀：

flowchart LR
    A["當前資料<br/>trx_id=103"] --> B["undo log v2<br/>trx_id=102"] --> C["undo log v1<br/>trx_id=101"]

    style A fill:#c8e6c9
    style B fill:#fff3e0
    style C fill:#e3f2fd

不同事務根據自己的「一致性視圖」，沿版本鏈找到可見的資料版本。

binlog（歸檔日誌）#

與 redo log 的區別#

（比較見上方「三大日誌概覽」表格）

三種格式#

statement 格式的風險示例：

DELETE FROM t WHERE a >= 4 AND t_modified <= '2018-11-10' LIMIT 1;
-- 主備可能使用不同索引，刪除不同的列

row 格式的優勢：

• 記錄被刪除列的主鍵，備庫一定會刪除相同的列
• 誤操作恢復容易：DELETE 誤刪可從 binlog 轉成 INSERT；INSERT 誤插可轉成 DELETE；UPDATE 誤改可交換前後資料重新執行

推薦使用 row 格式，雖然日誌大，但資料一致性有保證，且便於資料恢復。後續「05 高可用」章在討論主備複製時會直接採用此結論。

關鍵參數#

sync_binlog = 1  -- 推薦：每次事務提交都刷盤

資料恢復流程#

恢復到某個時間點（如中午 12 點誤刪資料表）：

1. 找最近的全量備份（如昨晚）
2. 恢復全量備份到臨時庫
3. 從備份時間點開始，重放 binlog 到目標時間點
4. 從臨時庫提取資料，恢復到生產庫

兩階段提交#

為什麼需要#

保證 redo log 和 binlog 的一致性，避免資料和日誌不一致。

流程#

flowchart TD
    A[1. 執行器：執行 SQL，取得新資料] --> B[2. InnoDB：寫 redo log - prepare]
    B --> C[3. Server：寫 binlog]
    C --> D[4. InnoDB：redo log 改為 commit]

    style B fill:#fff3cd
    style D fill:#d4edda

整個事務在執行階段先把變更累積到 redo log buffer 與 binlog cache，到了提交階段才依序刷盤：redo log 進入 prepare 狀態 → binlog 落盤 → redo log 標記為 commit。

崩潰恢復規則#

最佳實踐#

參數設定#

-- 保證資料不丟失
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

-- binlog 格式
binlog_format = ROW

-- redo log 大小（根據寫入量調整）
innodb_log_file_size = 1G
innodb_log_files_in_group = 4

備份策略#

一天一備 vs 一週一備：一天一備的優勢是恢復時需要重放的 binlog 更少，恢復速度更快，即 RTO（Recovery Time Objective）更短。

監控指標#

本章小結#

生產環境必設：innodb_flush_log_at_trx_commit=1 + sync_binlog=1，雙 1 設定保證資料不丟。