您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 資料備份與還原完整技術指南：mysqldump、二進位日誌、熱備份與災難恢復

編輯：MySQL 綜合教學

備份工具與方法總覽

方法	類型	適用引擎	優點	缺點	適用場景
mysqldump	邏輯備份	所有	跨版本相容、可編輯、單表恢復	大資料庫慢、鎖表影響寫入	中小資料庫、遷移升級、開發環境
mysqlhotcopy	物理備份	MyISAM	極速、僅鎖表瞬間	僅 MyISAM、本機執行、Perl 相依	純 MyISAM、需極速備份
二進位日誌	增量備份	所有	時間點恢復 (PITR)、增量、無停機	需啟用、恢復複雜、佔磁碟	生產環境核心、高可用架構
直接複製檔案	物理冷備份	所有	最快、完整一致	需停機、無增量	維護窗口、叢集備份節點
Percona XtraBackup	物理熱備份	InnoDB	不鎖表、支援增量、壓縮、流式	需安裝、學習曲線	大型 InnoDB 生產環境首選
MySQL Enterprise Backup	物理熱備份	所有	官方支援、GUI、加密、雲整合	商業授權	企業級合規需求

mysqldump 核心參數深度解析

# MyISAM 最佳化參數
mysqldump -u$user -p$pwd --default-character-set=utf8mb4 \
  --opt --extended-insert=FALSE --triggers --routines --events \
  --hex-blob --lock-all-tables \
  db_name > db_name.sql

# InnoDB 最佳化參數 (無鎖、一致性快照)
mysqldump -u$user -p$pwd --default-character-set=utf8mb4 \
  --opt --extended-insert=FALSE --triggers --routines --events \
  --hex-blob --single-transaction --master-data=2 \
  db_name > db_name.sql

參數	功能	關鍵說明
`--opt`	預設開啟組合	等同 `--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset`
`--single-transaction`	InnoDB 一致性快照	啟動時 `BEGIN`，不阻塞寫入，僅適用事務引擎
`--lock-all-tables`	全域讀鎖	MyISAM 確保一致性，會阻塞所有寫入
`--master-data=2`	記錄 Binlog 位置	註解形式輸出 `CHANGE MASTER TO`，從庫同步起點
`--extended-insert=FALSE`	單行 INSERT	避免超大包導入失敗，利於 diff 比對，稍大檔案
`--hex-blob`	二進位欄位 HEX	BINARY/VARBINARY/BLOB 必須，避免字元集問題
`--quick`	串流輸出	大表必須，避免記憶體爆炸
`--routines --triggers --events`	包含程式物件	完整遷移必須

還原策略

邏輯備份還原

# 方法一：mysql 客戶端管道 (推薦)
mysql -u$user -p$pwd --default-character-set=utf8mb4 db_name < db_name.sql

# 方法二：SOURCE 指令 (需絕對路徑、mysqld 使用者可讀)
mysql> SOURCE /full/path/db_name.sql;

# 大檔案分批導入技巧
split -l 10000 db_name.sql chunk_
for f in chunk_*; do mysql -u$user -p$pwd db_name < $f; done

二進位日誌增量恢復 (PITR)

啟用 Binlog：my.cnf → log-bin=mysql-bin、server-id=1、binlog_format=ROW、expire_logs_days=7
全量備份基準點：執行 FLUSH LOGS 產生新 Binlog，配合 mysqldump --master-data=2 記錄位置
增量備份：定期複製/同步新產生的 Binlog 檔至異地
恢復流程：

# 1. 還原全量備份
mysql -u$user -p$pwd db_name < full_backup.sql

# 2. 重放 Binlog 至指定時間點
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
            --stop-datetime="2024-01-15 14:30:00" \
            /var/lib/mysql/mysql-bin.000123 | mysql -u$user -p$pwd db_name

# 或指定位置
mysqlbinlog --start-position=12345 --stop-position=67890 \
            /var/lib/mysql/mysql-bin.000123 | mysql -u$user -p$pwd db_name

物理熱備份：Percona XtraBackup 實戰

# 安裝
yum install percona-xtrabackup-80  # 或 apt

# 完整備份 (不鎖表)
xtrabackup --backup --target-dir=/backup/full_$(date +%Y%m%d) \
  --user=backup_user --password=pwd --datadir=/var/lib/mysql

# 準備 (應用重做日誌，使資料一致)
xtrabackup --prepare --target-dir=/backup/full_$(date +%Y%m%d)

# 增量備份
xtrabackup --backup --target-dir=/backup/inc_$(date +%Y%m%d_%H%M) \
  --incremental-basedir=/backup/full_$(date +%Y%m%d) \
  --user=backup_user --password=pwd

# 恢復流程
# 1. 準備完整備份
xtrabackup --prepare --target-dir=/backup/full_...
# 2. 合併增量
xtrabackup --prepare --target-dir=/backup/full_... --incremental-dir=/backup/inc_...
# 3. 複製回資料目錄 (需停止 mysqld)
rsync -av /backup/full_.../ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

備份策略設計矩陣

業務等級	全量頻率	增量頻率	保留期	異地同步	恢復演練
核心交易 (金融/訂單)	每日	每小時 / Binlog 即時同步	90 天 / 7 年 (合規)	即時 (雙活/從庫)	月度
重要業務 (會員/商品)	每日	每 6 小時	30 天	每日	季度
一般業務 (日誌/分析)	每週	每日	14 天	每週	半年
開發/測試	按需	無	7 天	無	無

資料表維護與災難恢復

引擎	檢查/修復	優化/整理	碎片整理
MyISAM	`CHECK TABLE` / `REPAIR TABLE` / `myisamchk -r`	`OPTIMIZE TABLE`	`ANALYZE TABLE` 更新統計
InnoDB	啟動時自動 `innodb_force_recovery`	`OPTIMIZE TABLE` (重建表)	`ALTER TABLE t ENGINE=InnoDB` (Null 操作重建)

InnoDB 災難恢復關鍵參數

[mysqld]
innodb_force_recovery = 1  # 1-6 遞增積極度
# 1: SRV_FORCE_IGNORE_CORRUPT
# 4: SRV_FORCE_NO_IBUF_MERGE (常用最大值)
# 6: SRV_FORCE_NO_UNDO_LOG_SCAN (最後手段，可能遺失資料)

流程：停機 → 備份資料目錄 → 設定 innodb_force_recovery=4 → 啟動 → mysqldump 導出所有表 → 重建乾淨實例 → 匯入 → 移除參數重啟。