如何将现有InnoDB表克隆为FEDERATED引擎表？

我明白你遇到的困扰了——想用CREATE TABLE ... AS SELECT语句直接把现有的InnoDB表克隆成FEDERATED引擎的远程表，但这条语句确实无法正常工作。原因很简单：CREATE TABLE ... AS SELECT语法会自动继承原表的存储引擎（也就是InnoDB），而且不会保留FEDERATED表必需的远程连接配置参数。

下面是具体的解决方案，分步骤来操作：

步骤1：获取原表的完整结构定义 #

FEDERATED表的结构必须和远程目标表完全一致（包括字段类型、长度、约束、字符集等），所以首先要拿到原表的CREATE TABLE语句。执行这条命令：

SHOW CREATE TABLE balance;

你会得到类似这样的输出（以你的balance表为例）：

CREATE TABLE `balance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

步骤2：修改生成FEDERATED表的创建语句 #

复制上面的CREATE TABLE语句，做两处关键修改：

1. 将Engine=InnoDB替换为Engine=FEDERATED
2. 在语句末尾添加远程连接配置CONNECTION='mysql://user:pass@8.8.8.8:3306/db/balance'

修改后的最终语句如下：

CREATE TABLE `balance_live` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 CONNECTION='mysql://user:pass@8.8.8.8:3306/db/balance';

执行这条语句，就能成功创建出连接到远程balance表的FEDERATED表了。

进阶技巧：自动生成FEDERATED表创建语句 #

如果不想手动复制修改表结构，可以用这条SQL语句自动生成目标语句：

SELECT CONCAT(
  'CREATE TABLE `balance_live` ',
  REPLACE(
    REPLACE(
      (SELECT CREATE_TABLE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db' AND TABLE_NAME='balance'),
      'Engine=InnoDB',
      'Engine=FEDERATED'
    ),
    'DEFAULT CHARSET',
    'DEFAULT CHARSET CONNECTION=''mysql://user:pass@8.8.8.8:3306/db/balance'''
  )
) AS create_federated_table;

执行后会直接输出完整的FEDERATED表创建语句，复制执行即可。

关键注意事项 #

• 启用FEDERATED引擎：先确认你的MySQL服务器已经开启了FEDERATED引擎，执行SHOW ENGINES;查看，如果FEDERATED的Support列是YES就没问题；如果是NO，需要在my.cnf（Linux）或my.ini（Windows）中添加federated配置项，然后重启MySQL服务。
• 远程权限配置：确保CONNECTION字符串里的用户名和密码拥有远程数据库db中balance表的访问权限，同时远程MySQL服务器要允许你的本地IP连接（可以在远程服务器执行GRANT SELECT, INSERT, UPDATE, DELETE ON db.balance TO 'user'@'你的本地IP';，然后FLUSH PRIVILEGES;）。
• 结构一致性：FEDERATED表和远程表的结构必须完全匹配，否则查询、写入操作会报错。如果远程表结构变更，本地FEDERATED表也需要同步修改。

内容的提问来源于stack exchange，提问作者Reza Hajianpour