本地 MySQL 資料庫遷移至遠端伺服器完整操作指南：備份、匯入、驗證、避坑

本地開發完成後，將資料庫遷移至生產/測試伺服器是標準流程。本文提供兩種主流工具（phpMyAdmin、MySQL-Front）的圖文級操作步驟，涵蓋備份、清空目標庫、匯入、驗證、權限、字符集、大檔案分割等關鍵細節，確保遷移零差錯。

前置檢查清單

• ✅ 目標伺服器已建立資料庫、用戶、授權權限
• ✅ 字符集一致：建議統一 utf8mb4 / utf8mb4_unicode_ci
• ✅ 目標庫為**空庫**（無表結構、無數據），避免主鍵衝突、外鍵報錯
• ✅ 本地與遠端 MySQL 版本相容（建議同主版本，如 5.7→5.7、8.0→8.0）
• ✅ 網路通暢：遠端 3306 端口可連接（安全組/防火牆/白名單）

方案一：phpMyAdmin（瀏覽器操作，無需安裝客戶端）

1. 本地匯出

1. 開啟本地 phpMyAdmin（如 https://localhost/phpmyadmin）
2. 左側選中目標資料庫
3. 頂部選單 → 匯出 → 自訂
4. 關鍵設定：
   • 格式：SQL
   • 壓縮：gzipped（大檔案必開）
   • 結構：勾選 ADD DROP TABLE、IF NOT EXISTS、AUTO_INCREMENT
   • 數據：INSERT 模式選 INSERT IGNORE 或 REPLACE（視業務）
   • 編碼：utf8mb4
   • 輸出：另存為檔案
5. 點擊 執行 下載 db_name.sql.gz

2. 遠端匯入

1. 開啟遠端 phpMyAdmin
2. 選中目標資料庫（確認為空）
3. 頂部選單 → 匯入
4. 選擇剛下載的 .sql.gz 檔案
5. 字符集確認為 utf8mb4
6. 點擊 執行，等待完成

3. 大檔案匯入失敗應對

• phpMyAdmin 受限於 upload_max_filesize、post_max_size、max_execution_time、memory_limit
• 超過 50MB 建議改用命令行或分割檔案：
  zcat db.sql.gz | split -l 50000 - db_part_ → 依次匯入分割檔

方案二：MySQL-Front / Navicat / DataGrip（GUI 客戶端，功能更強）

1. 建立連線

• 主機：遠端 IP/域名
• 端口：3306
• 用戶/密碼：遠端資料庫帳號
• 編碼：UTF-8 / utf8mb4

2. 匯出（本地連線）

1. 右鍵資料庫 → 備份/匯出
2. 格式：SQL，選項同 phpMyAdmin
3. 輸出至本地檔案

3. 匯入（遠端連線）

1. 連線遠端，右鍵目標庫 → 還原/匯入
2. 選擇 SQL 檔案，執行
3. GUI 客戶端通常支援大檔案分批執行、錯誤繼續、進度顯示

方案三：命令行（大數據量、自動化、CI/CD 首選）

1. 匯出

# 單庫
mysqldump -h localhost -u root -p --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 --hex-blob \
  db_name | gzip > db_name_$(date +%F).sql.gz

# 多庫
mysqldump -h localhost -u root -p --databases db1 db2 ... | gzip > multi_db.sql.gz

關鍵參數：--single-transaction（InnoDB 一致性備份不鎖表）、--hex-blob（二進位欄位安全）、--routines/triggers/events（完整物件）。

2. 匯入

# 解壓並匯入
zcat db_name.sql.gz | mysql -h remote_host -u user -p -D target_db --default-character-set=utf8mb4

# 或先解壓再匯入（大檔案建議）
gunzip -c db_name.sql.gz | mysql -h remote_host -u user -p target_db

3. 進度監控

# 安裝 pv (Pipe Viewer)
apt-get install pv
zcat db_name.sql.gz | pv | mysql -h remote_host -u user -p target_db

遷移後驗證清單

常見坑位與解決

自動化遷移腳本範例（CI/CD 整合）

#!/bin/bash
# migrate_db.sh
set -euo pipefail

LOCAL_HOST="127.0.0.1"
LOCAL_USER="root"
LOCAL_PASS="local_pass"
LOCAL_DB="myapp"

REMOTE_HOST="db.prod.example.com"
REMOTE_USER="app_user"
REMOTE_PASS="prod_pass"
REMOTE_DB="myapp"

DUMP_FILE="/tmp/${LOCAL_DB}_$(date +%s).sql.gz"

echo "📦 Dumping local database..."
mysqldump -h "$LOCAL_HOST" -u "$LOCAL_USER" -p"$LOCAL_PASS" \
  --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 --hex-blob \
  "$LOCAL_DB" | gzip > "$DUMP_FILE"

echo "🚀 Importing to remote..."
zcat "$DUMP_FILE" | mysql -h "$REMOTE_HOST" -u "$REMOTE_USER" -p"$REMOTE_PASS" \
  --default-character-set=utf8mb4 "$REMOTE_DB"

echo "✅ Verifying table count..."
LOCAL_CNT=$(mysql -h "$LOCAL_HOST" -u "$LOCAL_USER" -p"$LOCAL_PASS" -N -e "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$LOCAL_DB';")
REMOTE_CNT=$(mysql -h "$REMOTE_HOST" -u "$REMOTE_USER" -p"$REMOTE_PASS" -N -e "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$REMOTE_DB';")

if [[ "$LOCAL_CNT" == "$REMOTE_CNT" ]]; then
  echo "✅ Table count match: $LOCAL_CNT"
else
  echo "❌ Table count mismatch: local=$LOCAL_CNT remote=$REMOTE_CNT"
  exit 1
fi

rm -f "$DUMP_FILE"
echo "🎉 Migration completed!"

資料庫遷移看似簡單，細節決定成敗。建立標準化 SOP、自動化腳本、驗證清單，才能在每次發布中做到「無感遷移、零故障」。