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

Cross-Server Data Synchronization Using MySQL Federated Tables

To perform a cross-server select query in MySQL, one viable approach is to utilize federated tables. With federated tables, you can create a local representation of a remote table, allowing you to access data on a different server transparently.

Setup:

Consider the following setup:

• Server IP: 1.2.3.4, Database: Test
• Server IP: a.b.c.d, Database: Test

Procedure:

To establish a cross-server query, follow these steps:

1. Create a federated table on the local server:

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

   Copy after login

   Replace federated/test_table with the remote table's path on the remote server.

2. Write a query that uses the federated table:

   SELECT *
   FROM federated_table;

   Copy after login

By leveraging federated tables, you can seamlessly execute cross-server queries, as if the remote data were local to your current database. Remember to configure the necessary network permissions and establish a secure connection for data transfer between servers.

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