How Can I Join Tables Across Different MySQL Servers?

Joining Tables from Separate Servers in MySQL

Attempting to join tables from two distinct servers, server1 and server2, may encounter errors. Here's the approach to overcome this challenge using MySQL's FEDERATED ENGINE:

Using a Federated Table

To bridge the gap between servers, create a federated table based on the remote table. The structure of both tables must remain identical.

Example Code:

Create a federated table called federated_table based on a remote table named test_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/test_table';

By establishing the federated connection, you can join tables across servers as if they were local:

SELECT a.field1, b.field2
FROM federated_table AS a
INNER JOIN [server2, 3312].[db2].table2 AS b
ON a.field1 = b.field2;

Note: The fed_user account must have appropriate permissions on both servers to establish the federated connection.

The above is the detailed content of How Can I Join Tables Across Different MySQL Servers?. For more information, please follow other related articles on the PHP Chinese website!