MySQL Cookbook

Using Different MySQL Servers Simultaneously

Problem

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

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.

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