MySQL Federated Tables

The MySQL Federated storage engine for the MySQL relational database management system is a storage engine which allows a user to create a table that is a local representation of a foreign (remote) table. It utilizes the MySQL client library API as a data transport, treating the remote data source the same way other storage engines treat local data sources whether they be MYD files (MyISAM), memory (Cluster, Heap), or tablespace (InnoDB). Each Federated table that is defined there is one .frm (data definition file containing information such as the URL of the data source). The actual data can exist on a local or remote MySQL instance.

Before creating federated tables you need to install federated engine plugin & enable it from mysql. You can do this by following this post.

Creating FEDERATED Tables

Federated tables can be created in two ways

• Creating a FEDERATED Table Using Connection
• Creating a FEDERATED Table Using CREATE SERVER

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=FEDERATED 
DEFAULT CHARSET=utf8mb4
CONNECTION='mysql://fed_user@remote_host:3306/federated/test_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. Only mysql is supported as the scheme value at this point.
• user_name: The user name for the connection. This user must have been created on the remote server, and must have suitable privileges to perform the required actions (SELECT, INSERT, UPDATE, and so forth) on the remote table.
• password: (Optional) The corresponding password for user_name.
• host_name: The host name or IP address of the remote server.
• port_num: (Optional) The port number for the remote server. The default is 3306.
• db_name: The name of the database holding the remote table.
• tbl_name: The name of the remote table. The name of the local and the remote table do not have to match.

Sample connection strings:

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

Creating a FEDERATED Table Using CREATE SERVER

If you are creating a number of FEDERATED tables on the same server, or if you want to simplify the process of creating FEDERATED tables, you can use the CREATE SERVER statement to define the server connection parameters, just as you would with the CONNECTION string.

The format of the CREATE SERVER statement is:

CREATE SERVER
server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)

For example, to create a server connection identical to the CONNECTION string:

CONNECTION='mysql://fed_user@remote_host:3306/federated/test_table';

You would use the following statement:

CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', HOST 'remote_host', PORT 3306, DATABASE 'federated');

To create a FEDERATED table that uses this connection, you still use the CONNECTION keyword, but specify the name you used in the CREATE SERVER statement.

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=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='fedlink/test_table';

The connection name in this example contains the name of the connection (fedlink) and the name of the table (test_table) to link to, separated by a slash.