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