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

MySQL Cross-Server Select Query via SSH Tunnel and Federated Tables

To perform a cross-server select query using MySQL, a common approach is to create federated tables on one of the servers. By creating federated tables based on the remote tables, you can query the data as if it were all located locally.

Steps:

1. Establish an SSH tunnel to connect the two servers.
2. Define a table on the remote server.
3. Create a federated table on the local server that points to the remote table. Use the following syntax:

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/test_table';

4. Once the federated table is created, you can query it as if it were a local table. The federated table will transparently handle the communication with the remote server.

Example:

Consider the following setup:

Server IP	Database
1.2.3.4	Test
a.b.c.d	Test

You can create a federated table on server 1.2.3.4 to access a table on server a.b.c.d using the following query:

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@a.b.c.d:9306/Test/test_table';

Once the federated table is created, you can query it as follows:

SELECT * FROM federated_table;

This query will retrieve rows from the test_table table on server a.b.c.d.

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