MySQL Federated Tables: The Missing Manual

by Giuseppe Maxia
08/10/2006

One of the most exciting features introduced in MySQL 5 is the federated engine. The ability to access data from a remote server without the constraints of replication tickles every programmer's fancy.

Unfortunately, as of today, the documentation is not quite as detailed as I would like. I have the feeling that the federated engine has been somehow neglected. This article comes from my personal experience with this engine.

Color Codes

Actions with this background execute on the data server--the server holding the real data.
Actions with this background execute on the federating server--the server with just a link to the real table.
This background introduces the fundamental rules of the FEMM (Federated Engine Missing Manual).
This background is for comments from MySQL developers about the issues raised here.

Basic Federated Usage

This information is also available in the MySQL manual, but for the sake of completeness, I want to say a few words on the basics.

Federated tables are tables with storage in a remote server. When defining a table with this engine, you refer to another table in a different server, using the same structure. The only thing that resides in your local server is the definition of the table, which must be identical to the remote one, except for the engine specification.

On the remote server, you have:

CREATE TABLE `City` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` char(35) NOT NULL default '',
  `CountryCode` char(3) NOT NULL default '',
  `District` char(20) NOT NULL default '',
  `Population` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM;

To use that table in your local server, enter the description:

CREATE TABLE `City` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` char(35) NOT NULL default '',
  `CountryCode` char(3) NOT NULL default '',
  `District` char(20) NOT NULL default '',
  `Population` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE = FEDERATED
connection='mysql://user:pass@remote.com:3306/world/City';

Here, user and pass are valid credentials to access the table City in the database world on server remote.com.

With that done, you can query your federated table as if it were in your local server. Issue a query and get a record set.

select * from City where ID = 1;

+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+

There are a few limitations concerning federated engine usage, namely:

The remote table must exist when you create your local one.
You can't issue ALTER TABLE commands on a federated table.
The federated table is not aware of any structural changes that may occur in the remote one. You may get an error at runtime.
Transactions are not supported.

This is basically what the official docs say, no more and no less. Beyond that, it's all unexplored land.

Testing Federated Features

Because you won't have the reassuring backing from the manual in this matter, you should test these features for yourself. Because you need at least two instances of a MySQL server to use a federated table, here is a quick recipe to get the job done. Download the MySQL Sandbox and, from the command line, execute:

$ ./express_install.pl /opt/mysql/5.0.22/ -P 5001 -d server1 \
    -c log=general.log -c log-slow-queries=slow_queries.log \
    -c long_query_time=1 -c log-queries-not-using-indexes
$ ./express_install.pl /opt/mysql/5.0.22/ -P 5002 -d server2 \
    -c log=general.log -c log-slow-queries=slow_queries.log \
    -c long_query_time=1 -c log-queries-not-using-indexes

If you want to know more about the Sandbox, you can refer to the MySQL Sandbox tutorial. If you don't work in a Unix environment, you have to install it manually, following MySQL Windows server installation official guidelines.

The previous commands will create two directories under your $HOME, server1 and server2, each of which contains a data directory and a few bash scripts to start, stop, and use the instance. Both servers will have the general log and slow query log enabled, so you can examine what happens when the federated engine exchanges data between them.

Now start them with their appropriate commands:

$ ~/server1/start.sh
$ ~/server2/start.sh

To access the first server, use the command:

$ mysql -h 127.0.0.1 -u datacharmer -pdatacharmer -P 5001

Or just:

$ ~/server1/use.sh

For the second server, change the port to 5002:

$ mysql -h 127.0.0.1 -u datacharmer -pdatacharmer -P 5002

To make things easier to detect, create a user on server1, which server2 will use to access the federated tables.

$ echo \
    "grant select,execute on test.* to server2usr identified by 'datacharmer'" \
    | ~/server1/use.sh -u root

Now you're ready to get your hands dirty.

Pages: 1, 2, 3, 4, 5