(mysql.info.gz) FEDERATED use
Info Catalog
(mysql.info.gz) FEDERATED description
(mysql.info.gz) FEDERATED storage engine
(mysql.info.gz) FEDERATED limitations
14.6.3 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 also possible for a `FEDERATED' table to use another
table that is managed by the same server, though there is little point
in doing so.)
First, you must have a table on the remote server that you want to
access with the `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),
KEY name (name),
KEY other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1 ;
The `ENGINE' table option could name any storage engine; the table need
not be a `MyISAM' table.
Next, create a `FEDERATED' table for accessing the remote table. The
server where you will create the `FEDERATED' table is the "client
server." On this server, create the table as follows:
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),
KEY name (name),
KEY other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
COMMENT='mysql://root@remote_host:9306/federated/test_table';
The structure of this table must be exactly the same as the remote
table, except that the `ENGINE' table option should be `FEDERATED' and
the `COMMENT' table option is a connection string that indicates to the
`FEDERATED' engine how to connect to the remote server.
The `FEDERATED' engine will create only the `test_table.frm' file in
the `federated' database.
The remote host information indicates the remote server to which your
"client" server will connect, and the database and table information
indicates which remote table to use as the "data file." In the example,
the remote server is indicated to be running as `remote_host' on port
9306, so you want to start that server so that it is indeed listening to
port 9306.
The general form of the connection string in the `COMMENT' option is as
follows:
SCHEME://USER_NAME[:PASSWORD]@HOST_NAME[:PORT_NUM]:/DB_NAME/TBL_NAME
Only `mysql' is supported as the SCHEME at this point, and the password
and port number are optional.
Here are some example connection strings:
COMMENT='mysql://username:password@hostname:port/database/tablename'
COMMENT='mysql://username@hostname/database/tablename'
COMMENT='mysql://username:password@hostname/database/tablename'
The use of `COMMENT' for specifying the connection string is non-optimal
and likely will change in MySQL 5.1. Keep this in mind when you use
`FEDERATED' tables, because it means you'll need to make some
modifications when that happens.
Also, because a password is stored in the connection string as plain
text, it can be seen by any user who can use `SHOW CREATE TABLE' or
`SHOW TABLE STATUS' for the `FEDERATED' table.
Info Catalog
(mysql.info.gz) FEDERATED description
(mysql.info.gz) FEDERATED storage engine
(mysql.info.gz) FEDERATED limitations
automatically generated byinfo2html