Using Different MySQL Servers Simultaneously

12.23.1 Problem

You want to run a query that uses tables in databases that are hosted by different MySQL servers.

12.23.2 Solution

There is no SQL-only solution to this problem. One workaround is to open separate connections to each server and relate the information from the two tables yourself. Another is to copy one of the tables from one server to the other so that you can work with both tables using a single server.

12.23.3 Discussion

Throughout this chapter, I've been making the implicit assumption that all the tables involved in a multiple-table operation are managed by a single MySQL server. If this assumption is invalid, the tables become more difficult to work with. A connection to a MySQL server is specific to that server. You can't write a SQL statement that refers to tables hosted by another server. (I've seen claims that this can be done, but they always turn out to have been made by people who haven't actually tried it.)

Here is an example that illustrates the problem, using the artist and painting tables. Suppose you want to find the names of paintings by Da Vinci. This requires determining the ID for Da Vinci in the artist table and matching it to records in the painting table. If the both tables are located within the same database, you can identify the paintings by using the following query to perform a join between the tables:

mysql> SELECT painting.title
 -> FROM artist, painting
 -> WHERE artist.name = 'Da Vinci' AND artist.a_id = painting.a_id;
+-----------------+
| title |
+-----------------+
| The Last Supper |
| The Mona Lisa |
+-----------------+

If the tables are in different databases, but still managed by the same MySQL server, the query need only be modified a bit to include database qualifiers. (This technique is discussed in Recipe 12.3.) For the two tables at hand, the query looks something like this:

mysql> SELECT db2.painting.title
 -> FROM db1.artist, db2.painting
 -> WHERE db1.artist.name = 'Da Vinci'
 -> AND db1.artist.a_id = db2.painting.a_id;
+-----------------+
| title |
+-----------------+
| The Last Supper |
| The Mona Lisa |
+-----------------+

If the artist and painting tables are managed by different servers, you cannot issue a single query to perform a join between them. You must send a query to one server to fetch the appropriate artist ID:

mysql> SELECT a_id FROM artist WHERE name = 'Da Vinci';
+------+
| a_id |
+------+
| 1 |
+------+

Then use that a_id value (1) to construct a second query that you send to the other server:

mysql> SELECT title FROM painting WHERE a_id = 1;
+-----------------+
| title |
+-----------------+
| The Last Supper |
| The Mona Lisa |
+-----------------+

The preceding example uses a relatively simple example, which has a correspondingly simple solution. It's simple because it retrieves only a single value from the first table, and because it displays information only from the second table. If you wanted instead to display the artist name with the painting title, and to do so for several artists, the problem becomes correspondingly more difficult. You might solve it by writing a program that simulates a join:

1. Open a separate connection to each database server.
2. Run a loop that fetches artist IDs and names from the server that manages the artist table.
3. Each time through the loop, use the current artist ID to construct a query that looks for painting table rows that match the artist ID value. Send the query to the server that manages the painting table. As you retrieve painting titles, display them along with the current artist name.

This technique allows simulation of a join between tables located on any two servers. Incidentally, it also can be used when you need to work with tables that are hosted by different types of database engines. (For example, you can simulate a join between a MySQL table and a PostgreSQL table this way.) However, it's still messy, so when faced with this kind of problem, you may wish to consider another alternative: copy one of the tables from one server to the other. Then you can work with both tables using the same server, which allows you to perform a proper join between them. See Recipe 10.17 for information on copying tables between servers.