Emerge Inc. Blog

In a project I was working on, there were two sites on different servers with their own versions of the same table. On one site, the table was only being read from and the contents were static. On the other site, the table was actively being changed by the users. The task was to keep the data in sync on both servers.

One idea was to create a view, but I had never created a view from one server to another, so I looked for a solution for something like a remote view, if there was such a thing. It turns out there is with the MySQL federated storage engine. If you copy the create table syntax of the table you want to view remotely, you can create it on the other server with the federated storage engine and the connection details, and the table will be read from remotely.

First, setup each MySQL server to support the federated engine by turning it on. You can see which storage engines are supported by executing SHOW engines; in MySQL. If it is not supported, then you can turn it on by adding federated=ON to the MySQL configuration file (my.cnf) and then restarting MySQL.

Next, using the create table syntax of the table you want to view, change the engine to federated and add the connection details. For example,
ENGINE=FEDERATED
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table'

Once setup, you can view the contents of the table on the other server. There are some drawbacks though. The biggest drawback is the lack of speed. This is due to the remote connection and the fact that the federated engine does not support indexes. If the table is used in a lot of joins, the performance will definitely reflect it.

In the end, this was not the solution for my project, but it was something worth learning. To read more about it, visit the MySQL federated storage engine reference manual.

This entry was posted on Thursday, January 26th, 2012 at 9:24 am . 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.