How Can I Perform Cross-Server MySQL SELECT Queries Using Federated Tables?

MySQL Cross-Server Select Queries using Federated Tables

MySQL provides a mechanism for querying data across different database servers through the use of federated tables. This can be particularly useful when the servers are located apart and direct connections are not feasible.

In the scenario you described, with two MySQL servers at 1.2.3.4 and a.b.c.d, each hosting a database named Test, you can use a federated table to select rows from one server and insert them into a table on the other.

To create a federated table, you first need to establish an SSH tunnel between the two servers. This will allow you to connect to one server (the remote server) through the other (the local server).

Once the SSH tunnel is established, you can create the federated table on the local server as follows:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:port/database_name/table_name';

where:

• fed_user is a user that has access to the remote table
• remote_host is the IP address of the remote server
• port is the port number of the MySQL instance on the remote server
• database_name is the name of the remote database containing the table
• table_name is the name of the remote table

Once the federated table is created, you can execute SELECT queries on it to retrieve data from the remote table. The results of the query can then be inserted into a table on the local server using the INSERT statement.

Here's an example query that selects rows from the federated table and inserts them into a local table named local_table:

INSERT INTO local_table (id, name, other)
SELECT id, name, other FROM federated_table;

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