MySQL 大型資料庫完整遷移實戰：mysqldump 導出、壓縮傳輸、source 導入進度監控

場景

• 舊庫：MySQL 5.0，CentOS，無 Web/FTP，單機。
• 新庫：MySQL 5.1，CentOS + Nginx。
• 資料量：~300 MB，phpMyAdmin 導入超時、記憶體不足。
• 直接複製 datadir 嘗試失敗（表空間/檔案不相容）。

遷移步驟

1. 舊庫導出（單庫）

mysqldump -u user -p dbname > dbname.sql
# 輸入密碼後生成 SQL 檔

2. 壓縮與傳輸

zip dbname.sql.zip dbname.sql
# 透過跳板機 scp 傳輸
scp dbname.sql.zip user@jump:/tmp/
scp user@jump:/tmp/dbname.sql.zip user@new_db:/data0/tuan/
# 或直接 scp 旧服务器 -> 新服务器（須網路互通）

3. 新庫導入（監控進度）

# 解壓
unzip dbname.sql.zip

# 進入 MySQL 客戶端
mysql -u root -p
# 建庫（若不存在）
CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE dbname;

# 執行導入（可見進度）
SOURCE /data0/tuan/dbname.sql;

SOURCE 在客戶端逐條執行，終端會顯示執行狀態，比管道導入 mysql < file 更易監控。

大檔案導入優化參數

# 導出時加速
mysqldump -u root -p dbname \
  --single-transaction \
  --quick \
  --extended-insert \
  --max_allowed_packet=1G \
  --net_buffer_length=1M \
  > dbname.sql

# 導入前調大新庫參數（my.cnf）
[mysqld]
max_allowed_packet=1G
innodb_buffer_pool_size=4G
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=2
sync_binlog=0
# 重啟後導入，完成後恢復原值

跨版本遷移注意

• 導出加 --routines --triggers --events 保留程式物件。
• 導入後執行 mysql_upgrade -u root -p 修復系統表。
• 檢查 sql_mode 相容性（NO_AUTO_CREATE_USER 等）。

經驗總結

1. 命令列 mysqldump + SOURCE 是大庫遷移標準做法。
2. 壓縮傳輸節省頻寬與時間。
3. 導入前調大 InnoDB 緩衝、日誌、封包參數，導入後還原。
4. 無 Web 環境時，scp + 跳板機是常規傳輸路徑。