MySQL 大表備份策略：全量+增量邏輯備份、觸發器捕獲變更、Binlog 替代方案

超過 4GB 的大表每日全量備份極其痛苦，Binlog 又可能達 50-60GB/天 影響 IOPS。本文提出「全量基線 + 觸發器邏輯增量」方案：利用 AFTER INSERT/UPDATE/DELETE 觸發器捕獲變更寫入三張日誌表，定期導出 SQL 實現增量備份，避開 Binlog 效能代價。

方案架構：邏輯增量備份

-- 1. 原始業務表
CREATE TABLE wubx (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL DEFAULT 0,
    friend_id INT NOT NULL DEFAULT 0,
    dir_id INT NOT NULL DEFAULT 0,
    created INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

-- 2. 三張邏輯日誌表（結構同業務表，刪除日誌僅存主鍵）
CREATE TABLE wubx_ii LIKE wubx;  -- INSERT 日誌
CREATE TABLE wubx_uu LIKE wubx;  -- UPDATE 日誌（REPLACE INTO 去重）
CREATE TABLE wubx_dd (id INT PRIMARY KEY);  -- DELETE 日誌

觸發器定義（捕獲每次變更）

DELIMITER //
-- INSERT 觸發器
CREATE TRIGGER wubx_ii AFTER INSERT ON wubx
FOR EACH ROW BEGIN
    INSERT INTO wubx_ii SET
        id = NEW.id, user_id = NEW.user_id, friend_id = NEW.friend_id,
        dir_id = NEW.dir_id, created = NEW.created;
END//

-- UPDATE 觸發器（REPLACE 保留最新狀態）
CREATE TRIGGER wubx_uu AFTER UPDATE ON wubx
FOR EACH ROW BEGIN
    REPLACE INTO wubx_uu SET
        id = NEW.id, user_id = NEW.user_id, friend_id = NEW.friend_id,
        dir_id = NEW.dir_id, created = NEW.created;
END//

-- DELETE 觸發器
CREATE TRIGGER wubx_dd AFTER DELETE ON wubx
FOR EACH ROW BEGIN
    INSERT INTO wubx_dd VALUES (OLD.id);
END//
DELIMITER ;

模擬業務操作與日誌產生

INSERT INTO wubx VALUES (NULL, 1, 10, 1, 1198464252);
INSERT INTO wubx VALUES (NULL, 1, 11, 1, 1198464252);
INSERT INTO wubx VALUES (NULL, 1, 2, 1, 1198464252);
INSERT INTO wubx VALUES (NULL, 2, 10, 1, 1198464252);
INSERT INTO wubx VALUES (NULL, 2, 12, 1, 1198464252);
INSERT INTO wubx VALUES (NULL, 3, 12, 1, 1198464252);
UPDATE wubx SET dir_id = 5 WHERE user_id = 3;
UPDATE wubx SET dir_id = 4 WHERE user_id = 3;
DELETE FROM wubx WHERE user_id = 2 AND friend_id = 12;

增量備份導出（生成可重放 SQL）

-- 1. INSERT 操作 → REPLACE INTO（冪等）
mysql -e "SELECT CONCAT('REPLACE INTO wubx SET id=', id, ',user_id=', user_id,
    ',friend_id=', friend_id, ',dir_id=', dir_id, ',created=', created, ';')
    FROM wubx_ii" >> backup_ii.sql

-- 2. UPDATE 操作 → UPDATE ... WHERE id=
mysql -e "SELECT CONCAT('UPDATE wubx SET user_id=', user_id, ',friend_id=', friend_id,
    ',dir_id=', dir_id, ',created=', created, ' WHERE id=', id, ';')
    FROM wubx_uu" >> backup_uu.sql

-- 3. DELETE 操作 → DELETE ... WHERE id=
mysql -e "SELECT CONCAT('DELETE FROM wubx WHERE id=', id, ';')
    FROM wubx_dd" >> backup_dd.sql

恢復流程

1. 還原全量基線備份（mysqldump 或檔案複製）。
2. 按順序執行 backup_ii.sql、backup_uu.sql、backup_dd.sql。
3. 驗證資料一致性：CHECKSUM TABLE wubx 對比主從。

自動化與清理（生產環境必備）

-- 定時任務：每小時導出增量、清理已備份日誌
DELIMITER //
CREATE PROCEDURE flush_incremental_backup()
BEGIN
    -- 導出 SQL（略，同上）
    -- 清理已備份資料，保留未備份窗口
    DELETE FROM wubx_ii WHERE id <= @last_backup_max_id;
    DELETE FROM wubx_uu WHERE id <= @last_backup_max_id;
    DELETE FROM wubx_dd WHERE id <= @last_backup_max_id;
END//
DELIMITER ;

方案對比

延伸閱讀

• MySQL 分表處理實現方法
• MySQL 授權表使用示例
• MySQL 多表刪除語法完全指南
• MySQL 獨立表空間優缺點深度分析
• MySQL MyISAM 表結構恢復