日誌系統#

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

• write_pos：當前寫入位置，循環往後移動
• checkpoint：當前擦除位置，需要先刷盤才能擦除
• write_pos 追上 checkpoint：必須停下來刷盤

crash-safe 能力#

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

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

關鍵參數#

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

undo log（回滾日誌）#

作用#

1. 事務回滾：記錄資料修改前的狀態，回滾時恢復
2. MVCC：提供資料的歷史版本，實作一致性讀

工作機制#

事務修改資料時：
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 的區別#

三種格式#

推薦使用 row 格式，雖然日誌大，但資料一致性有保證，且便於資料恢復。

關鍵參數#

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

崩潰恢復規則#

日誌寫入時機#

事務執行過程：

flowchart LR
    A["執行 SQL"] --> B["redo log buffer"] --> C["binlog cache"]

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

事務提交時：

flowchart TD
    A["1. redo log 從 buffer 刷到磁盤 (prepare)"] --> B["2. binlog 從 cache 刷到磁盤"]
    B --> C["3. redo log 標記為 commit"]

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

最佳實踐#

參數組態#

-- 保證資料不丟失
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_log_waits：等待 redo log 刷盤的次數
• Innodb_os_log_written：寫入 redo log 的字節數
• Binlog_cache_disk_use：binlog cache 不夠用的次數

本章小結#

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