MySQL 備份與恢復全景指南：邏輯/物理、冷/熱、工具鏈與實戰

備份分類體系

邏輯備份工具鏈

mysqldump

• 一致性保障：--single-transaction（InnoDB，不可與 MyISAM 混用）、--lock-all-tables（全鎖）、--master-data=2（記錄 Binlog 位點）。
• 常用參數：-R（存儲過程）、-E（事件）、--triggers、--hex-blob、--quick（流式讀取）。
• 恢復：mysql -u root -p < backup.sql。

SELECT INTO OUTFILE / mysqlimport

• 導出：SELECT * INTO OUTFILE '/tmp/tbl.txt' FIELDS TERMINATED BY ',' FROM tbl;
• 導入：LOAD DATA INFILE 或 mysqlimport --local。
• 缺點：無結構定義，需手動建表；字符集、分隔符易出錯。

物理熱備份：xtrabackup (Percona) 核心原理

兩大工具

• xtrabackup：僅備份 InnoDB/XtraDB，不鎖表。
• innobackupex：腳本封裝，支援 MyISAM（需全局讀鎖 FLUSH TABLES WITH READ LOCK）。

備份流程

1. 記錄起始 LSN（Log Sequence Number）。
2. 拷貝 .ibd/.ibdata 文件（過程中頁面可能變更）。
3. 後台線程監聽 Redo Log，將變更頁面追寫至 xtrabackup_logfile。
4. 拷貝完成 → FLUSH TABLES WITH READ LOCK → 刷新 MyISAM/.frm → 釋放鎖。

增量備份

# 基於上次備份 LSN
xtrabackup --backup --incremental --incremental-basedir=/backup/base --target-dir=/backup/inc1

本質是備份 Redo Log 區間，極快。

恢復流程

1. xtrabackup --prepare --apply-log-only --target-dir=/backup/base（基礎備份）
2. xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1（應用增量）
3. 重複應用所有增量 → xtrabackup --prepare --target-dir=/backup/base（最終回滾未提交事務）
4. 複製回 datadir，修正權限，啟動 MySQL。

二進制日誌時間點恢復 (PITR)

# 按時間點
mysqlbinlog --stop-datetime="2023-01-01 10:00:00" binlog.000001 | mysql -u root -p
mysqlbinlog --start-datetime="2023-01-01 10:00:00" binlog.000001 | mysql -u root -p

# 按位置
mysqlbinlog --stop-position=368312 binlog.000001 | mysql -u root -p
mysqlbinlog --start-position=368315 binlog.000001 | mysql -u root -p

關鍵概念：LSN (Log Sequence Number)

• Redo Log 中每條記錄的遞增標識，標識寫入位置。
• 崩潰恢復、增量備份、Page Cleaner 刷盤均依賴 LSN 推進。
• 5.6.3+ LSN 為 8 字節（最高 ~256 TB）。

LVM 快照備份補充

• lvcreate -L10G -s -n mysql_snap /dev/vg/mysql 創建只讀快照。
• 掛載快照 → 物理拷貝或 xtrabackup --prepare 於快照目錄。
• 釋放快照 lvremove /dev/vg/mysql_snap。
• 優點：對主庫影響極小（秒級凍結），缺點：需 LVM/雲盤支援。

備份策略建議

• 每日全量（xtrabackup/LVM）+ 每小時增量（xtrabackup/二進制日誌）。
• 定期恢復演練（至少季度一次），驗證備份可用性、RTO/RPO。
• 異地多副本（至少 3-2-1 原則）。
• 監控備份任務成功率、耗時、檔案完整性校驗。