FEDERATED存储引擎实现oracle dblink功能及弊端分别是什么
发布时间：2021-12-19 20:33:09 所属栏目：MySql教程 来源：互联网
导读：这篇文章给大家介绍FEDERATED存储引擎实现oracle dblink功能及弊端分别是什么，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。 mysql FEDERATED存储引擎可以实现oracle dblink类似的远程访问功能，FEDERATED存储引擎需要针对每个需要
|
这篇文章给大家介绍FEDERATED存储引擎实现oracle dblink功能及弊端分别是什么，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。 mysql FEDERATED存储引擎可以实现oracle dblink类似的远程访问功能，FEDERATED存储引擎需要针对每个需要远程访问的表进行逐一创建，而不能像oracle dblink有全局功能。 mysql FEDERATED存储引擎默认是不开启的 mysql> show engines; | Engine | Support | Comment | Transactions | XA | Savepoints | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | CSV | YES | CSV storage engine | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | 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 | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | 9 rows in set (0.00 sec) 开启方法：my.cnf的[mysqld]下增加federated（小写），重启即可 mysql> show engines; | Engine | Support | Comment | Transactions | XA | Savepoints | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | CSV | YES | CSV storage engine | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | 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 | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | 9 rows in set (0.00 sec) 测试一：远程访问 源192.168.129.150表test.fader mysql> select * from fader; +----+------+ | id | name | +----+------+ | 1 | leo | | 2 | mike | | 3 | lucy | | 4 | tom | +----+------+ 目标172.30.249.154上创建FEDERATED引擎表 CREATE TABLE fader_link ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE =FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root:Welcome1>@192.168.129.150:3306/test/fader' ; mysql> select * from test.fader_link; +----+------+ | id | name | +----+------+ | 1 | leo | | 2 | mike | | 3 | lucy | | 4 | tom | +----+------+ 4 rows in set (0.00 sec) 可远程访问 测试二：物理文件 源192.168.129.150表test.fader [root@trcloud-gtt-db-master test]# ls -l fader* -rw-rw---- 1 mysql mysql 8586 9月 22 10:22 fader.frm -rw-rw---- 1 mysql mysql 98304 9月 22 10:22 fader.ibd 目标172.30.249.154表test.fader_link [root@trcloud-gtt-test-db test]# ls -l fader_link* -rw-rw---- 1 mysql mysql 8586 Sep 22 10:30 fader_link.frm 可以看出FEDERATED引擎表没有idb的数据文件，只有结构文件 测试三：修改表结构 源192.168.129.150表test.fader增加字段并增加数据 ALTER TABLE `fader` ADD COLUMN `city` varchar(255) NULL AFTER `name`; mysql> select * from test.fader; +----+------+------+ | id | name | city | +----+------+------+ | 1 | leo | bj | | 2 | mike | sh | | 3 | lucy | nj | | 4 | tom | hz | +----+------+------+ 4 rows in set (0.00 sec) 目标172.30.249.154表test.fader_link查数据 mysql> select * from test.fader_link; +----+------+ | id | name | +----+------+ | 1 | leo | | 2 | mike | | 3 | lucy | | 4 | tom | +----+------+ 4 rows in set (0.00 sec) 查不到新增的列数据，尝试增加字段 mysql> ALTER TABLE test.fader_link ADD COLUMN `city` varchar(255) NULL AFTER `name`; ERROR 1031 (HY000): Table storage engine for 'fader_link' doesn't have this option 报错无法对FEDERATED引擎表进行这个操作 源192.168.129.150表test.fader删除字段 ALTER TABLE `fader` DROP COLUMN `city`; ALTER TABLE `fader` DROP COLUMN `name`; mysql> select * from test.fader; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | +----+ 4 rows in set (0.00 sec) 目标172.30.249.154表test.fader_link查数据 mysql> select * from test.fader_link; ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1054: Unknown column 'name' in 'field list'' from FEDERATED 找不到name列 1.本地的表结构必须与远程的完全一样。 2.远程数据库目前仅限MySQL 3.不支持事务 4.不支持表结构修改 关于FEDERATED存储引擎实现oracle dblink功能及弊端分别是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。 （编辑：源码网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
