How to Link Tables with MySQL (FEDERATED Tables)

Linking Tables is a normal Feature in Oracle DB or MS-SQL. It is also (similar) possible with MySQL! The solution in this case is a Plugin called “FEDERATED”. A FEDERATED Table syncs the complete Table with a Remote Database, which is totally awesome (INSERT, UPDATE, DELETE). Normally, it is already installed but deactivated….

How to check if the FEDERATED Plugin is activated?

To check if you have the Plugin installed and activate you just need to login into your mysql instance via SSH / Terminal and then type

mysql> show engines;

Now a list should appear with all Engines. If “FEDERATED” is in the list and “Support” is set to “YES” then you are already ready to go and can skip this part. When is set to “NO” then you need to activate it first or if it don’t appear on the list then you need to install it.

How to install the FEDERATED Plugin?

Login to you mysql instance and type:

mysql> install plugin federated soname 'ha_federated.so';

How to activate the FEDERATED Plugin?

You can edit your my.cnf file. Just add following:

[mysqld]
federated

or add –federated to your mysql starting string.

How to use it?

First of all you need two (or more) identical Tables. You can create your destination Table like a normal Table. Here is a example:

CREATE TABLE destination_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;

Now it is time to create a Table that links to the destination. It needs to have the same structure like the destination table. Here is a example:

CREATE TABLE federated_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://username:password@hostname/database/tablename';

What’s different is the new ENGINE Parameter which is set to FEDERATED and the CONNECTION String. If you created your Federated Table then test it! INSERT, UPDATE, DELETE your first items and check if they are the same as on the destination table. Have Fun!