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

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.

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. Finding Rows in One Table That Match Rows in Another 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:[email protected]/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.

Another approach to joining tables that are hosted by different servers is to write a program that simulates a join:

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 Copying Tables or Databases to Another Server for information on copying tables between servers.