MySQL 資料庫同機跨庫複製與大量資料遷移實戰

MySQL 同機跨庫複製與大量資料遷移實戰

本文記錄同一 MySQL 實例內不同資料庫間的資料複製，以及 GB 級大資料庫的高效遷移技巧。

一、同機跨庫直接複製（小資料量）

mysqldump Portal_DEV -u root -ppassword1$ --add-drop-table | mysql Portal_Optimize -u root -ppassword1$

注意：在 Shell 終端機執行，非 MySQL 命令列。管道流避免中間檔案落地，速度較快。

二、常見錯誤與解決

ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

原因：單行/單封包超過預設 1MB/4MB 限制。

解決：修改 my.cnf：

[mysqld]
max_allowed_packet = 32M

重啟 MySQL 服務生效。

三、大容量資料庫遷移（1GB+）

1. 壓縮導出

mysqldump -uroot -ppassword --quick Portal_DEV | gzip > Portal_DEV.contents.gz

• --quick：逐行讀取寫入管道，不載入全部結果至記憶體
• gzip：即時壓縮，減少磁碟 I/O 與空間佔用

2. 解壓導入

gunzip < Portal_DEV.contents.gz | mysql -uroot -ppassword Portal_Optimize

同樣採用管道流，邊解壓邊導入，無需完整解壓檔案。

四、進階優化參數

五、遷移檢查清單

1. 來源/目標版本、字符集、SQL_MODE 一致性確認
2. 目標庫預先建立，權限配置完成
3. 關閉目標庫非必要索引、外鍵檢查（SET FOREIGN_KEY_CHECKS=0;）
4. 導入後 ANALYZE TABLE 更新統計資訊
5. 核對表數量、行數、關鍵業務資料抽樣驗證
6. 啟用應用驗證，觀察錯誤日誌與慢查詢

六、替代方案對比

• Percona XtraBackup：物理熱備份，速度最快，支援增量，適合 TB 級
• mydumper/myloader：多執行緒並行導出導入，比 mysqldump 快 3~10 倍
• CDC 工具：Canal、Debezium 實時同步，適合零停機遷移