# 16.8.2.1 Creating a FEDERATED Table Using CONNECTION

To use the first method, you must specify theCONNECTIONstring after the engine type in aCREATE TABLEstatement. 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=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

Note

CONNECTIONreplaces theCOMMENTused in some previous versions of MySQL.

TheCONNECTIONstring contains the information required to connect to the remote server containing the table in which the data physically resides. The connection string specifies the server name, login credentials, port number and database/table information. In the example, the remote table is on the serverremote_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:

  • scheme: A recognized connection protocol. Onlymysqlis supported as the*scheme*此时的价值。

  • 用户名:连接的用户名。此用户必须已在远程服务器上创建,并且必须具有适当的权限才能执行所需的操作 (选择,插入,更新等)在远程表上。

  • 密码:(可选)对应的密码*用户名*.

  • 主机名:远程服务器的主机名或IP地址。

  • 端口号:(可选)远程服务器的端口号。默认值为 3306。

  • 数据库名称: 保存远程表的数据库的名称。

  • tbl_name:远程表的名称。本地和远程表的名称不必匹配。

    示例连接字符串:

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