如何通过MySQL查询语句实现跨服务器数据库表数据迁移？

嘿，这个需求我之前帮同事处理过，纯用MySQL语句跨服务器传输数据完全可行，主要有两种靠谱的方案，我给你详细拆解下：

方案一：用FEDERATED存储引擎直接跨库读写 #

这个方案不需要中转文件，直接通过MySQL的引擎特性建立跨服务器的表映射，然后用普通的INSERT...SELECT完成数据迁移。

步骤： #

1. 先确认源服务器开启FEDERATED引擎
   登录源服务器的MySQL，执行以下命令检查：

   SHOW ENGINES;
   

   如果FEDERATED的Support列是NO，需要修改MySQL配置文件（比如my.cnf或my.ini），添加一行：

   federated=ON
   

   然后重启MySQL服务生效。

2. 在目标服务器创建FEDERATED映射表
   登录目标服务器的MySQL，创建一个和源表结构完全一致的FEDERATED表，用来映射源服务器的目标表：

   CREATE TABLE source_table_mirror (
     -- 这里要和源服务器的source_table字段、类型、约束完全一致
     id INT PRIMARY KEY AUTO_INCREMENT,
     username VARCHAR(50) NOT NULL,
     email VARCHAR(100),
     create_time DATETIME DEFAULT CURRENT_TIMESTAMP
   ) ENGINE=FEDERATED
   DEFAULT CHARSET=utf8mb4
   CONNECTION='mysql://源服务器用户名:源服务器密码@源服务器IP:端口/源数据库名/source_table';
   

   注意：CONNECTION参数里的用户名需要有源服务器的远程访问权限（可以在源服务器执行GRANT SELECT ON 源数据库名.source_table TO '用户名'@'目标服务器IP';授权）。

3. 执行数据迁移
   现在这个映射表就相当于源表的“远程镜像”，直接用INSERT...SELECT把数据导入目标表：

   INSERT INTO 目标数据库名.目标表名 (id, username, email, create_time)
   SELECT id, username, email, create_time FROM source_table_mirror;
   

注意事项： #

• FEDERATED引擎不支持事务、外键，也不兼容部分存储引擎的特殊特性，适合中小规模数据迁移；
• 映射表的结构必须和源表完全匹配，否则会触发报错；
• 传输过程中如果源表有数据更新，映射表会实时同步，所以如果需要数据一致性，可以先在源服务器锁表：LOCK TABLES source_table READ;，迁移完成后再解锁：UNLOCK TABLES;。

方案二：用SELECT INTO OUTFILE + LOAD DATA INFILE 中转文件 #

这个方案通过导出源数据到文件，再导入到目标服务器，不需要开启额外引擎，但需要服务器有文件读写权限。

步骤： #

1. 在源服务器导出数据到文件
   登录源服务器的MySQL，执行导出语句：

   SELECT id, username, email, create_time
   INTO OUTFILE '/tmp/source_data_export.csv'
   FIELDS TERMINATED BY ',' 
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
   FROM 源数据库名.source_table;
   

   注意：

   • 文件路径需要MySQL运行用户（比如mysql用户）有写入权限；
   • 可以用SHOW VARIABLES LIKE 'secure_file_priv';查看MySQL允许的文件路径，必须把文件放在这个目录下，否则会报错。

2. 手动复制文件到目标服务器
   把源服务器上的/tmp/source_data_export.csv复制到目标服务器的相同路径（比如/tmp/），这个是系统操作，不属于SQL语句，但整个数据迁移的核心操作还是纯SQL完成的。

3. 在目标服务器导入数据
   登录目标服务器的MySQL，执行导入语句：

   LOAD DATA INFILE '/tmp/source_data_export.csv'
   INTO TABLE 目标数据库名.目标表名
   FIELDS TERMINATED BY ',' 
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
   (id, username, email, create_time);
   

注意事项： #

• 目标表的结构要和导出的字段顺序、类型匹配；
• 大数据量的话，这个方案比FEDERATED更稳定，不容易因为网络波动中断；
• 同样，如果需要数据一致性，导出前记得锁源表。

内容的提问来源于stack exchange，提问作者M. irfan