14.9.2.1. Creating a FEDERATED Table UsingCONNECTION

To use the first method, you must specify the CONNECTION string after the engine type in a CREATE TABLE statement. For example:

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=FEDERATEDDEFAULT CHARSET=latin1CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

The CONNECTION string contains the information required to connect to the remote server containing the table that will be used to physically store the data. The connection string specifies the server name, login credentials, port number and database/table information. In the example, the remote table is on the server remote_host, using port 9306. The name and port number should match the host name (or IP address) and port number of the remote MySQL server instance you want to use as your remote table.

The format of the connection string is as follows:

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

Where:

Sample connection strings:

CONNECTION='mysql://username:password@hostname:port/database/tablename'CONNECTION='mysql://username@hostname/database/tablename'CONNECTION='mysql://username:password@hostname/database/tablename'