MySQL FEDERATED引擎

最新推荐文章于 2024-09-12 21:42:09 发布

奋斗吧_攻城狮

最新推荐文章于 2024-09-12 21:42:09 发布

阅读量460

点赞数

分类专栏： MySQL/MariaDB 文章标签： mysql

本文链接：https://blog.csdn.net/yu757371316/article/details/62884786

版权

MySQL/MariaDB 专栏收录该内容

65 篇文章 2 订阅

订阅专栏

前言：随着应用复杂度的增加，数据库不断细化切分，导致应用程序中数据库应用就得复杂，凌乱。绝大部分程序人员可能都遇到这种情况，应用程序中需要连接多台数据库服务器，进行相应的操作。随着时间积累，太多的数据库服务器的连接逻辑出现在程序之中，这给程序的维护扩展，数据库维护工作带来极大的工作量。

而本文所描述的 federated属于 MySQL的一种特殊引擎，利用它可将本地数据表映射至远程 MySQL 数据表，从而就可以解决应用程序中繁多的跨机器连接数据库问题，拓扑图如下：

这里写图片描述

如此就可以构造出一个统一的数据访问入口，就大大提高了整个数据库系统的可维护性。
Federated引擎是基于表级别的，只能将本地数据表定义为 Federated 引擎并映射至远程实体表，无法实现基于库级别的整体映射。

在本文中，我们将启用Federated 引擎的数据库访问入口服务器称为本地数据库，而将本地数据表对应的远程数据表，称之为实体表。

本地数据库需要启用Federated 引擎支持，而远程数据表无须 Federated 引擎支持。 Federated 引擎表使用标准的 MySQL 客户端协议与远程数据库建立 TCP 连接。

开启federated存储引擎
先查看federated存储引擎存储引擎是否启用

SHOW ENGINES ;

这里写图片描述

默认mysql是没有开启federated存储引擎，我这里因为已经配置了。
开启federated存储引擎只需要在my.cnf文件中增加‘federated’就可以。

创建Federated 表的过程：

方法一、直接创建本地表
1、以root 登录远程 MySQL ，上创建合适的访问账号

grant all on DB1.* to 'federated'@'%' identified by 'federated';
flush privileges;

2、在远程MySQL 找到对应实体表的创建命令（如果是新表，请先建立好数据表，再执行此命令）
假设在远程mysql 上有库名 DB1, 表名 tag, 执行以下命令找到远程表的结构：

show create table DB1.tag

输出：

CREATE TABLE `tag` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `frequency` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

3、假设我们要将远程的DB1.tag 映射至本地 DB.TableA 表上。那么我们应该保持本地虚拟表与远程实体表结构一致（结构可以有所差异，但会造成使用，管理上的麻烦）。根据远程实体表的创建命令，创建本地虚拟表 ( 结构部分完全一样，创建表选项有所差异 ) ：

4、登录本地Mysql 服务器，创建相应的数据库及表：

create database DB;
use DB; 
CREATE TABLE `TableA` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `frequency` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=federated connection="mysql://federated:federated@127.0.0.1:3306/DB1/tag";

这时，即建立好了federated 虚拟表，实际上本地 MySQL 只创建了表定义文件 , 而没有数据文件。我们对本地虚拟表的数据修改，均会发送到远程机器上执行。
注意：本地创建的表名必须在远程服务器存在，创建的字段也必须是远程表中的字段，可以比远程表的字段少，但是不能多，本地存储引擎选择：ENGINE =FEDERATED，本地虚拟表名与远程表名，可不相同。

CONNECTION选项中的连接字符串的一般形式如下：

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

还有一些连接字符串的例子：

CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'

注意：配置密码作为纯文本的话会存在安全问题，运行show create table，show table status是可以见的

方法二：create server方法创建本地表

还可以通过create server方法创建基于远程服务器的链接，但是这种链接也是用于federated存储引擎，不能通过该链接来查询远程服务器的表。

语法：

CREATE SERVER server_name
    FOREIGN DATA WRAPPER wrapper_name
    OPTIONS (option [, option] ...)

option:
  { HOST character-literal
  | DATABASE character-literal
  | USER character-literal
  | PASSWORD character-literal
  | SOCKET character-literal
  | OWNER character-literal
  | PORT numeric-literal }

注意：wrapper_name只能是mysql,使用其它名称无法支持

1.创建server

CREATE SERVER server_10
    FOREIGN DATA WRAPPER mysql
    OPTIONS (HOST '192.168.137.10',USER 'root',PASSWORD 'root' ,PORT 3306,DATABASE 'study');

2.创建表

 CREATE TABLE y (name varchar(10)) ENGINE=FEDERATED CONNECTION='server_10';

同样创建的表名必须和远程服务器的表名一样，字段也必须是远程表中的字段，可以比远程表的字段要少但是不能多。

现在查询本地创建的表也就是查询远程表，同时对本地表的操作也会应用到远程服务器表中。

经过测试，这个引擎的一些额外特点：
1.　本地虚拟表与远程实体表之间是 TCP 长连接，并且是多个客户端利用的。所以不用担心因频繁建立连接带来的网络开销。
2. 本虚拟表表与远程实体表之间的网络连接断开后，当对虚拟表发起查询时，它会尝试重新连接远程实体表，所以我们不用担心网络连接断开造成的永久中断问题。
3. 如果无时间未对本地虚拟表作任何操作，虚拟表与实体表之间的连接将在远程主机的 wait_timeout 秒后自动断开，当对虚拟表发起查询时，连接又会重新建立。

一些注意事项：
1.　对本地虚拟表的结构修改，并不会修改远程表的结构
2.　 truncate 命令，会清除远程表数据
3. drop命令只会删除虚拟表，并不会删除远程表
4. 不支持 alter table 命令
目前使用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