備份是 DBA 核心職責。本文系統整理 MySQL 五大備份路徑、還原實操、備份策略制定與災難恢復 SOP，涵蓋邏輯備份、物理備份、增量備份與時間點恢復（PITR）。
| 方法 | 類型 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|---|
| mysqldump | 邏輯 | 跨版本/跨架構可遷移、可壓縮、可篩選表 | 大庫慢、鎖表影響寫入 | 中小庫、異構遷移、開發環境 |
| 直接拷貝檔案 | 物理 | 極快、無邏輯解析開銷 | 需停庫/鎖表、僅限同架構/同版本、MyISAM 跨平台較易 | 大庫、維護窗口、同架構叢集 |
| SQL 語法 BACKUP/SELECT INTO OUTFILE | 邏輯/物理混合 | 線上備份、可自定義格式 | 不含索引、恢復慢、權限要求高 | 特定表導出、異構數據交換 |
| 二進制日誌 | 增量 | 增量備份、PITR、可做從庫備份分攤壓力 | 需啟用 binlog、恢復需全量基礎 | 7x24 高可用、增量備份 |
| LVM/ZFS 快照 + 物理備份 | 物理+快照 | 近零停機、瞬時一致性 | 需檔案系統支援、快照空間管理 | 大庫生產環境、雲端磁碟 |
--opt：預設開啟，等同 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset--single-transaction：InnoDB 熱備必加，配合 --quick 避免記憶體暴增--master-data=1：輸出 CHANGE MASTER TO 陳述句，記錄 binlog 位置，支援從庫建立--hex-blob：二進制欄位十六進制輸出，避免編碼問題--routines / --triggers：包含儲存過程/觸發器--compress：跨網路傳輸壓縮mysqldump -uuser -ppass --default-character-set=utf8 \
--opt --extended-insert=false --triggers -R --hex-blob -x dbname > dbname.sqlmysqldump -uuser -ppass --default-character-set=utf8 \
--opt --extended-insert=false --triggers -R --hex-blob \
--single-transaction dbname > dbname.sqlmysqldump --master-data=1 --single-transaction --flush-logs dbname > full.sql
# 之後每小時 FLUSH LOGS 產生新 binlog，備份增量 binlog 即可# 1. 鎖表刷新
mysql> FLUSH TABLES WITH READ LOCK;
# 2. 另開終端拷貝資料目錄
cp -rp /var/lib/mysql /backup/mysql_$(date +%F)
# 3. 釋放鎖
mysql> UNLOCK TABLES;
# InnoDB 額外備份 ib_logfile* 以便崩潰恢復server-id=1; log-bin=binlog; log-bin-index=binlog.index 重啟FLUSH LOGS; 切換 binlog → 拷貝舊 binlog 檔案mysqlbinlog /backup/binlog.000001 可篩選庫、時間範圍mysqlbinlog binlog.000001 | mysql -uuser -ppass dbname| 備份類型 | 還原指令 |
|---|---|
| mysqldump SQL | mysql -uuser -ppass dbname < dump.sql 或 SOURCE /path/dump.sql; |
| 直接拷貝 | 停服務 → 覆蓋 datadir → 啟動（InnoDB 需含 ib_logfile*） |
| BACKUP TABLE | RESTORE TABLE tbl FROM '/path/'; |
| SELECT INTO OUTFILE | LOAD DATA INFILE '/path/tbl.txt' INTO TABLE tbl [REPLACE|IGNORE]; |
| binlog 增量 | mysqlbinlog binlog.xxx | mysql -uuser -ppass dbname |
REPAIR TABLE/myisamchk 修復（MyISAM）innodb_force_recovery=1..6 嘗試啟動導出備份未恢複等於零；定期演練恢復流程，才是真正的資料安全。
