MySQL Cookbook, 2nd Edition

Using Different MySQL Servers Simultaneously

Problem

You want to execute a statement that uses tables located in databases that are hosted by different MySQL servers.

Solution

Set up a FEDERATED table, which enables one MySQL server to access a table hosted by another MySQL server. Other approaches are to open separate connections to each server and combine the information from the two tables yourself, or 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, we have assumed 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 because a connection to a MySQL server enables you to directly access only tables hosted by that server. However, MySQL supports a FEDERATED storage engine that enables you to remotely access tables that are hosted by another MySQL server. For a FEDERATED table, the local MySQL server takes on the role of a client that connects to another MySQL server so that it can access the remote table on your behalf and make its contents appear to be local.

Here is an example that illustrates the problem, using the artist and painting tables. Suppose that 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 rows in the painting table. If both tables are located within the same ...