Tag: federated

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:[email protected]: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.