備份是資料庫災難恢復的最後防線。本文系統整理 mysqldump 邏輯備份、mysqlhotcopy 物理備份（MyISAM 專用）、壓縮管道、跨伺服器遷移、還原驗證的完整指令集，並說明 MyISAM 與 InnoDB 參數差異。

一、mysqldump 邏輯備份（通用、跨版本、跨引擎）

1. MyISAM 表備份（鎖表一致性）

/usr/local/mysql/bin/mysqldump \
  -u yejr -p yejr \
  --default-character-set=utf8 \
  --opt --extended-insert=false \
  --triggers -R --hex-blob -x \
  db_name > db_name.sql

-x / --lock-all-tables：鎖定所有表，確保 MyISAM 一致性。

2. InnoDB 表備份（熱備份、不鎖表）

/usr/local/mysql/bin/mysqldump \
  -u yejr -p yejr \
  --default-character-set=utf8 \
  --opt --extended-insert=false \
  --triggers -R --hex-blob \
  --single-transaction \
  db_name > db_name.sql

關鍵差異：--single-transaction 替代 -x，利用 MVCC 實現一致性非阻塞備份，**InnoDB 必加**。

3. 常用場景組合

場景	命令範例
僅結構	mysqldump -u user -p --no-data --add-drop-table db > schema.sql
僅資料	mysqldump -u user -p --no-create-info db > data.sql
指定表	mysqldump -u user -p db tbl1 tbl2 > tables.sql
多庫備份	mysqldump -u user -p --databases db1 db2 db3 > multi.sql
全例備份	mysqldump -u user -p --all-databases > all.sql
帶 WHERE 條件	mysqldump -u user -p --where="id>100" db tbl > part.sql
壓縮輸出	mysqldump -u user -p db | gzip > db.sql.gz

二、還原與導入

1. mysql 客戶端直接導入（推薦）

/usr/local/mysql/bin/mysql -u yejr -p yejr db_name < db_name.sql

2. SOURCE 命令（需進入交互模式，指定絕對路徑）

mysql -u yejr -p yejr
mysql> USE db_name;
mysql> SOURCE /tmp/db_name.sql;

3. 從壓縮檔直接導入

gunzip < db_name.sql.gz | mysql -u yejr -p yejr db_name
# 或
zcat db_name.sql.gz | mysql -u yejr -p yejr db_name

三、mysqlhotcopy 物理備份（僅 MyISAM、本機、已棄用）

⚠️ 已在 MySQL 5.7+ 移除，僅供舊版 MyISAM 環境參考。InnoDB 請用 mysqldump --single-transaction 或 Percona XtraBackup。

備份範例

# 單庫
mysqlhotcopy -h localhost -u yejr -p yejr db_name /backup/dir

# 多庫
mysqlhotcopy -h localhost -u yejr -p yejr db1 db2 db3 /backup/dir

# 正則匹配庫名
mysqlhotcopy -h localhost -u yejr -p yejr 'db_name.*' /backup/dir

還原步驟

# 1. 停止 MySQL
systemctl stop mysqld

# 2. 複製資料庫目錄到 datadir
cp -rf /backup/dir/db_name /var/lib/mysql/

# 3. 修正權限
chown -R mysql:mysql /var/lib/mysql/db_name

# 4. 啟動 MySQL
systemctl start mysqld

四、Linux Shell 管道備份與跨機遷移

1. 本地壓縮備份

shell> mysqldump --quick db_name | gzip > db_name.contents.gz

2. 還原/跨機遷移（單條管道，無落地檔案）

# 還原
shell> gunzip < db_name.contents.gz | mysql db_name

# 遷移至遠端主機
shell> mysqldump -u user -p db_name | mysql --host=remote_ip -C db_name
# -C: 啟用壓縮協議傳輸

五、備份驗證與自動化建議

1. 定期測試還原：每月至少一次在測試環境完整還原，驗證數據完整性。
2. 校驗和對比：CHECKSUM TABLE、pt-table-checksum（主從一致性）。
3. 備份保留策略：每日全備保留 7 天、每週全備保留 4 週、每月全備保留 12 個月。
4. 異地備份：上傳至 S3/OSS/異地機房，防單點災難。
5. 監控告警：備份任務成功/失敗、備份檔大小異常（過小可能空庫）、備份耗時異常。

六、InnoDB 專用物理熱備份：Percona XtraBackup

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

# 完全備份（非阻塞）
xtrabackup --backup --target-dir=/backup/full --user=root --password=xxx

# 增量備份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full --user=root --password=xxx

# 準備（合併增量、應用日誌）
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc1

# 還原（需停庫、清空 datadir）
xtrabackup --copy-back --target-dir=/backup/full --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

XtraBackup 支援 InnoDB 熱備份、增量、壓縮、並行、流式傳輸，是**生產環境 InnoDB 物理備份的標準選擇**。