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功能及弊端

相關文章