【3.3】mysql链接服务器，Federated存储引擎实现远程表，相当于sql server的连接服务器

MySQL中针对不同的功能需求提供了不同的存储引擎。

所谓的存储引擎也就是MySQL下特定接口的具体实现。

FEDERATED是其中一个专门针对远程数据库的实现。

一般情况下在本地数据库中建表会在数据库目录中生成相应的表定义文件，并同时生成相应的数据文件。

但通过FEDERATED引擎创建的表只是在本地有表定义文件，数据文件则存在于远程数据库中(这一点很重要)。

通过这个引擎可以实现类似Oracle 下DBLINK的远程数据访问功能。

【1】FEDERATED 引擎相关操作

（1.1）使用show engines 命令查看数据库是否已支持FEDERATED引擎

（1.2）开启federated引擎

vi /etc/my.cnf

在[mysqld]之后添加：
federated

（1.3）安装federated存储引擎

install plugin federated soname 'ha_federated.so';
mysql> install plugin federated soname 'ha_federated.so';
ERROR 1125 (HY000): Function 'federated' already exists

已经安装好了，只是没有启用（启用需要重启服务，在启动参数中加载）

（1.4）测试使用

[root@rhel6Mysql02 ~]# mysqld_safe --federated &
[1] 2194
[root@rhel6Mysql02 ~]# 130620 18:12:28 mysqld_safe Logging to '/var/lib/mysql/rhel6Mysql02.err'.
130620 18:12:28 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

若提示mysqld_safe A mysqld process already exists，说明mysql的进程占用了log文件，将mysql服务停止即可：

root@iZ94j7vkg4wZ:~#mysqld_safe --federated &

root@iZ94j7vkg4wZ:~# 160726 20:00:07 mysqld_safe Can't log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effect.
160726 20:00:07 mysqld_safe Logging to '/var/log/mysql/error.log'.
160726 20:00:07 mysqld_safe A mysqld process already exists
#5.7默认已经加载了federated，只需要启用就可以了

将mysql服务停止，然后再次运行即可：

　　root@iZ94j7vkg4wZ:~#service mysql status

重启mysql服务后，用show engines 命令再次查看，已支持

（1.5）使用FEDERATED建表语句如下

CREATE TABLE (......) ENGINE =FEDERATED CONNECTION='mysql://[username]:[password]@[ip]:[port]/[db-name]/[table-name]'

创建成功后就可直接在本地查询相应的远程表了。

简单创建测试：

# 源端表结构及数据
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)

（1.6）使用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'

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)

【2】FEDERATED 引擎表使用注意事项

（2.1）特点

基于MySQL5.7.23版本，笔者在源端及目标端实验了多种DDL及DML，现简单总结如下，有兴趣的同学可以试试看。

目标端建表结构可以与源端不一样推荐与源端结构一致
源端DDL语句更改表结构目标端不会变化
源端DML语句目标端查询会同步
源端drop表目标端结构还在但无法查询
目标端不能执行DDL语句
目标端执行DML语句源端数据也会变化
目标端truncate表源端表数据也会被清空
目标端drop表对源端无影响

本地虚拟表名与远程表名，可不相同。

经过测试，这个引擎的一些额外特点：

　　1. 本地虚拟表与远程实体表之间是 TCP 长连接，并且是多个客户端利用的。所以不用担心因频繁建立连接带来的网络开销。

　　2. 本虚拟表表与远程实体表之间的网络连接断开后，当对虚拟表发起查询时，它会尝试重新连接远程实体表，所以我们不用担心网络连接断开造成的永久中断问题。

　　3. 如果无时间未对本地虚拟表作任何操作，虚拟表与实体表之间的连接将在远程主机的 wait_timeout 秒后自动断开，当对虚拟表发起查询时，连接又会重新建立。

（2.2）使用时的注意事项【最佳实践】

对本地虚拟表的结构修改，并不会修改远程表的结构
truncate 命令，会清除远程表数据
drop命令只会删除虚拟表，并不会删除远程表
不支持 alter table 命令
源端专门创建只读权限的用户来供目标端使用。
目标端建议用CREATE SERVER方式创建FEDERATED表。
FEDERATED表不宜太多，迁移时要特别注意。
目标端应该只做查询使用，禁止在目标端更改FEDERATED表。
建议目标端表名及结构和源端保持一致。
源端表结构变更后目标端要及时删除重建。

（2.3）缺点

目前使用federated 最大的缺点：

1. select count(*), select * from limit M, N 等语句执行效率非常低，数据量较大时存在很严重的问题，但是按主键或索引列查询，则很快，如以下查询就非常慢（假设 id 为主索引）

select id from db.tablea where id >100 limit 10 ;

而以下查询就很快：

select id from db.tablea where id >100 and id<150

2. 如果虚拟虚拟表中字段未建立索引，而实体表中为此字段建立了索引，此种情况下，性能也相当差。但是当给虚拟表建立索引后，性能恢复正常。

3. 类似 where name like "str%" limit 1 的查询，即使在 name 列上创建了索引，也会导致查询过慢

是因为federated引擎会将所有满足条件的记录读取到本，再进行 limit 处理。

这几个问题已经严重影响了federated 在实际环境中的应用

【参考文档】

https://www.cnblogs.com/mysqljs/p/11131592.html

posted @ 2019-09-29 11:42 郭大侠1 阅读(877) 评论(0) 编辑收藏举报

刷新页面返回顶部