MySQL 資料庫備份與還原完整指南：mysqldump、實體複製、Binlog 時間點恢復 (PITR)

備份策略三角權衡

mysqldump 核心參數速查

# 全庫備份 (含建表語句)
mysqldump -u root -p --single-transaction --routines --triggers --events --all-databases > full_$(date +%F).sql

# 單庫備份 (加 DROP TABLE 防重複匯入錯誤)
mysqldump -u root -p --single-transaction --add-drop-table db_name > db_$(date +%F).sql

# 大表優化 (避免記憶體暴增)
mysqldump -u root -p --quick --single-transaction huge_table > huge.sql

# 最高效組合 (生產常用)
mysqldump -u root -p --opt --single-transaction --master-data=2 db_name | gzip > db_$(date +%F).sql.gz
# --opt = --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
# --master-data=2 記錄 Binlog 座標為註釋，便於 PITR

跨機器直接同步 (無落地檔案)

# 建立目標庫
mysqladmin -h target_host -u root -p create db_name
# 管道串流
mysqldump -u root -p --single-transaction --master-data=2 db_name | mysql -h target_host -u root -p db_name

實體複製步驟 (冷備份)

1. systemctl stop mysqld
2. rsync -av /var/lib/mysql/ /backup/mysql_$(date +%F)/
3. systemctl start mysqld

熱備份 (LVM/ZFS 快照)： lvcreate -L10G -s -n mysql_snap /dev/vg0/mysql → 掛載 → rsync → 釋放快照。

時間點恢復 (PITR) 完整流程

# 1. 還原基線備份 (mysqldump 或 實體)
mysql -u root -p db_name < full_2024-01-10.sql

# 2. 套用 Binlog 從備份點到故障點
# 確認備份對應 Binlog 位置 (--master-data=2 註釋中)
mysqlbinlog --start-position=154 --stop-datetime="2024-01-15 10:00:00" \
  /var/lib/mysql/mysql-bin.000001 | mysql -u root -p db_name

# 3. 多個 Binlog 檔案依序套用
mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -u root -p db_name

備份命名與保留策略

/backup/
  ├─ full_2024-01-10.sql.gz          # 每日全備
  ├─ incr_2024-01-10_0600.binlog.gz  # 每 6 小時增量 Binlog 打包
  └─ retention_policy.sh             # 保留 30 天全備、7 天增量

黃金法則

1. 定期驗證還原： 每季在測試環境完整演練。
2. 備份異地： 至少兩地三中心 (本地 + 異地雲存)。
3. 監控備份成功率： 搭配 Prometheus + Alertmanager 告警。
4. 啟用 Binlog (ROW 格式)： PITR 必備條件。

備份的價值不在「備」而在「還原」，未經驗證的備份 = 廢紙。