:Create a FEDERATED table

Prerequisites

The cluster that you want to manage is a PolarDB for MySQL 8.0 cluster whose revision version meets the following requirements:

• 8.0.1.1.28 or later

• 8.0.2.2.4 or later

For information about how to view the version of a cluster, see the Query the engine version section of the "Engine versions" topic.

Limits

• You can connect to a database on a remote server only by using the public IP address of the server.

• You cannot remotely connect to a database that is hosted on an Elastic Compute Service (ECS) instance.

• The FEDERATED storage engine cannot access the databases in the current cluster by using the public endpoint.

Procedure

1. Create a remote server. Syntax:

   CREATE SERVER <server_name>
       FOREIGN DATA WRAPPER mysql
       OPTIONS (HOST "<host_name>", PORT 3306, USER "<user_name>", PASSWORD "<password>",
                DATABASE "<db_name>");

   The following table describes the parameters that you can configure in the statement.

   Parameter	Description
   server_name	The name of the remote server.
   host_name	The hostname or IP address of the remote server.
   user_name	The username used to authenticate the connection to the remote server.
   password	The password used to authenticate the connection to the remote server.
   db_name	The name of the database created on the remote server.

2. Create a FEDERATED table.

   You can connect to a remote table to create a FEDERATED table by using a three-part name or specifying a remote server or connection.

   • (Recommended) Create a FEDERATED table by using a three-part name. Syntax:

     CREATE FOREIGN TABLE tbl_name for <server_name>.<remote_db_name>.<tbl_name>;

     Sample statement:

     CREATE FOREIGN TABLE t1 for server.test.t1;

     Note

     Your cluster must be of PolarDB for MySQL 8.0.2 with revision version 8.0.2.2.25 or later.

   • Create a FEDERATED table by specifying a remote server. Syntax:

     CREATE TABLE <tbl_name> (create_definition,...) ENGINE=FEDERATED
       CONNECTION='<server_name>/<tbl_name>';

     Sample statement:

     CREATE TABLE `t1`(`a` VARCHAR(100),UNIQUE KEY(`a`(30))) ENGINE=FEDERATED
       CONNECTION='server_name/t1';

   • (Not recommended) Create a FEDERATED table by specifying a connection. Syntax:

     CREATE TABLE <tbl_name> (create_definition,...) ENGINE=FEDERATED
       CONNECTION='<MYSQL://<user_name>:<password>@<host_name>:3306/<db_name>/<tbl_name>';

     Sample statement:

     CREATE TABLE `t1`(`a` VARCHAR(100),UNIQUE KEY(`a`(30))) ENGINE=FEDERATED
       CONNECTION='MYSQL://username:passsword@127.0.0.1:3306/test/t1';

     Note

     Accounts and passwords are saved in plain text in this method. Therefore, we recommend that you do not use this method.