How Can I Use MySQL Federated Tables for Cross-Server Data Transfer?

Cross-Server Data Transfer using MySQL Federated Tables

In MySQL, it is possible to perform cross-server data transfer using federated tables. Federated tables provide a convenient way to access tables on remote servers as if they were local tables.

Steps for Establishing Federated Tables:

1. Create a remote database and table on Server 1 (e.g., IP: 1.2.3.4).
2. Create a federated table on Server 2 (e.g., IP: a.b.c.d) that references the remote table.

Example Syntax for Creating a Federated Table:

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=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/remote_table';

Executing Cross-Server Select Queries:

Once the federated table is created, you can execute cross-server select queries as follows:

SELECT * FROM federated_table;

This query will fetch data from the underlying remote table on Server 1 and return it to the client on Server 2.

Additional Considerations:

• The user executing the federated query must have appropriate permissions on both the local federated table and the remote table.
• Federated tables do not support all MySQL features, such as transactions and subqueries.
• The performance of federated queries may vary depending on network latency and the hardware specifications of the servers involved.

The above is the detailed content of How Can I Use MySQL Federated Tables for Cross-Server Data Transfer?. For more information, please follow other related articles on the PHP Chinese website!