1. 背景
* 本地MySQL数据库要访问远程MySQL数据库的表中的数据, 必须通过FEDERATED存储引擎来实现.
* 有点类似Oracle中的数据库链接(DBLINK). 要允许这个存储引擎, 当构建MySQL时使用--with-federated-storage-engine来configure.
* 当创建一个FEDERATED表的时候, 服务器在数据库目录创建一个表定义文件. 文件由表的名字开始, 并有一个.frm扩展名.
* 无其它文件被创建, 因为实际的数据在一个远程数据库上.
2. 相关特性
* 允许本地访问远程 MySQL 数据库中表的数据
* 本地不存储任何数据文件
* 仅支持 MySQL 对 MySQL 的访问
* 不支持异构数据库的访问
* MySQL默认不开启Federated存储引擎
3. 环境
两个MySQL 5.7实例
serverA3306
serverB 3307[root@MySQL~]#mysql-S/tmp/mysql.sock1-p123456
mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.
mandsendwith;or\g.
YourMySQLconnectionidis7
Serverversion:5.7.18MySQLCommunityServer(GPL)
Copyright(c)2000,,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>selectversion();
+-----------+
|version()|
+-----------+
|5.7.18|
+-----------+
1rowinset(0.00sec)
[root@MySQL~]#mysql-S/tmp/mysql.sock2-p123456
mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.
mandsendwith;or\g.
YourMySQLconnectionidis6
Serverversion:5.7.18MySQLCommunityServer(GPL)
Copyright(c)2000,,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>selectversion();
+-----------+
|version()|
+-----------+
|5.7.18|
+-----------+
1rowinset(0.00sec)
4.Federated存储引擎设置
* 查看Federated是否开启 [ FEDERATED 中Support状态NO表明引擎未开启]mysql>showengines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
|Engine|Support|Comment|Transactions|XA|Savepoints|
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
|CSV|YES|CSVstorageengine|NO|NO|NO|
|MRG_MYISAM|YES|CollectionofidenticalMyISAMtables|NO|NO|NO|
|MyISAM|YES|MyISAMstorageengine|NO|NO|NO|
|BLACKHOLE|YES|/dev/nullstorageengine(anythingyouwritetoitdisappears)|NO|NO|NO|
|InnoDB|DEFAULT|Supportstransactions,row-levellocking,andforeignkeys|YES|YES|YES|
|PERFORMANCE_SCHEMA|YES|PerformanceSchema|NO|NO|NO|
|ARCHIVE|YES|Archivestorageengine|NO|NO|NO|
|MEMORY|YES|Hashbased,storedinmemory,usefulfortemporarytables|NO|NO|NO|
|FEDERATED|NO|FederatedMySQLstorageengine|NULL|NULL|NULL|
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9rowsinset(0.01sec)
* 配置文件指定开启Federated存储引擎[ /etc/f ][mysqld]
federated
* 重启MySQL 再次查看 [ FEDERATED 中Support状态成YES表明引擎开启成功 ]mysql>showengines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
|Engine|Support|Comment|Transactions|XA|Savepoints|
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
|CSV|YES|CSVstorageengine|NO|NO|NO|
|MRG_MYISAM|YES|CollectionofidenticalMyISAMtables|NO|NO|NO|
|MyISAM|YES|MyISAMstorageengine|NO|NO|NO|
|BLACKHOLE|YES|/dev/nullstorageengine(anythingyouwritetoitdisappears)|NO|NO|NO|
|InnoDB|DEFAULT|Supportstransactions,row-levellocking,andforeignkeys|YES|YES|YES|
|PERFORMANCE_SCHEMA|YES|PerformanceSchema|NO|NO|NO|
|ARCHIVE|YES|Archivestorageengine|NO|NO|NO|
|MEMORY|YES|Hashbased,storedinmemory,usefulfortemporarytables|NO|NO|NO|
|FEDERATED|YES|FederatedMySQLstorageengine|NO|NO|NO|
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9rowsinset(0.00sec)
5. 部署
*在ServerA上有一个数据库dbtestA，在ServerB上有数据库dbtestB，要在ServerB的数据库dbtestB上建立ServerA的数据库dbtestA上的表tabletestA的数据表链接remote_tabletestA，通过普通用户test连接。
* ServerA创建数据库mysql>createdatabasedbtestA;
QueryOK,1rowaffected(0.02sec)
* ServerA在dbtestA数据库中创建tabletestA表mysql>createtabletabletestA(
idINTPRIMARYKEYNOTNULLAUTO_INCREMENT
)ENGINE=INNODB;
QueryOK,0rowsaffected(0.11sec)
* ServerA中创建普通用户并授权dbtestA数据库相关权限mysql>createuser'test'@'127.0.0.1'IDENTIFIEDBY'123456';
QueryOK,0rowsaffected(0.00sec)
mysql>grantselectondbtestA.*to'test'@'127.0.0.1';
QueryOK,0rowsaffected(0.00sec)
* ServerB 中创建数据库 dbtestBmysql>createdatabasedbtestB;
QueryOK,1rowaffected(0.00sec)
* ServerB 在dbtestB中创建链接表remote_tabletestA, 使用普通用户testmysql>usedbtestB;
Databasechanged
mysql>createtableremote_tabletestA(
->idINTPRIMARYKEYNOTNULLAUTO_INCREMENT
->)ENGINE=FEDERATED
->CONNECTION='mysql://test:123456@127.0.0.1:3306/dbtestA/tabletestA';
QueryOK,0rowsaffected(0.09sec)
* 在ServerA dbtestA库tableA表中插入数据Databasechanged
mysql>usedbtestA;
Databasechanged
mysql>insertintotabletestAselectNULL;
QueryOK,1rowaffected(0.01sec)
Records:1Duplicates:0Warnings:0
mysql>insertintotabletestAselectNULL;
QueryOK,1rowaffected(0.03sec)
Records:1Duplicates:0Warnings:0
mysql>insertintotabletestAselectNULL;
QueryOK,1rowaffected(0.01sec)
Records:1Duplicates:0Warnings:0
mysql>select*fromtabletestA;
+----+
|id|
+----+
|1|
|2|
|3|
+----+
3rowsinset(0.01sec)
* ServerB dbtestB库链接表remote_tabletestA查看mysql>usedbtestB;
Databasechanged
mysql>select*fromremote_tabletestA;
+----+
|id|
+----+
|1|
|2|
|3|
+----+
3rowsinset(0.01sec)
* ServerB服务器中查看链接表remote_tablestestA相关文件
.frm 表定义文件[ Federated链接库本地不产生数据文件][root@MySQL~]#ll/data/mysql_data2/dbtestB/
total16
-rw-r-----1mysqlmysql65Jun2510:40db.opt
-rw-r-----1mysqlmysql8556Jun2510:42remote_tabletestA.frm
6. 总结
以需求驱动技术，技术本身没有优略之分，只有业务之分。
如果觉得《mysql federated 缺点_MySQL存储引擎--------Federated最佳实战》对你有帮助，请点赞、收藏，并留下你的观点哦！
