如果要在同一服务器上创建多个FEDERATED表,或者想简化创建FEDERATED表的过程,则可以使用该CREATE SERVER语句定义服务器连接参数,这样多个表可以使用同一个server。
CREATE SERVER创建的格式是:
CREATE SERVER fedlink FOREIGN DATA WRAPPER mysql OPTIONS (USER 'fed_user', PASSWORD '123456', HOST 'remote_host', PORT 3306, DATABASE 'federated');
之后创建FEDERATED表可采用如下格式:
CREATE TABLE (......) ENGINE =FEDERATED CONNECTION='test_link/tablename'
示例演示:
# 目标端创建指向源端的server mysql> CREATE SERVER test_link -> FOREIGN DATA WRAPPER mysql -> OPTIONS (USER 'root', PASSWORD 'root',HOST '10.50.60.212',PORT 3306,DATABASE 'source'); Query OK, 1 row affected (0.00 sec) mysql> select * from mysql.servers\G *************************** 1. row *************************** Server_name: test_link Host: 10.50.60.212 Db: source Username: root Password: root Port: 3306 Socket: Wrapper: mysql Owner: 1 row in set (0.00 sec) # 目标端创建FEDERATED表 mysql> CREATE TABLE `s1` ( -> `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键', -> `stu_id` int(11) NOT NULL COMMENT '学号', -> `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名', -> `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', -> `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', -> PRIMARY KEY (`increment_id`), -> UNIQUE KEY `uk_stu_id` (`stu_id`) -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT='学生表' CONNECTION='test_link/s1'; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE `s2` ( -> `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键', -> `stu_id` int(11) NOT NULL COMMENT '学号', -> `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名', -> `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', -> `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', -> PRIMARY KEY (`increment_id`), -> UNIQUE KEY `uk_stu_id` (`stu_id`) -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT='学生表' CONNECTION='test_link/s2'; Query OK, 0 rows affected (0.01 sec)
4.FEDERATED使用总结
基于MySQL5.7.23版本,笔者在源端及目标端实验了多种DDL及DML,现简单总结如下,有兴趣的同学可以试试看。
目标端建表结构可以与源端不一样 推荐与源端结构一致
源端DDL语句更改表结构 目标端不会变化
源端DML语句目标端查询会同步
源端drop表 目标端结构还在但无法查询
目标端不能执行DDL语句
目标端执行DML语句 源端数据也会变化
目标端truncate表 源端表数据也会被清空
目标端drop表对源端无影响
5.FEDERATED引擎最佳实践目前FEDERATED引擎使用范围还不多,若确实有跨实例访问的需求,建议做好规范,个人总结最佳实践如下:
源端专门创建只读权限的用户来供目标端使用。
目标端建议用CREATE SERVER方式创建FEDERATED表。
FEDERATED表不宜太多,迁移时要特别注意。
目标端应该只做查询使用,禁止在目标端更改FEDERATED表。
建议目标端表名及结构和源端保持一致。
源端表结构变更后 目标端要及时删除重建。
参考:
https://dev.mysql.com/doc/refman/5.7/en/federated-create.html
https://dev.mysql.com/doc/refman/5.7/en/create-server.html
Linux公社的RSS地址:https://www.linuxidc.com/rssFeed.aspx