MySQL Reference Manual :: 14.6.3 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 also possible for a FEDERATED table to use another table that is managed by the same server, though there is little point in doing so.)

First, you must have a table on the remote server that you want to access with the FEDERATED 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),
    KEY name (name),
    KEY other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

The ENGINE table option could name any storage engine; the table need not be a MyISAM table.

Next, create a FEDERATED table for accessing the remote table. The server where you create the FEDERATED table is the ``client server.'' On this server, create the table as follows:

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),
    KEY name (name),
    KEY other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
COMMENT='mysql://root@remote_host:9306/federated/test_table';

The structure of this table must be exactly the same as the remote table, except that the ENGINE table option should be FEDERATED and the COMMENT table option is a connection string that indicates to the FEDERATED engine how to connect to the remote server.

The FEDERATED engine creates only the test_table.frm file in the federated database.

The remote host information indicates the remote server to which your ``client'' server connects, and the database and table information indicates which remote table to use as the ``data file.'' In the example, the remote server is indicated to be running as remote_host on port 9306, so you want to start that server so that it is indeed listening to port 9306.

The general form of the connection string in the COMMENT option is as follows:

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

Only mysql is supported as the scheme at this point, and the password and port number are optional.

Here are some example connection strings:

COMMENT='mysql://username:password@hostname:port/database/tablename'
COMMENT='mysql://username@hostname/database/tablename'
COMMENT='mysql://username:password@hostname/database/tablename'

The use of COMMENT for specifying the connection string is non-optimal and will likely change in MySQL 5.1. Keep this in mind when you use FEDERATED tables, because it means you'll need to make some modifications when that happens.

Also, because a password is stored in the connection string as plain text, it can be seen by any user who can use SHOW CREATE TABLE or SHOW TABLE STATUS for the FEDERATED table, or query the TABLES table of INFORMATION_SCHEMA.