MySQL’s FEDERATED storage engine

One of the most exciting features introduced in MySQL 5 is the federated engine. The ability to access data from a remote server without the constraints of replication tickles every programmer’s fancy. The Federated engine allows a DBA to create logical pointers to tables that exist on other MySQL servers and thereby link together separate data islands to form one or more logical databases. The Federated storage engine of MySQL is extremely easy to use and set up, and can quickly turn into a DBA’s best friend if they have to answer customer demands to correlate data that exists on several different physical servers.

Let’s take a quick walk :

  1. MySQL Version: We can validate that our installation has Federated by issuing a simple SHOW ENGINES command from the mysql client program. The FEDERATED storage engine is available beginning with MySQL 5.0.3. It is a storage engine that accesses data in tables of remote databases rather than in local tables.
  2. File Structure: When we create a FEDERATED table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. No other files are created, because the actual data is in a remote table. With the MySQL FEDERATED storage engine, there are no local data files for a table (for example, there is no .MYD file).
  3. Data in Remote Server: A Federated table acts as a pointer to an actual table object that exists on the same or another server. Once this link/pointer has been established, we can perform whatever operations we would like on the remote object (inserts, updates, deletes, reads, etc.), as long as we have been given the privileges to do so. The local server connects to a remote server, and uses the MySQL client API to read, delete, update, and insert data in the remote table.
  4. Capability: Understand that our capabilities on the remote object are restricted to the underlying engine that is serving as the source of the Federated table. For example, if we create a Federated table that is pointing to a MyISAM table on another server, we do not have transaction (commit/rollback) capability. Likewise, a Federated pointer to an Archive engine table would not allow us to update or selectively delete data as Archive tables allow reads and inserts only.
  5. Communication: The local server communicates with the remote server using MySQL client C API functions. It invokes mysql_real_query() to send the statement. To read a result set, it uses mysql_store_result() and fetches rows one at a time using mysql_fetch_row().

How to Use:

Normally, we have two mysql servers running, either both on the same host or on different hosts.

On the remote server, we have:

CREATE TABLE `city_remote` (
  `city_id` int(11) NOT NULL auto_increment,
  `name` char(35) NOT NULL default '',
  `country_code` char(3) NOT NULL default '',
  `district` char(20) NOT NULL default '',
  `population` int(11) NOT NULL default '0',
  PRIMARY KEY  (`city_id`)
) ENGINE=MyISAM;

To use that table in our local server, enter the description:

CREATE TABLE `city_local` (
  `city_id` int(11) NOT NULL auto_increment,
  `name` char(35) NOT NULL default '',
  `country_code` char(3) NOT NULL default '',
  `district` char(20) NOT NULL default '',
  `population` int(11) NOT NULL default '0',
  PRIMARY KEY  (`city_id`)
) ENGINE = FEDERATED
connection='mysql://user:pass@remote.com:3306/world/city_remote';

Here, user and pass are valid credentials to access the table city_remote in the database world on server remote.com. The structure of these tables must be exactly the same. With that done, we can query your federated table as if it were in our local server. Issue a query and get a record set.

select * from city_local where city_id = 1;

+----------+--------+----------------+----------+--------------+
| city_id  | name   | country_code   | district | population   |
+----------+--------+----------------+----------+--------------+
|  1       | Noida  | IN             | Noida    |    3000000   |
+----------+--------+----------------+----------+--------------+

There are a few limitations concerning federated engine usage, namely:

  • The remote table must exist when you create your local one.
  • DDL operations are not supported through the Federated engine (ALTER TABLE, etc.).
  • We can’t issue ALTER TABLE commands on a federated table.
  • The federated table is not aware of any structural changes that may occur in the remote one. You may get an error at runtime. The structure definitions of both the source and federated object must stay identical.
  • Transactions are not supported (should be in version 5.1).
  • Query cache support is not enabled.
  • Any DROP TABLE statement issued against a FEDERATED table drops only the local table, not the remote table.
  • FEDERATED tables do not work with the query cache.

.

Comments (11)

  1. derekpm

    Rather interesting. Has few times re-read for this purpose to remember. Thanks for interesting article. Waiting for trackback

  2. Kenny Rogers

    Nice post. Thanks for sharing. Thanks,

  3. Thomas Choe

    Thank you for this site, such as multi information.I! Thank you!,

  4. TRACEY MCGOUGAN

    thank you this topic,

  5. abigael desmond

    Nice man!,

  6. Dennis Miller

    great tips. I enjoyed reading this,

  7. Phyllis Neff

    beautiful site..)),

  8. Arsento

    Interesting and informative. But will you write about this one more?

  9. Harold Wittmaier

    Great work

  10. Kim Teaney

    Very interesting!I

  11. John

    Hmm… I read blogs on a similar topic, but i never visited your blog. I added it to favorites and i’ll be your constant reader.

Leave a Comment

Your email address will not be published. Required fields are marked *