MySQL Federated Tables: The Missing Manual

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.

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:

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.