您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 跨資料庫/跨表資料複製完整語法指南（同實例/異實例）

編輯：MySQL 資料庫相關知識

同一 MySQL 實例內跨資料庫複製

1. 表結構完全相同

-- 全量複製
INSERT INTO `db1`.`table_a` SELECT * FROM `db2`.`table_a`;

-- 去重複製（假設有主鍵/唯一鍵）
INSERT IGNORE INTO `db1`.`table_a` SELECT * FROM `db2`.`table_a`;

-- 限制筆數
INSERT INTO `db1`.`table_a` SELECT * FROM `db2`.`table_a` LIMIT 1000;

2. 表結構不同（欄位順序/名稱/數量不一）

-- 顯式指定欄位對應
INSERT INTO `db1`.`table_a` (`id`, `name`, `email`, `created_at`)
SELECT `user_id`, `username`, `mail`, `reg_time` FROM `db2`.`users`;

3. 建立新表並複製結構+資料（最常用）

-- 一次性建表+複製（結構含索引/主鍵/預設值，不含外鍵/觸發器/分區）
CREATE TABLE `db1`.`new_table` LIKE `db2`.`source_table`;
INSERT INTO `db1`.`new_table` SELECT * FROM `db2`.`source_table`;

4. 僅複製結構（不含資料）

CREATE TABLE `db1`.`empty_table` LIKE `db2`.`source_table`;

跨實例/跨伺服器複製

方案 A：mysqldump + 管道/檔案中轉（標準、支援壓縮/過濾）

# 1. 導出單表（含結構+資料）
mysqldump -h src_host -u user -p --single-transaction --quick \
  --databases src_db --tables source_table > /tmp/table.sql

# 2. 導入目標庫
mysql -h dst_host -u user -p dst_db < /tmp/table.sql

# 3. 一條管道命令完成（無中間檔，跨機房建議壓縮）
mysqldump -h src_host -u user -p --single-transaction --quick \
  src_db source_table | gzip -c | ssh dst_host "gunzip -c | mysql -u user -p dst_db"

方案 B：FEDERATED 引擎（同網段、小表、臨時同步）

-- 1. 目標庫建立映射表
CREATE TABLE `federated_table` (
  `id` INT NOT NULL,
  `name` VARCHAR(50) NOT NULL
) ENGINE=FEDERATED
CONNECTION='mysql://user:pass@src_host:3306/src_db/source_table';

-- 2. 直接查詢/複製
INSERT INTO `local_table` SELECT * FROM `federated_table` WHERE ...;

限制：源表需有索引、不支援事務、DDL 需手動同步、大表效能差。僅適合臨時小表探查。

方案 C：資料同步工具（生產環境推薦）

DataX（阿里開源，異構支援、插件化、分佈式）
Canal + Kafka（基於 Binlog 增量同步、準實時）
DTS / Cloud Vendor 工具（雲廠商托管、全量+增量、校驗修復）
pt-archiver（Percona Toolkit，歸檔/搬遷大表、分批不鎖表）

大表分批複製避坑指南（防鎖表、防主從延遲）

-- 範例：每批 5000 行，按主鍵範圍分批
-- 適用於單表 > 100 萬行或主從環境

DELIMITER //
CREATE PROCEDURE batch_copy()
BEGIN
  DECLARE v_min_id BIGINT DEFAULT 0;
  DECLARE v_max_id BIGINT DEFAULT 0;
  DECLARE v_batch_size INT DEFAULT 5000;
  DECLARE v_sleep_ms INT DEFAULT 100;

  SELECT MIN(id), MAX(id) INTO v_min_id, v_max_id FROM src_db.source_table;

  WHILE v_min_id <= v_max_id DO
    INSERT IGNORE INTO dst_db.target_table
    SELECT * FROM src_db.source_table
    WHERE id >= v_min_id AND id < v_min_id + v_batch_size;

    SET v_min_id = v_min_id + v_batch_size;
    DO SLEEP(v_sleep_ms / 1000);  -- 釋放鎖、緩解主從延遲
  END WHILE;
END//
DELIMITER ;

CALL batch_copy();

條件過濾導出（mysqldump --where）

# 僅導出 id > 1000000 的資料
mysqldump -u root -p src_db source_table \
  --where="id > 1000000" > /tmp/partial.sql

# 多條件
mysqldump -u root -p src_db source_table \
  --where="status=1 AND created_at > '2023-01-01'" > /tmp/active.sql

SELECT INTO OUTFILE / LOAD DATA INFILE（檔案級高速傳輸）

-- 1. 導出為 CSV（需 FILE 權限、secure_file_priv 目錄）
SELECT `id`, `name`, `email` 
INTO OUTFILE '/var/lib/mysql-files/export.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM `source_table`;

-- 2. 目標庫高速導入
LOAD DATA INFILE '/var/lib/mysql-files/export.csv'
INTO TABLE `target_table`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;  -- 若含標題行

速度比 INSERT 快 10~20 倍，適合 TB 級大表遷移。需注意 `secure_file_priv` 限制目錄、字符集一致、外鍵/觸發器暫時關閉。

常見錯誤與修正

錯誤	原因	修正
INSERT ... SELECT 卡住/極慢	無索引關聯、大事務鎖表、主從延遲	加索引、分批、關閉 binlog (`SET sql_log_bin=0`)、暫停從庫
CREATE TABLE ... LIKE 不複製外鍵/觸發器/分區	官方行為定義	手動補充 `SHOW CREATE TABLE` 獲取完整 DDL
mysqldump 導出報 "Access denied ... FILE privilege"	用戶無 FILE 權限或 secure_file_priv 限制	授權 `GRANT FILE ON . TO user` 或改用管道導入
FEDERATED 表查詢報 "Can't connect to MySQL server"	連線字串錯誤、防火牆、源表無索引	檢查 CONNECTION 字串、源表加索引、網路連通
LOAD DATA 導入資料截斷/亂碼	字符集不匹配、欄位長度不足、分隔符衝突	統一 `utf8mb4`、檢查欄位定義、用不衝突分隔符

總結：選型決策樹

資料量 < 10萬行、同實例
    → INSERT ... SELECT / CREATE TABLE LIKE + INSERT

資料量 10萬~1000萬、同實例/同機房
    → 分批存儲過程 / mysqldump 管道 / SELECT INTO OUTFILE + LOAD DATA

資料量 > 1000萬、跨機房/異構/需增量同步
    → DataX / Canal / DTS / pt-archiver

臨時探查、小表、不想導出檔案
    → FEDERATED 引擎（謹慎使用）