(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