MySQL 備份遷移與數據同步：mysqldump 實戰與自動化方案

MySQL 備份、遷移與同步最佳實踐

圖形化工具（如 EMS SQL Manager）在大數據量下易崩潰，官方 mysqldump 才是生產環境的可靠選擇。本文涵蓋單庫備份、跨庫遷移、定時同步架構設計。

一、核心命令：mysqldump 與 mysql

1. 單庫備份（含結構與數據）

mysqldump -h localhost -u root -padmin local_db > a.sql

常用參數：

• --single-transaction：InnoDB 熱備份，不鎖表（需配合 --master-data=2 記錄 binlog 位點）
• --routines --triggers --events：導出存儲過程、觸發器、事件
• --no-data：僅導出結構
• --no-create-info：僅導出數據
• --extended-insert=FALSE：每行單獨 INSERT，便於 diff 對比

2. 導入目標庫

mysql -h 110.110.110.110 -u root -padmin remote_db < a.sql

二、自動化同步架構（每日增量/全量）

經典三步走定時任務：

1. 01:00 本地 mysqldump 全量/增量備份 → .sql 文件
2. 02:00 scp/rsync/ftp 傳輸至遠端服務器
3. 03:00 遠端 mysql < file.sql 導入

# 本地備份腳本 backup.sh
#!/bin/bash
DATE=$(date +%F)
mysqldump -h localhost -u root -p'password' \
    --single-transaction --master-data=2 \
    --routines --triggers --events \
    mydb > /backup/mydb_${DATE}.sql
gzip /backup/mydb_${DATE}.sql

# 遠端同步腳本 sync.sh（配合 crontab）
#!/bin/bash
scp /backup/mydb_$(date +%F).sql.gz user@remote:/backup/
ssh user@remote "gunzip -c /backup/mydb_$(date +%F).sql.gz | mysql -u root -p'password' mydb"

三、進階同步方案對比

四、遷移實戰檢查清單

• [ ] 目標庫版本兼容性測試（mysql_upgrade）
• [ ] 字符集統一 utf8mb4，排序規則 utf8mb4_general_ci
• [ ] sql_mode 一致性（特別是 NO_AUTO_CREATE_USER、ONLY_FULL_GROUP_BY）
• [ ] 大表分批導入（按主鍵範圍 WHERE id BETWEEN ...）
• [ ] 導入後 ANALYZE TABLE 更新統計信息
• [ ] 驗證行數、校驗和、關鍵業務查詢結果

五、常見坑與對策

1. 導入卡住：關閉 foreign_key_checks=0、unique_checks=0、autocommit=0，導入後再開啟。
2. 時區不一致：備份前設置 SET time_zone='+00:00'; 或導入時指定 --tz-utc。
3. 權限遷移：單獨導出 mysql 系統庫或使用 pt-show-grants 重建授權。