MySQL下的DB Link

前言：

在实际工作中，我们可能会遇到需要操作其他数据库实例的部分表，但又不想系统连接多库。此时我们就需要用到数据表映射。如同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引擎使用范围还不多，若确实有跨实例访问的需求，建议做好规范，个人总结最佳实践如下：

1. 源端专门创建只读权限的用户来供目标端使用。
2. 目标端建议用create server方式创建federated表。
3. federated表不宜太多，迁移时要特别注意。
4. 目标端应该只做查询使用，禁止在目标端更改federated表。
5. 建议目标端表名及结构和源端保持一致。
6. 源端表结构变更后 目标端要及时删除重建。

参考：