mysqldump 備份恢復 MySQL 數據庫實戰：單庫、多庫、全庫、跨服務器同步、參數優化

核心備份恢復模板

快速參考

# 備份單庫
mysqldump -u root -p db_name > backup.sql

# 還原單庫（需先建庫）
mysql -u root -p -e "CREATE DATABASE db_name;"
mysql -u root -p db_name < backup.sql

場景一：單庫備份與恢復

# 導出（含結構+數據+存儲過程+觸發器）
mysqldump -u root -p \
  --single-transaction --routines --triggers --events \
  --master-data=2 --hex-blob --quick \
  sugarcrm > sugarcrm_$(date +%F).sql

# 壓縮存儲
mysqldump -u root -p sugarcrm | gzip > sugarcrm_$(date +%F).sql.gz

# 還原
gunzip -c sugarcrm_2024-01-15.sql.gz | mysql -u root -p sugarcrm
# 或
mysql -u root -p sugarcrm < sugarcrm_2024-01-15.sql

場景二：多庫備份

# 指定多庫
mysqldump -u root -p --databases bugs sugarcrm > bugs_sugarcrm.sql

# 驗證包含庫
grep "Current Database:" bugs_sugarcrm.sql

場景三：全庫備份

mysqldump -u root -p --all-databases > all_dbs_$(date +%F).sql

場景四：單表備份

mysqldump -u root -p sugarcrm accounts_contacts > accounts_contacts.sql

場景五：跨服務器實時同步（一條命令）

# 源庫備份並直接導入目標庫（需目標庫已存在）
mysqldump -h src_host -u root -p src_db | mysql -h dst_host -u root -p dst_db

# 參數說明
# -h src_host: 源服務器
# -h dst_host: 目標服務器
# 管道直接傳輸，無中間文件，適合遷移、構建從庫

mysqldump 參數組解析

--opt 組（默認開啟）

等同於：--add-drop-table --add-locks --create-options --quick --extended-insert --lock-tables --set-charset --disable-keys

• 適用於中小表導出導入
• 大表建議關閉 --extended-insert 避免超大 SQL 行

--compact 組（精簡輸出）

等同於：--skip-add-drop-table --no-set-names --skip-disable-keys --skip-add-locks

• 適合調試、查看結構
• 不適合生產恢復（缺少 DROP、鎖、字符集設置）

生產環境推薦參數矩陣

進階技巧

1. 並行導出加速（mydumper）

mydumper -h localhost -u root -p -B db_name -o /backup -t 8 -c
# -t 8: 8線程並行, -c: 壓縮

2. 定點恢復（PITR）配合 Binlog

# 1. 全量恢復
mysql -u root -p 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/binlog.000001 | mysql -u root -p db_name

3. 異構遷移 DEFINER 處理

# 導出後批量替換
sed -i "s/DEFINER=\`[^@]*\`@\`[^@]*\`/DEFINER=CURRENT_USER/g" backup.sql
# 或指定目標用戶
sed -i "s/DEFINER=\`[^@]*\`@\`[^@]*\`/DEFINER=\`app\`@\`%\`/g" backup.sql

備份驗證清單

1. 文件大小非零、可解壓
2. head -20 backup.sql 包含版本、字符集、USE DATABASE
3. grep -c "CREATE TABLE" backup.sql 表數量預期一致
4. 測試環境恢復驗證：行數、關鍵業務數據、存儲過程/函數/觸發器
5. 記錄備份耗時、文件大小、壓縮比，建立基線

自動化部署建議

• 腳本化：參數化配置、錯誤處理、郵件/企業微信告警
• 定時任務：全量每日、增量每小時（Binlog）
• 異地存儲：上傳對象存儲（S3/OSS/COS）、異地機房
• 定期演練：月度隨機恢復測試，驗證 RTO/RPO