資料庫備份是資料安全的最後一道防線。本文系統梳理 MySQL 備份的兩大核心流派——邏輯備份 (mysqldump) 與 物理備份 (文件複製/Percona XtraBackup),對比優劣勢,制定分級備份策略,並詳細說明恢復流程與最佳實踐。
| 維度 | 邏輯備份 (mysqldump/mydumper) | 物理備份 (XtraBackup/文件複製) |
|---|---|---|
| 原理 | 透過 SQL 協議導出 DDL+DML 語句 | 直接複製數據文件 (.ibd, .frm, .MYD 等) |
| 速度 | 慢(受 SQL 層解析、網路、單線程限制) | 快(接近磁碟 I/O 速度,支援並行、增量) |
| 一致性 | 易實現(單線程、鎖表、事務快照) | 需工具協調(XtraBackup 複製時應用 Redo Log 達到一致) |
| 跨版本/架構遷移 | ✅ 優秀(文本格式,可編輯、跨版本、跨架構) | ❌ 困難(需同版本、同存儲引擎、同頁面大小) |
| 單表/單庫恢復 | ✅ 容易(grep/sed 分割 SQL 檔) | ❌ 困難(需整備再導出,或使用 Transportable Tablespace) |
| 壓縮/加密 | ✅ 管道輕鬆實現 (gzip, openssl) | ✅ XtraBackup 內建支援 |
| 對業務影響 | 鎖表模式影響寫入;單事務模式長事務影響 Undo 回收 | 幾乎無影響(非阻塞,僅 FLUSH TABLES WITH READ LOCK 瞬間) |
| 適用場景 | 中小數據量 (<500GB)、異構遷移、邏輯錯誤恢復、開發測試環境構建 | 大數據量 (>500GB)、核心生產庫、RTO/RPO 要求嚴格、PITR |
# 生產環境推薦:單事務一致性 + 二進制日志位置 + 完整物件
mysqldump -u backup_user -p -h host -P port \
--single-transaction \
--master-data=2 \
--flush-logs \
--events --routines --triggers \
--hex-blob \
--quick \
--compress \
--databases db1 db2 > backup_$(date +%F_%H%M).sql
| 參數 | 作用 |
|---|---|
--single-transaction | InnoDB 專用,啟動 REPEATABLE READ 事務,不鎖表,獲取一致性快照 |
--master-data=2 | 輸出 CHANGE MASTER TO 註釋(含 Binlog 文件/位置),作為 PITR 基點 |
--flush-logs | 備份前切換 Binlog,新 Binlog 只記錄備份後變更,便於增量恢復 |
--quick | 不緩存結果集,逐行輸出,防止大表 OOM |
--hex-blob | 二進制欄位 (BLOB/BINARY) 以十六進制導出,避免字元集問題 |
--compress | 客戶端/服務端壓縮協議,減少網路傳輸 |
單表 > 10GB 建議拆分:
# 導出結構
mysqldump -d db_name big_table > big_table_schema.sql
# 分塊導出數據 (配合 WHERE 主鍵範圍)
mysqldump -t db_name big_table --where="id BETWEEN 1 AND 1000000" > big_table_part1.sql
mysqldump -t db_name big_table --where="id BETWEEN 1000001 AND 2000000" > big_table_part2.sql
# 來源 -> 目標 直接管道傳輸
mysqldump -h src_host -u user -p --single-transaction --databases db1 | \
mysql -h dst_host -u user -p --compress db1
XtraBackup 是唯一成熟的開源 InnoDB 熱備工具,支援全量、增量、壓縮、加密、並行、流式傳輸。
xtrabackup --backup --user=backup_user --password=xxx \
--target-dir=/backup/full_$(date +%F) --parallel=4 --compress --compress-threads=2
xtrabackup --prepare --target-dir=/backup/full_2023-10-01 --use-memory=4G
# 基於上次全量/增量的 LSN
xtrabackup --backup --incremental-basedir=/backup/full_2023-10-01 \
--target-dir=/backup/inc_$(date +%F_%H%M) --parallel=2
xtrabackup --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/inc1 ...xtrabackup --prepare --target-dir=/backup/fullxtrabackup --copy-back --target-dir=/backup/full --datadir=/var/lib/mysqlchown -R mysql:mysql /var/lib/mysql| 層級 | 頻率 | 工具 | 保留 | 目的 |
|---|---|---|---|---|
| L0 全量物理 | 每日 1 次 (低峰) | XtraBackup | 14 天 | 核心災難恢復基點,RTO 最短 |
| L1 增量物理 | 每 1-2 小時 | XtraBackup | 48 小時 | 精細 RPO,配合 Binlog 實現分鐘級 PITR |
| L2 邏輯全量 | 每週 1 次 | mysqldump | 8 週 | 跨版本遷移、邏輯錯誤恢復、審計歸檔 |
| Binlog 歸檔 | 實時同步 | rsync / Kafka / Canal | 7-30 天 | PITR 核心,異地災備 |
mysqlbinlog --start-position=XXX --stop-datetime="T" binlog.xxx | mysql -u root -ptbl_recover 導入數據。mysqlbinlog --database=db --table=tbl --start-datetime=... --stop-datetime=... 提取 Binlog 中該表的變更。REPLACE INTO/INSERT ... ON DUPLICATE KEY UPDATE 合併數據。{env}_{type}_{db}_{YYYYMMDD_HHMM}.{sql|xb},如 prod_xtra_full_order_20231001_0200.xb。沒有最好的備份工具,只有最適合的備份架構。核心建議:
備份不僅是 DBA 的職責,更是全鏈路工程質量的體現。從今天開始,給你的備份系統做一次體檢吧。