MySQL 5.0 Reference Manual  /  ...  /  How to Use FEDERATED Tables

14.7.2 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 a FEDERATED 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 a FEDERATED table. Suppose that the remote table is in the federated database and is defined like this:

CREATE TABLE test_table (
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)

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 (
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)

(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 the ENGINE 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.


You can improve the performance of a FEDERATED table by adding indexes to the table on the host. The optimization will occur because the query sent to the remote server will include the contents of the WHERE clause and will be sent to the remote server and subsequently 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 federated database.

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 as remote_host on port 9306, so there must be a MySQL server running on the remote host and listening to port 9306.

The general format of the connection string in the CONNECTION option is as follows:


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

Sample connection strings:


The use of CONNECTION for specifying the connection string is nonoptimal and is likely to change in future. Keep this in mind for applications that use FEDERATED tables. Such applications are likely to need modification if the format for specifying connection information changes.

Because any password given in the connection string is stored 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 in the INFORMATION_SCHEMA database.

User Comments
  Posted by Chris Bloom on February 2, 2007
I was having trouble connecting to a MySQL database using a connection URL like scheme://user_name:password@host_name/db_name/tbl_name until I realized that it was failing because my password contained an '@' character. I assume that '@', ':', '?', '&', and '/' are considered "special" characters and cannot be used within a connection URL scheme. To the best of my knowledge there is no way to escape these characters.

Also, note that this occurs anytime you use a URL connection string, from ANY connector program and to ANY MySQL table type. In my case I was using the ADOdb library for PHP to connect to an MyISAM table. I decided to post the note here since it was the only place I could find reference to a connection URL.

  Posted by john danilson on July 27, 2007
be sure to check that the target table exists, that the user you use exists and has the correct password. Any error in the remote connection returns the <useless> error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax...

In my case I mistyped the password.
  Posted by Charles Hamilton on January 6, 2008
If I am wrong here...let me apologize in advance. Otherwise...

I would say that any Introduction to a new or unfamiliar product should include basic requirements. For example, that BOTH servers must be running MySQL.

This may seem trivial after figuring it out or for experienced web masters, but for others it takes a lot of time to figure this out.

I eventually figured out that I couldn't access tables via MySQL on a remote server unless the "local" server (for example the one with the web pages on it) was also running MySQL (which it isn't).

Hope this helps someone else.
  Posted by Cody Baker on December 6, 2009
Dropping the federated table only drops the linked table and leaves the remote copy intact.

This is the behavior I expected, but wanted to verify before using it against a production database. Hopefully this saves someone that step.
  Posted by David Johnston on April 21, 2011
In the simple test I set up to map a Federated table on one machine to an InnoDB table on another, I neglected to establish a primary key in the schema for each. The error message was "1031 - Table storage engine for 'testtable' doesn't have this option"; not terribly obvious. Adding the primary keys eliminated the error.
  Posted by First Last on January 10, 2013
If your password has special characters such as @, :, %, ? etc try url encoding them. For example, @ should be represented as %40.
Sign Up Login You must be logged in to post a comment.