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 (
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';
(Note:
CONNECTION
replaces the
COMMENT
used in some previous versions of
MySQL.)
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.