How Can I Join MySQL Tables Across Different Servers?

Query Two Tables from Different Servers in MySQL with Federated Engine

Question:

Is it possible to join tables from two different MySQL servers in a single query?

Solution:

Yes, it is possible using the MySQL Federated Engine.

Implementation:

To join tables from two different servers, you must first create federated tables that point to the remote tables. The federated tables must have the same structure as the original tables.

Here's an example of creating a federated table named federated_table1 pointing to a remote table named table1 on server1:

CREATE TABLE federated_table1 (
    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@server1:3306/db1/table1';

Once you have created the federated tables, you can join them like regular tables:

SELECT a.field1, b.field2
FROM federated_table1 a
INNER JOIN federated_table2 b
ON a.field1 = b.field2;

Note:

• The user specified in the CONNECTION string must have the necessary privileges to access the remote tables.
• The performance of federated queries may vary depending on the network latency and load on the remote server.

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