Efficiently managing data retrieval from two separate MySQL servers can be achieved through either federated tables or a middle-tier application. Federated tables allow for direct querying and joining of data across servers by creating a table on one server that references a table on another.

In this article, We will learn about the How to select data from two different servers in MySQL in detail.

How to Select Data from Two Different Servers in MySQL?

When working with data distributed across multiple MySQL servers, the primary challenge is efficiently querying and combining this data. The solution is using federated tables, which allow for direct querying across servers, or a middle-tier application that handles data integration programmatically.

1. Using Federated Tables
2. Using a Middle-Tier Application

1. Using Federated Tables

• Federated tables allow us to create a table on one MySQL server that directly references and pulls data from a table on another server.
• This setup enables querying and joining of data across servers as if they were part of the same database but it requires proper configuration of federated tables.

Setting Up Federated Tables

• Enable Federated Storage Engine: The FEDERATED storage engine should be enabled in your MySQL Server.
• Create Server Definition: Define the remote server connection in the local server.
• Federated Table Creation: A table is created in the local server that will reference the remote table.

Example

Here are some steps by which you can use the Federate Tables.

Enable the FEDERATED storage engine

INSTALL PLUGIN federated SONAME 'ha_federated.so';

Define the connection to Server B

CREATE SERVER serverB
FOREIGN DATA WRAPPER mysql
OPTIONS (HOST 'remote_host', DATABASE 'dbB', USER 'username', PASSWORD 'password');

where,

• CREATE SERVER serverB: This command creates a new server definition with the name serverB
• FOREIGN DATA WRAPPER mysql: FOREIGN DATA WRAPPER specifies the type of data wrapper to use.
• OPTIONS: It provide necessary details required for the connection.

Create a federated table

Create a federate table with your necessary columns using CREATE statement.

CREATE TABLE federated_tableB (
    id INT,
    name VARCHAR(100)
) ENGINE=FEDERATED
CONNECTION='serverB/tableB';

Querying Data

Now you can join the two databases and perform queries.

SELECT local_table.*, federated_tableB.*
FROM local_table
JOIN federated_tableB ON local_table.id = federated_tableB.id;

Explanation:

• First, turn on the FEDERATED storage engine on the local MySQL server. Second, it is necessary to define a connection to the remote server using a server definition that describes the remote host, database, and user credentials. Then, create a federated table on the local server that will reference a table on the remote server.
• For example, you create a federated table on Server A—federated_tableB—after defining the remote server connection. That will be pointing to tableB on Server B.
• Now, the setup is such that from Server A you can run queries joining the local tables with the federated table—for instance, joining local_table to federated_tableB on some common column, thus giving the impression that both server data lies in one.

2. Using a Middle-Tier Application

Another way would be through a middle-tier application that joins both of the servers, retrieves data and programmatically combines it.

This would show a better strategy since this provides more control and flexibility over exactly what data is being retrieved from these servers and merged.

Setting Up a Middle-Tier Application

As the first method here we also need to setup middle tier application:

• Connect to both Servers: This could be facilitated by utilizing any programming language like Python, PHP, or Java and forming a connection with both the MySQL servers.
• Fetch Data Separately: Fetching of data can be done by executing separate queries on each of the servers.
• Programmatic Data Combination: Programmatically combine your data in your application code.

Example:

Install the MySQL connector for Python:

pip install mysql-connector-python

Connect to both servers and fetch data:

import mysql.connector

# Connect to Server A
conn_a = mysql.connector.connect(
    host="localhost",
    user="user_a",
    password="password_a",
    database="dbA"
)
cursor_a = conn_a.cursor()
cursor_a.execute("SELECT * FROM local_table")
data_a = cursor_a.fetchall()

# Connect to Server B
conn_b = mysql.connector.connect(
    host="remote_host",
    user="user_b",
    password="password_b",
    database="dbB"
)
cursor_b = conn_b.cursor()
cursor_b.execute("SELECT * FROM tableB")
data_b = cursor_b.fetchall()

# Combine Data
combined_data = []
for row_a in data_a:
    for row_b in data_b:
        if row_a[0] == row_b[0]:  # Assuming id is the first column
            combined_data.append(row_a + row_b)

# Output Combined Data
for row in combined_data:
    print(row)

Explanation:

• So, in the above example, in Python, we have to first install the MySQL connector library that will enable database operations. Then, we can create different connections to Server A and Server B. After the connections are established, we can execute queries on each of these servers, fetching the required data.
• Once we fetch data from both sources, programmatically we will combine or join both based on your specific logic.
• For example, we might join data from local_table on Server A with tableB on Server B based on some kind of ID. The consecutive treatment or display is left to our application.
• This method requires more coding and treatment of merging the data but allows for complex data transformations and operations which federated tables might not support.

Conclusion

In summary, selecting data from two different MySQL servers can be accomplished through federated tables or a middle-tier application. Federated tables simplify the process by allowing direct queries across servers but may come with limitations in performance and features. On the other hand, a middle-tier application offers robust control and flexibility for more complex data operations and transformations.

FAQs

How to select data from 2 different tables in MySQL?

Use SQL joins to combine data from two tables: SELECT a.column1, b.column2 FROM tableA a JOIN tableB b ON a.common_column = b.common_column;

How to get data from multiple servers in SQL Server?

Create a linked server with sp_addlinkedserver and query using SELECT * FROM LinkedServerName.Database.dbo.Table;.

How to get data from two different databases in SQL?

Use fully qualified table names: SELECT db1.table1.column1, db2.table2.column2 FROM database1.dbo.table1 db1 JOIN database2.dbo.table2 db2 ON db1.common_column = db2.common_column;.

How to fetch data from one server to another server in MySQL?

Use federated tables to link servers or export data from one server with mysqldump and import it to another with mysql.