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 {AB} 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 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 (
    name   VARCHAR(32) NOT NULL
    PRIMARY KEY  (id)

2) Local server

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

CREATE TABLE federated_table (
    name   VARCHAR(32) NOT NULL
    PRIMARY KEY  (id)

Sample connection strings:


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


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


1 Comment

Filed under MySQL

One response to “MySQL Federated table | MySQL join across servers

  1. Johne449

    Hey, thanks for the blog article.Really looking forward to read more. Much obliged.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s