MySQL - Storage Engine - FEDERATED

The FEDERATED storage engine is new in MySQL 5. It allows a MySQL server to use tables from other MySQL servers and to make them available to its clients as though the tables were its own.

One benefit provided by this capability is that you can use a single query to access tables that are managed by different servers. You can perform a join between tables from different servers.

Each FEDERATED table is represented on disk only by an .frm format file in the database directory.

The FEDERATED engine does not support transactions.

The FEDERATED storage engine supports SELECT, DELETE, UPDATE, and INSERT statements.

MySQL does not use any locking for FEDERATED tables.

To create a local FEDERATED table, use a definition similar to that of the remote table, but make two changes. First, use an ENGINE = FEDERATED table option. Second, include a COMMENT table option that specifies a connection string. Connection string format is as follows:

mysql://user_name[:password]@host_name[:port]/db_name/table_name
CREATE TABLE City
(
    ID INT NOT NULL AUTO_INCREMENT,
    Name CHAR(35) NOT NULL,
    CountryCode CHAR(3) NOT NULL,
    District CHAR(20) NOT NULL,
    Population INT NOT NULL,
    PRIMARY KEY (ID)
)
ENGINE = FEDERATED
COMMENT = 'mysql://username:password@world.example.com/world/City'

FEDERATED tables can be defined for accessing tables from other servers running on the same host, or even other tables from the same server.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License