(mysql.info) federated-use

Info Catalog

(mysql.info) federated-description

(mysql.info) federated-storage-engine

(mysql.info) federated-limitations

 
 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';
 
 (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.
 

Info Catalog

(mysql.info) federated-description

(mysql.info) federated-storage-engine

(mysql.info) federated-limitations