|
本帖最后由 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等
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>
|
|