What is FEDERATED Engine / how use to it.

what is FEDERATED engine.

The FEDERATED storage engine is available beginning with MySQL 5.0.3. It is a storage engine that accesses data in tables of remote databases rather than in local tables.

How to Use FEDERATED Tables

The procedure for using FEDERATED tables is very simple. Normally, you have two servers running, either both on the same host or on different hosts. (It is possible for aFEDERATED table to use another table that is managed by the same server, although there is little point in doing so.)

First, you must have a table on the remote server that you want to access by using aFEDERATED table. Suppose that the remote table is in the federated database and is defined like this:

CREATE TABLE test_table ( id INT(20) NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL DEFAULT '', other INT(20) NOT NULL DEFAULT '0', PRIMARY KEY (id), INDEX name (name), INDEX other_key (other) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The example uses a MyISAM table, but the table could use any storage engine.

Next, create a FEDERATED table on the local server for accessing the remote table:

CREATE TABLE federated_table ( id INT(20) NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL DEFAULT '', other INT(20) NOT NULL DEFAULT '0', PRIMARY KEY (id), INDEX name (name), INDEX other_key (other) ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

(Before MySQL 5.0.13, use COMMENT rather than CONNECTION.)

The basic structure of this table should match that of the remote table, except that theENGINE table option should be FEDERATED and the CONNECTION table option is a connection string that indicates to the FEDERATED engine how to connect to the remote server.

Note:

You can improve the performance of a FEDERATED table by adding indexes to the table on the host, even though the tables will not actually be created locally. The optimization will occur because the query sent to the remote server will include the contents of the WHERE clause will be sent to the remote server and executed locally. This reduces the network traffic that would otherwise request the entire table from the server for local processing.

The FEDERATED engine creates only the test_table.frm file in the federateddatabase.

The remote host information indicates the remote server to which your local server connects, and the database and table information indicates which remote table to use as the data source. In this example, the remote server is indicated to be running asremote_host on port 9306, so there must be a MySQL server running on the remote host and listening to port 9306.