We sometime need to access the data (tables ) which lies on another remote MySQL DB Server , MySQL provides an engine type federated which does exactly what we want .

The FEDERATED storage engine enables data to be accessed from a remote MySQL database on a local server without using replication or cluster technology. When using a FEDERATED table, queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables.

federated table in action

check if the federated engine is enabled

Mysql > show engines;

+————+———+—————————————————————-+
| Engine | Support | Comment |
+————+———+—————————————————————-+

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) |
| CSV | YES | CSV storage engine |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| FEDERATED | YES | Federated MySQL storage engine |
| ARCHIVE | YES | Archive storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
+————+———+—————————————————————-+

if it is not enabled go to /etc/my.cnf add federated=ON int the [mysqld] section and restart the MySQL .

Creating original table on remote server

CREATE TABLE test_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT ”,
other INT(20) NOT NULL DEFAULT ’0′,
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

Create table on the local server

Two methods

1) Creating a FEDERATED Table Using CONNECTION

CREATE TABLE test_table(
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT ”,
other INT(20) NOT NULL DEFAULT ’0′,
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION=’mysql://fed_user@remote_host:9306/federated/test_table’;

more details can be checked on http://dev.mysql.com/doc/refman/5.1/en/federated-create-connection.html

2)Creating a FEDERATED Table Using CREATE SERVER

For example, to create a server connection identical to the CONNECTION string:

CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql

This server name is used while creating the federated table in CONNECTION

CREATE TABLE test_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT ”,
other INT(20) NOT NULL DEFAULT ’0′,
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION=’fedlink/test_table’;

more details can be checked at http://dev.mysql.com/doc/refman/5.1/en/federated-create-server.html

Access the data of the federated table on local server

mysql>use test ;

mysql>select * from test_table ;

federate table acts as a local table and all the select and DML operations can be done as if the table is lying on the local server ,
please note that no actual data is stored on the local sever.

now we can have the cross MySQL server data access

mysql > select * from localtable join test_table on localtable.id =test_table.id ;

Important things to remember

• FEDERATED tables may be replicated to other slaves, but you must ensure that the slave servers are able to use the user/password combination that is defined in the CONNECTION string (or the row in the mysql.servers table) to connect to the remote server.
• The remote server must be a MySQL server.
• The remote table that a FEDERATED table points to must exist before you try to access the table through the FEDERATED table.
• The FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE TABLE, and indexes. It does not support ALTER TABLE, or any Data Definition Language statements that directly affect the structure of the table, other than DROP TABLE. The current implementation does not use prepared statements. Transactions are not supported.
• There is no way for the FEDERATED engine to know if the remote table has changed. The reason for this is that this table must work like a data file that would never be written to by anything other than the database system. The integrity of the data in the local table could be breached if there was any change to the remote database.
• 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.

Thanks
Pankaj Joshi

This entry was posted on Friday, July 16th, 2010 at 9:06 am and is filed under Linux, Mysql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.