前言:
在实际工作中, 我们可能会遇到需要操作其他数据库实例的部分表, 但又不想系统连接多库. 此时我们就需要用到数据表映射. 如同 Oracle 中的 DBlink 一般, 使用过 Oracle DBlink 数据库链接的人都知道可以跨实例来进行数据查询, 同样的, MySQL 自带的 FEDERATED 引擎完美的帮我们解决了该问题. 本篇文章介绍 FEDERATED 引擎的开启和使用.
1. 开启 FEDERATED 引擎
若需要创建 FEDERATED 引擎表, 则目标端实例要开启 FEDERATED 引擎. 从 MySQL5.5 开始 FEDERATED 引擎默认安装 只是没有启用, 进入命令行输入 show engines; FEDERATED 行状态为 NO.
- MySQL> show engines;
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | Engine | Support | Comment | Transactions | XA | Savepoints |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
- | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
- | CSV | YES | CSV storage engine | NO | NO | NO |
- | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
- | MyISAM | YES | MyISAM storage engine | NO | NO | NO |
- | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
- | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
- | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
- | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- 9 rows in set (0.00 sec)
在配置文件 [mysqld] 中加入一行: federated, 然后重启数据库, FEDERATED 引擎就开启了.
- MySQL> show engines;
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | Engine | Support | Comment | Transactions | XA | Savepoints |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | CSV | YES | CSV storage engine | NO | NO | NO |
- | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
- | MyISAM | YES | MyISAM storage engine | NO | NO | NO |
- | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
- | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
- | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
- | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
- | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
- | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- 9 rows in set (0.00 sec)
2. 使用 CONNECTION 创建 FEDERATED 表
使用 CONNECTION 创建 FEDERATED 引擎表通用模型:
- CREATE TABLE (......)
- ENGINE =FEDERATED CONNECTION='mysql://username:password@hostname:port/database/tablename'
简单创建测试:
- # 源端表结构及数据
- MySQL> show create table test_table\G
- *************************** 1. row ***************************
- Table: test_table
- Create Table: CREATE TABLE `test_table` (
- `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=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='学生表'
- 1 row in set (0.00 sec)
- MySQL> select * from test_table;
- +--------------+--------+----------+---------------------+---------------------+
- | increment_id | stu_id | stu_name | create_time | update_time |
- +--------------+--------+----------+---------------------+---------------------+
- | 1 | 1001 | wang | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
- | 2 | 1002 | dfsfd | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
- | 3 | 1003 | fdgfg | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
- | 4 | 1004 | sdfsdf | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
- | 5 | 1005 | dsfsdg | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
- | 6 | 1006 | fgd | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
- +--------------+--------+----------+---------------------+---------------------+
- 6 rows in set (0.00 sec)
- # 目标端建表及查询
- # 注意 ENGINE=FEDERATED CONNECTION 后为源端地址 避免使用带 @的密码
- MySQL> CREATE TABLE `test_table` (
- -> `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='mysql://root:root@10.50.60.212:3306/source/test_table';
- Query OK, 0 rows affected (0.01 sec)
- MySQL> select * from test_table;
- +--------------+--------+----------+---------------------+---------------------+
- | increment_id | stu_id | stu_name | create_time | update_time |
- +--------------+--------+----------+---------------------+---------------------+
- | 1 | 1001 | wang | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
- | 2 | 1002 | dfsfd | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
- | 3 | 1003 | fdgfg | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
- | 4 | 1004 | sdfsdf | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
- | 5 | 1005 | dsfsdg | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
- | 6 | 1006 | fgd | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 |
- +--------------+--------+----------+---------------------+---------------------+
- 6 rows in set (0.00 sec)
3. 使用 CREATE SERVER 创建 FEDERATED 表
如果要在同一服务器上创建多个 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://www.cnblogs.com/kunjian/p/11131592.html