The FEDERATED storage engine is available
beginning with MySQL 5.0.3. It is a storage engine that accesses
data in tables of remote databases rather than in local tables.
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.
Additional resources
-
A forum dedicated to the
FEDERATEDstorage engine is available at http://forums.mysql.com/list.php?105.
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:
-
SQL calls issued locally
-
MySQL handler API (data in handler format)
-
MySQL client API (data converted to SQL calls)
-
Remote database -> MySQL client API
-
Convert result sets (if any) to handler format
-
Handler API -> Result rows or rows-affected count to local
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.
The general form of the connection string in the
CONNECTION option is as follows:
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
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.
The following items indicate features that the
FEDERATED storage engine does and does not
support:
-
In the first version, the remote server must be a MySQL server. Support by
FEDERATEDfor other database engines may be added in the future. -
The remote table that a
FEDERATEDtable points to must exist before you try to access the table through theFEDERATEDtable. -
It is possible for one
FEDERATEDtable to point to another, but you must be careful not to create a loop. -
There is no support for transactions.
-
There is no way for the
FEDERATEDengine to know if the remote table has changed. The reason for this is that this table must work like a data file that would never be written to by anything other than the database. The integrity of the data in the local table could be breached if there was any change to the remote database. -
The
FEDERATEDstorage engine supportsSELECT,INSERT,UPDATE,DELETE, and indexes. It does not supportALTER TABLE, or any Data Definition Language statements other thanDROP TABLE. The current implementation does not use Prepared statements. -
Any
DROP TABLEstatement issued against a FEDERATED table will only drop the local table, not the remote table. -
The implementation uses
SELECT,INSERT,UPDATE, andDELETE, but notHANDLER. -
FEDERATEDtables do not work with the query cache.
Some of these limitations may be lifted in future versions of
the FEDERATED handler.