Federating Different Things
So far, I have shown federated tables on a peer-to-peer basis; that is, as a link between two tables in two different MySQL 5.0 servers. That may be the most frequent case, but it does not have to be. There are other objects that you can federate, provided that they look like a MySQL 5.0 table.
Federating Tables On Older MySQL Servers
One thing that looks like a MySQL 5.0 table (in the federated engine's limited view) is a MySQL 4.x or 3.23 table. Because the federated table course of action is currently quite crude, any table on the other side would do, provided that it belongs to a server that can answer to the federated engine basic requests.
If you don't have a 3.23 server hanging around, install one through the MySQL Sandbox:
$ ./express_install /opt/mysql/3.23.58 \ -d server3 -P 3230 -c log=general.log \ -c log-slow-queries -c log-long-format
Then, after loading the MySQL world sample
database on server3, make three federated tables from
server2, as you did for server1. (Don't forget to add
Now you can enjoy the unique pleasure of issuing a nested query on a couple of MySQL 3.23 tables and getting a meaningful result:
server2 (world) > SELECT * -> FROM City -> WHERE CountryCode = ( -> SELECT Code FROM Country WHERE Name = 'Afghanistan'); +----+----------------+-------------+----------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+----------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | +----+----------------+-------------+----------+------------+ 4 rows in set (0.01 sec)
The federated engine did you a favor, turning your nested query into something that the old engine understands:
tail ~/server3/data/general.log1 Connect server2usr@localhost on world 1 Query SHOW TABLE STATUS LIKE 'City' 2 Connect server2usr@localhost on world 2 Query SHOW TABLE STATUS LIKE 'Country' 2 Query SELECT `Code`, `Name`, ... FROM `Country` WHERE (`Name` = 'Afghanistan') 1 Query SELECT `ID`, `Name`, ... FROM `City` WHERE (`CountryCode` = 'AFG')
Rule number 7: You can federate any MySQL table, regardless of the server version.
Think about the possibilities. You can federate any existing MySQL table and use this link to suit several purposes. For example, you can consolidate data from several sources into a data warehouse, or you can just check from a centralized point whether all of your servers are working properly.
Another thing that looks like a table, from the particular angle of the federated engine, is a view. You can cheat the engine into linking to a view instead of a table, which opens a whole new world of opportunities.
Rule number 8: You can federate a view instead of a table.
For instance, you can mitigate the terrible performance of aggregating
functions by using views. First, create a view that simply counts
server1 (test) > CREATE VIEW tkey_count AS SELECT COUNT(*) AS how_many FROM tkey; Query OK, 0 rows affected (0.00 sec)
Then, on the second server, federate that view as if it were a table:
create table tkey_count_fed (how_many int) ENGINE=FEDERATED CONNECTION='mysql://server2usr:email@example.com:5001/test/tkey_count';
Now try it. This time the count comes in no time at all.
server2 (test) > SELECT * FROM tkey_count_fed; +----------+ | how_many | +----------+ | 518584 | +----------+ 1 row in set (0.00 sec) server2 (test) > INSERT INTO tkey_fed VALUES (NULL, 'this_was_not_there'); Query OK, 1 row affected (0.00 sec) server2 (test) > SELECT * FROM tkey_count_fed; +----------+ | how_many | +----------+ | 518585 | +----------+ 1 row in set (0.00 sec)
Your satisfaction will be almost total when the general log shows that the request did not trigger any unpleasant side effects.
$ tail ~/server1/data/general.log 8 Query SHOW TABLE STATUS LIKE 'tkey_count' 8 Query SELECT `how_many` FROM `tkey_count` 060613 21:18:24 4 Query INSERT INTO `tkey` (id, word) VALUES (0, 'this_was_not_there') 060613 21:18:28 8 Query SHOW TABLE STATUS LIKE 'tkey_count' 8 Query SELECT `how_many` FROM `tkey_count`
Still, there is room for improvement. This solution does not allow a
WHERE clause for the
COUNT query. All worries should
dissipate when you read the next section.
Executing Remote Commands With Federated Tables and VIEWS
It's time to open the Wizardry department and give the federated engine a workout.
You can execute predefined functions on a remote server by implementing a simple framework based on a federated table, a view, and a few support tables.
On server1, create:
exec_tablewith just a record,
input_table, in which to insert the wanted command and its parameters,
output_table, in which to report the result of the command,
exec_functionthat reads from the input table and executes the request, writes the result to
output_table, and returns 1, and
exec_viewthat reads the only record from
exec_tableand has a call to
On server2, create:
- one federated table to
- one federated table to
- one federated table to
The full implementation of the framework requires some more components to allow different users to execute remote commands concurrently, and some additions to allow the execution of arbitrary commands, but I won't burden you now with such details. I plan to describe the full framework in a separate article.
Rule number 9: You can exploit the federated engine to execute remote commands.
Half Baked? A Glimpse At Future Developments
Finally, I should mention that there are plans for improving the federated engine in future releases of MySQL.
This knowledge comes from attending a presentation at the MySQL Users
Conference 2006, where Patrick Galbraith explained that the plan is to
implement a new
servers table to hold the connection parameters
for a remote server. Unfortunately, this implementation won't allow a name for
the table being linked, which will have the same name as the local one. This
fact will make the usage of federated tables much harder in some situations
where you need to have, in the same database, a local table X and a link to table
X on one or more remote servers. I appeal to the MySQL developers to implement
this feature in the right way. For example, something along the lines of:
CREATE SERVER server_def ( host : 'my.host.mydomain.org', port : 3306, user : 'uname', password : 'mysecret', database : 'my_db_name' ); CREATE TABLE xyz ( ... ) ENGINE = FEDERATED CONNECT TO 'table_name' USING server_def;
Such an enhancement would yield the clear syntax combined with the freedom of defining a table name.
Developer's angle: There will also be a
tables system table, so this shouldn't be a problem
Federated tables are one great feature in MySQL. With the right amount of documentation and test units, it can really make a difference, turning MySQL into a distributed DBMS.
Thanks to Roland Bouman and Beat Vontobel for reviewing this draft and
providing useful advice. In particular, I owe Roland for an improvement on the
Thanks to Patrick Galbraith for answering some questions about the future development of this engine.
Giuseppe Maxia is a QA developer in MySQL community team. A system analyst with 20 years of IT experience, he has worked as a database consultant and designer for several years. He is a frequent speaker at open source events and he's the author of many articles. He lives in Sardinia (Italy).
Return to the Databases.