MySQL FEDERATED 存储引擎的使用

jiawang · 发表于 2020-10-16 17:16:07

本帖最后由 jiawang 于 2020-10-16 17:18 编辑

FEDERATED 存储引擎描述

FEDERATED存储引擎能让你访问远程的MySQL数据库而不使用replication或cluster技术(类似于Oracle的dblink),使用FEDERATED存储引擎的表,
本地只存储表的结构信息,数据都存放在远程数据库上,查询时通过建表时指定的连接符去获取远程库的数据返回到本地。

FEDERATED存储引擎默认不启用

如果是使用的源码，需要使用CMake 加上DWITH_FEDERATED_STORAGE_ENGINE选项。

如果是二进制包,则在启动MySQL时指定 [--federated] 选项开启或在my.cnf文件中的[mysqld]部分加上federated参数

FEDERATED 存储引擎架构
1 本地服务器 FEDERATED 存储引擎的表只存放表的.frm结构文件
2 远程服务器存放了.frm和数据文件
3 增删改查操作都是通过建立的连接来访问远程数据库进行操作,把结果返回给本地。
4 远程数据表的存储引擎为MySQL支持的存储引擎,如MyISAM,InnoDB等

登录/注册后可看大图

664405f895dce79c97.png (73.22 KB, 下载次数: 363)

下载附件

2020-10-16 16:46 上传

FEDERATED 存储引擎操作步骤

操作步骤：

远程库:

开启 FEDERATED 存储引擎

建立远程访问用户

授予访问对象的权限

本地库：

测试登陆远程库是否能成

防火墙问题

selinux问题

创建 FEDERATED 表

查询是否成功

查询当前FEDERATED 存储引擎状态
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine          | Support | Comment                                                       | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED       | NO    | Federated MySQL storage engine                               | NULL       | NULL | NULL    |
| PERFORMANCE_SCHEMA | YES    | Performance Schema                                           | NO          | NO | NO       |
| InnoDB          | DEFAULT | Supports transactions, row-level locking, and foreign keys    | YES       | YES  | YES       |
| MRG_MYISAM       | YES    | Collection of identical MyISAM tables                         | NO          | NO | NO       |
| BLACKHOLE       | YES    | /dev/null storage engine (anything you write to it disappears) | NO          | NO | NO       |
| MEMORY          | YES    | Hash based, stored in memory, useful for temporary tables    | NO          | NO | NO       |
| MyISAM          | YES    | MyISAM storage engine                                        | NO          | NO | NO       |
| CSV             | YES    | CSV storage engine                                           | NO          | NO | NO       |
| ARCHIVE          | YES    | Archive storage engine                                        | NO          | NO | NO       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.04 sec)
显示NO就表示没打开，需要打开；

打开MYSQL的my.cny文件，进行编辑，在[mysqld]标签下添加federated
[mysql@sztech mysql]$ cd conf
[mysql@sztech conf]$ ll
total 4
-rwxrwxr-x. 1 mysql mysql 175 Sep  5 19:30 my.cnf
[mysql@sztech conf]$ vim my.cnf
[mysqld]
port=3306
federated
datadir=/wangjia/mysql/data
basedir=/setup/mysql/
user=mysql
default_authentication_plugin=mysql_native_password
[client]
socket=/wangjia/mysql/mysql.sock
~
~

设置完后重启mysql服务

验证：
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine          | Support | Comment                                                       | Transactions | XA  | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| FEDERATED       | YES    | Federated MySQL storage engine                               | NO          | NO  | NO       |
| PERFORMANCE_SCHEMA | YES    | Performance Schema                                           | NO          | NO  | NO       |
| InnoDB          | DEFAULT | Supports transactions, row-level locking, and foreign keys    | YES       | YES | YES       |
| MRG_MYISAM       | YES    | Collection of identical MyISAM tables                         | NO          | NO  | NO       |
| BLACKHOLE       | YES    | /dev/null storage engine (anything you write to it disappears) | NO          | NO  | NO       |
| MEMORY          | YES    | Hash based, stored in memory, useful for temporary tables    | NO          | NO  | NO       |
| MyISAM          | YES    | MyISAM storage engine                                        | NO          | NO  | NO       |
| CSV             | YES    | CSV storage engine                                           | NO          | NO  | NO       |
| ARCHIVE          | YES    | Archive storage engine                                        | NO          | NO  | NO       |
+--------------------+---------+----------------------------------------------------------------+--------------+-----+------------+
9 rows in set (0.16 sec)

mysql>

帐号		自动登录	找回密码
密码			注册

[命令及语法] MySQL FEDERATED 存储引擎的使用