Recipe 12.15. 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 database, you can identify the paintings by using the following statement to perform a join between the tables:

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

Now suppose that the painting table is not available on the MySQL server to which we normally connect but is located remotely on another MySQL server. We can access the remote table as though it is local by creating a FEDERATED table that is defined to have the same structure as the remote table. The CREATE TABLE statement for the FEDERATED table must include table options to specify the FEDERATED storage engine and a connection string that tells our server how to connect to the remote server and locate the table. Section 12.1 shows the original structure of the painting table. To set up a corresponding FEDERATED table, define it like this:

CREATE TABLE fed_painting (   a_id  INT UNSIGNED NOT NULL,                # artist ID   p_id  INT UNSIGNED NOT NULL AUTO_INCREMENT, # painting ID   title VARCHAR(100) NOT NULL,                # title of painting   state VARCHAR(2) NOT NULL,                  # state where purchased   price INT UNSIGNED,                         # purchase price (dollars)   INDEX (a_id),   PRIMARY KEY (p_id) ) ENGINE = FEDERATED CONNECTION = 'mysql://cbuser:[e-mail]/cookbook/painting'; 

The CONNECTION string used here has the following format:

mysql://user_name:pass_val@host_name/db_name/tbl_name             

In other words, the remote server host is remote.example.com, the MySQL username and password are cbuser and cbpass, and the table is named painting in the cookbook database. Adjust the parameters in the connection string as necessary for your network. After creating the FEDERATED table, you can use it to access the remote table as though it were local. For example, to perform the join described earlier in this section, write it as shown here:

mysql> SELECT fed_painting.title     -> FROM artist INNER JOIN fed_painting     -> ON artist.a_id = fed_painting.a_id     -> WHERE artist.name = 'Da Vinci'; +-----------------+ | title           | +-----------------+ | The Last Supper | | The Mona Lisa   | +-----------------+ 

Currently, FEDERATED tables can be used to access only other MySQL servers, not servers for other database engines.

NOTE

In MySQL 5.0 binary distributions, the FEDERATED storage engine is not enabled unless you use a MySQL-Max server. In MySQL 5.1, FEDERATED is enabled by default in binary distributions. If you compile MySQL from source (for either version), use the --with-federated-storage-engine configuration option to enable FEDERATED support.

Another approach to joining tables that are hosted by different servers is to write 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 statement that looks for painting table rows that match the artist ID value. Send the statement to the server that manages the painting table. As you retrieve painting titles, display them along with the current artist name.

This technique enables 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.)

A third approach is to copy one of the tables from one server to the other. Then you can work with both tables using the same server, which enables you to perform a proper join between them. See Section 10.16 for information on copying tables between servers.