8.7. The FEDERATED Storage Engine
The FEDERATED storage engine is included in MySQL-Max binary distributions. To enable this storage engine if you build MySQL from source, invoke configure with the --with-federated-storage-engine option.
To examine the source for the FEDERATED engine, look in the sql directory of a source distribution for MySQL 5.0.3 or newer.
8.7.1. Description of the FEDERATED Storage Engine
When you create a FEDERATED table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. No other files are created, because the actual data is in a remote table. This differs from the way that storage engines for local tables work.
For local database tables, data files are local. For example, if you create a MyISAM table named users, the MyISAM handler creates a data file named users.MYD. A handler for local tables reads, inserts, deletes, and updates data in local data files, and rows are stored in a format particular to the handler. To read rows, the handler must parse data into columns. To write rows, column values must be converted to the row format used by the handler and written to the local data file.
With the MySQL FEDERATED storage engine, there are no local data files for a table (for example, there is no .MYD file). Instead, a remote database stores the data that normally would be in the table. The local server connects to a remote server, and uses the MySQL client API to read, delete, update, and insert data in the remote table. Data retrieval is initiated via a SELECT * FROM tbl_name SQL statement. To read the result, rows are fetched one at a time by using the mysql_fetch_row() C API function, and then converting the columns in the SELECT result set to the format that the FEDERATED handler expects.
The flow of information is as follows:
8.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 ( 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://root@remote_host:9306/federated/test_table';
(Before MySQL 5.0.13, use COMMENT rather than CONNECTION.)
The structure of this table must be exactly the same as 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.
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.
Only mysql is supported as the scheme value at this point; the password and port number are optional.
Here are some example connection strings:
CONNECTION='mysql://username:password@hostname:port/database/tablename' CONNECTION='mysql://username@hostname/database/tablename' CONNECTION='mysql://username:password@hostname/database/tablename'
The use of CONNECTION for specifying the connection string is non-optimal 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.
8.7.3. Limitations of the FEDERATED Storage Engine
The following items indicate features that the FEDERATED storage engine does and does not support:
Some of these limitations may be lifted in future versions of the FEDERATED handler.