MySQL 資料庫與資料表快速複製遷移實戰方法總結

MySQL 資料庫與資料表快速複製遷移實戰方法總結

無論是搭建測試環境、克隆專案還是資料遷移，MySQL 提供多種高效的資料庫/資料表複製方案。本文匯總命令行工具、SQL 語句及最佳實踐，涵蓋同機房與跨機房場景。

一、資料庫級複製：mysqldump 管道流式傳輸

同一伺服器複製

# 1. 建立目標資料庫
mysql -u root -p -e "CREATE DATABASE \`newdb\` DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;"

# 2. 管道流式複製（無中間檔案，效能最優）
mysqldump db1 -u root -p --add-drop-table | mysql newdb -u root -p

關鍵參數：--add-drop-table 確保目標表存在時先刪除再建立，避免衝突；-p 後直接接密碼無空格。

跨伺服器遠程複製

mysqldump db1 -u root -p --add-drop-table | mysql -h 192.168.1.22 newdb -u root -p

前提：目標 MySQL 允許遠程連線，網路頻寬與延遲可接受。大資料庫建議壓縮傳輸：mysqldump ... | gzip | ssh target "gunzip | mysql ..."。

二、資料表級複製：多種 SQL 方案對比

方案 A：結構+資料完整複製（推薦生產）

CREATE TABLE mytbl_new LIKE production.mytbl;
INSERT mytbl_new SELECT * FROM production.mytbl;

• LIKE 完整複製結構（含索引、主鍵、AUTO_INCREMENT 等）；
• INSERT ... SELECT 高效批量寫入資料。

方案 B：僅複製結構

CREATE TABLE new_table LIKE old_table;
-- 或
CREATE TABLE new_table SELECT * FROM old_table WHERE 0;

方案 C：CREATE TABLE ... SELECT（注意缺陷）

CREATE TABLE new_table SELECT * FROM old_table;

缺陷：新表不會繼承主鍵、索引、外鍵、AUTO_INCREMENT 等約束，僅為查詢結果集建表，後續需手動 ALTER TABLE 補齊，易出錯。

方案 D：進階靈活複製場景

• 跨庫複製：CREATE TABLE newdb.newtbl LIKE olddb.oldtbl;
• 指定欄位複製：CREATE TABLE newtbl (SELECT col1, col2 FROM oldtbl);
• 欄位重命名：CREATE TABLE newtbl (SELECT id, name AS uname FROM oldtbl);
• 條件篩選複製：CREATE TABLE newtbl (SELECT * FROM oldtbl WHERE name LIKE 'A%');
• 混合定義欄位與查詢：CREATE TABLE newtbl (id INT PRIMARY KEY AUTO_INCREMENT) SELECT * FROM oldtbl;

三、mysqldump 單表導出導入

# 導出單表結構+資料
mysqldump -u root -p db1 tbl1 > tbl1.sql

# 導入
mysql -u root -p db1 < tbl1.sql

結合 --no-data、--no-create-info 等參數可靈活控制導出內容。