29.6. The FEDERATED Engine

29.6. The FEDERATED Engine

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. The clients need not connect directly to the other servers to access the tables.

One benefit provided by this capability is that you can use a single query to access tables that are managed by different servers. It's not necessary to connect to each server and retrieve data separately for each one. For example, you can perform a join between tables from different servers. FEDERATED is new, so much remains to be done in terms of optimizing such queries, but the fact that they now can be issued is significant.

The FEDERATED storage engine manages tables that have the following characteristics:

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

• The FEDERATED storage 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.

Suppose that there is an instance of the world database located on the remote host world.example.com and that its City table has this definition:

 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 = MyISAM; 

If the world database on the remote host can be accessed by connecting to the MySQL server there with a username and password of wuser and wpass, a FEDERATED table can be created on the local host that allows the remote City table to be accessed as though it were local. 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. The connection string indicates to the local server where the remote table is located and how to connect to the remote server. Connection string format is as follows, where optional parts are shown in square brackets:

 mysql://user_name[:password]@host_name[:port]/db_name/table_name 

The username, password, hostname, and port number specify what connection parameters to use for connecting to the remote server. The database and table names indicate which table to access on that server.

With the ENGINE and COMMENT table options, the resulting definition for a FEDERATED table named FedCity looks like this:

 CREATE TABLE FedCity (     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://wuser:[e-mail]/world/City'; 

To access the FedCity table, just refer to it as you would any other table. For example:

 mysql> SELECT ID, Name, Population FROM FedCity     -> WHERE CountryCode = 'EGY'; +-----+----------------------+------------+ | ID  | Name                 | Population | +-----+----------------------+------------+ | 608 | Cairo                |    6789479 | | 609 | Alexandria           |    3328196 | | 610 | Giza                 |    2221868 | | 611 | Shubra al-Khayma     |     870716 | | 612 | Port Said            |     469533 | | 613 | Suez                 |     417610 | | 614 | al-Mahallat al-Kubra |     395402 | | 615 | Tanta                |     371010 | | 616 | al-Mansura           |     369621 | ... 

"Remote" in the preceding discussion actually is not quite accurate: FEDERATED tables can be defined for accessing tables from other servers running on the same host, or even other tables from the same server.