Federated Storage Engines in MySQL – closest you can get to Oracle Database Links »

December 13, 2011

0 - add yours »

For those of us migrating from Oracle to MySQL, one of the features that is missed the most is database links. Database link, fondly called dblink, allows access to remote database from a local database. When it comes to MySQL, accessing different databases residing on the same server is simple and straight-forward. You just use dbname.table_name to access a table residing in a different database, on the same server. If you notice closely, this is equivalent to accessing tables across schemas in Oracle.

Now the question is how do we access database residing on a remote server? This is simple with Oracle. You just have to add an entry in tnsnames.ora and create a database link. However, there is no such feature in MySQL. In order to access a remote table in MySQL, you need to create a table with exactly the same structure (same constraints, indices etc) and use Federated Storage Engine (FSE).

Here is a good example on the syntax of creating a table using FSE. Once the federated table is created, you just access it as a normal table.

So what is the main limitation of FSE of MySQL? Unlike dbLInks, FSE is applicable at a table level, not at a database level. This means, that you have to create federated tables on the local database individually for every remote table that you need access to.

Where would you use FSE? I typically use it as part of ETL for OLAP apps. I create a read-only user to the production environment and then create a federated tables using this login in the reporting environment. I then schedule a cron job that queries the federated table and loads data into my reporting tables. Once done, all my reports run against my local tables (not the federated ones).