MySQL Federated table | MySQL join across servers

This blog will explain you how to use Federated tables in MySQL. Just follow the steps mentioned below to use Federated table in your MySQL server:

What is Federated Engine?

About Federated Engine in MySQL:

The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.

Why to use Federated Engine?

Allows a user to create a table that is a local representation of a foreign (remote) table.
Real world example:

                  There are two servers-A and B. Both servers {A, B} are on different machines and have different connection parameters (different username, different password etc). In such a case, it is possible to perform a join between a table that is in database A, to a table that is in database B using Federated table.

How to use Federated Engine?

A Federated table consists of two elements:

1) Remote server–

First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the sakila database and is defined like this:

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL
    PRIMARY KEY  (id)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

2) Local server–

Next, create a FEDERATED table on the local server for accessing the remote table:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL
    PRIMARY KEY  (id)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user:fed_user@197.186.1.199:3306/sakila/test_table';

Sample connection strings:

CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'

The basic structure of this table should match that of the remote table, except that the ENGINE table option should be FEDERATED.

Execute:

show variables like '%federated%'; 

to check if FEDERATED storage engine is available on your local server.

The table- federated_table in localhost becomes virtual table of test_table in remote server.

Now you can use the JOIN between the tables in a DB in the localhost server. Suppose if there is a table in sakila- test and you want to JOIN with test_table which is in remote server, write a query like the one shown below:

SELECT * FROM `federated_table` JOIN `test`;

The federated_table in the query will actually refer to test_table in remote server.

Limitations of Federated tables: http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html