共計(jì) 4164 個(gè)字符，預(yù)計(jì)需要花費(fèi) 11 分鐘才能閱讀完成。
這篇文章給大家介紹 FEDERATED 存儲(chǔ)引擎實(shí)現(xiàn) oracle dblink 功能及弊端分別是什么，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。
mysql FEDERATED 存儲(chǔ)引擎可以實(shí)現(xiàn) oracle dblink 類似的遠(yuǎn)程訪問(wèn)功能，F(xiàn)EDERATED 存儲(chǔ)引擎需要針對(duì)每個(gè)需要遠(yuǎn)程訪問(wèn)的表進(jìn)行逐一創(chuàng)建，而不能像 oracle dblink 有全局功能。
mysql FEDERATED 存儲(chǔ)引擎默認(rèn)是不開(kāi)啟的
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)
開(kāi)啟方法：my.cnf 的 [mysqld] 下增加 federated（小寫(xiě)），重啟即可
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)
測(cè)試一：遠(yuǎn)程訪問(wèn)
源 192.168.129.150 表 test.fader
mysql select * from fader;
+—-+——+
| id | name |
+—-+——+
| 1 | leo |
| 2 | mike |
| 3 | lucy |
| 4 | tom |
+—-+——+
目標(biāo) 172.30.249.154 上創(chuàng)建 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)
可遠(yuǎn)程訪問(wèn)
測(cè)試二：物理文件
源 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
目標(biāo) 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 引擎表沒(méi)有 idb 的數(shù)據(jù)文件，只有結(jié)構(gòu)文件
測(cè)試三：修改表結(jié)構(gòu)
源 192.168.129.150 表 test.fader 增加字段并增加數(shù)據(jù)
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)
目標(biāo) 172.30.249.154 表 test.fader_link 查數(shù)據(jù)
mysql select * from test.fader_link;
+—-+——+
| id | name |
+—-+——+
| 1 | leo |
| 2 | mike |
| 3 | lucy |
| 4 | tom |
+—-+——+
4 rows in set (0.00 sec)
查不到新增的列數(shù)據(jù)，嘗試增加字段
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 報(bào)錯(cuò)無(wú)法對(duì) FEDERATED 引擎表進(jìn)行這個(gè)操作
源 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)
目標(biāo) 172.30.249.154 表 test.fader_link 查數(shù)據(jù)
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. 本地的表結(jié)構(gòu)必須與遠(yuǎn)程的完全一樣。
2. 遠(yuǎn)程數(shù)據(jù)庫(kù)目前僅限 MySQL
3. 不支持事務(wù)
4. 不支持表結(jié)構(gòu)修改
關(guān)于 FEDERATED 存儲(chǔ)引擎實(shí)現(xiàn) oracle dblink 功能及弊端分別是什么就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。
