Know Your MYSQL: WHAT IS A FEDERATED TABLE

The MySQL has many types of the storage engines and the Federated storage engine is one of them. The Federated storage engine for the MySQL RDBMS (Relational Database Management System) is a storage engine which connects the tables remotely. It treats the table of the remote database as a local data source.

Now first we understand:

WHAT IS A FEDERATED TABLE?

A Federated Table is a table which points to a table in another MySQL database instance (mostly on another server). It can be seen as a view to this remote database table. Other RDBMS have similar concepts for example database links.

WHAT CAN WE DO WITH A FEDERATED TABLE?

To show what we can do with a federated table let’s assume the following condition: We have two different MySQL situated at different location and we required fetching data from both server and in these data set we required a JOIN which is normally can’t done over two different server. But by federated connection it is possible.

To create a Federated table, see the blow example:

create table user (

 id int,

 name varchar(50))

ENGINE=FEDERATED CONNECTION='mysql://username@hostname/databasename/tablename'

The connection URL is in the format of:

scheme://username:password@hostname:port/database/tablename