MySQL-Queries Across Various Servers

Imagine you have two or more isolated MySQL serves instances and you want to execute queries across different server instances. One possible way is to query each sevrer individually, gather the result and combine the results on the client. Another more comfortable way is to execute the query across a single prepared server instance which redirects queries to other remaining MySQL servers.

Therefore the federated table type was implemented starting with MySQL 5.0.3.

CREATE TABLE federated_kontenplan (
tenant INT(10) NOT NULL,
id VARCHAR(6) NOT NULL DEFAULT ‚0‘,
name VARCHAR(255) NOT NULL DEFAULT “
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION=’mysql://username:password@localhost:3506/schappy_test/kontenplan‚;

This statement creates the meta data for the local table ‚federated_kontenplan‚ which mirrors the remote table named ‚kontenplan‚ from the database ‚schappy_test‚ running in the server localhost:3506. Further details can be found in the MySQL reference manual.

Interesting statement from the limitations list: „FEDERATED tables do not work with the query cache.“