Shulou(Shulou.com)05/31 Report--

This article introduces FEDERATED storage engine oracle dblink function and disadvantages are what, the content is very detailed, interested friends can refer to, hope to help everyone

mysql FEDERATED storage engine can implement remote access functions similar to oracle dlink. FEDERATED storage engine needs to create tables one by one for each table that needs remote access, but cannot have global functions like oracle dlink.

mysql FEDERATED storage engine is disabled by default

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)

Open method: my.cnf [mysqld] under the addition of federated (lowercase), restart can be

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)

Test 1: Remote Access

Source 192.168.129.150 Table test.fader

mysql> select * from fader;

+----+------+

| id | name |

+----+------+

| 1 | leo |

| 2 | mike |

| 3 | lucy |

| 4 | tom |

+----+------+

Create FEDERATED Engine Table on Target 172.30.249.154

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)

remotely accessible

Test 2: Physical Documents

Source 192.168.129.150 Table test.fader

[root@trcloud-gtt-db-master test]# ls -l fader*

-rw-rw--- 1 mysql mysql 8586 September 22 10:22 fader.frm

-rw-rw--- 1 mysql mysql 98304 September 22 10:22 fader.ibd

Target 172.30.249.154 Table 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

You can see that the FEDERATED engine table has no idb data files, only structure files

Test 3: Modify the table structure

Source 192.168.129.150 table test.fader adds fields and adds data

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)

Target 172.30.249.154 Table test.fader_link Check data

mysql> select * from test.fader_link;

+----+------+

| id | name |

+----+------+

| 1 | leo |

| 2 | mike |

| 3 | lucy |

| 4 | tom |

+----+------+

4 rows in set (0.00 sec)

Cannot find new column data. Try adding fields.

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

Source 192.168.129.150 table test.fader delete field

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)

Target 172.30.249.154 Table test.fader_link Check data

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 column not found

The local table structure must be exactly the same as the remote one.

2. Remote databases are currently MySQL only

3. not support transactions

4. Table structure modification is not supported

About FEDERATED storage engine oracle dblink function and disadvantages are what to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.