FEDERATED存储引擎实现oracle dblink功能及弊端分别是什么

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

这篇文章给大家介绍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列