How to create linked server MySQL

Question

Is it possible create/configure MySQL for functionality like SQL Server's Linked Server?

If yes, would you please tell me how? I'm using MySQL 5.5.

Answer 1

Unfortunately you cannot link an entire MySQL database to another MySQL database like you can with MS SQL. However, you can link individual tables. A federated table is a local table you create that points to a table on another server.

You can run queries and stored procedures just like any other table. The two tables must have the same structure, except the Federated table uses a different database engine: Federated. If you make ANY changes to the structure of the remote table, you should re-create the local federated table.

The process is actually quite easy, here is an example: https://docs.oracle.com/cd/E17952_01/mysql-5.0-en/federated-use.html

In my experience, the time needed to create and implement this process is minimal, even when compared to linked servers. It should take you less than 30 minutes to get your first federated table working, after that its a 5 min process. Last item, when naming your federated table, I give it the same name as the remote table with a "federated_" in front, like federated_customer.

Also, store your federated table definitions as separate stored procedures so you can reuse them anytime you need to create the federated table again, AND so that other developers can see how you generated the federated table.

Answer 2

I am the developer of the MySQL Data Controller. Unfortunately, since we had lack of requests we have stopped development on it. The plugin was 100% functional with MySQL connecting to Oracle, MSSQL or MySQL.

Base on some requests, we had added back a blog and video about it :

http://www.acentera.com/mysql-datacontroller/

Regards, Francis L.

Answer 3

The MySQL Data Controller is a follow-on to the federated engine that allows connection to different database types, such as Microsoft SQL Server or Oracle. I am not sure how development is going, yet.

See: http://en.wikipedia.org/wiki/MySQL_DataController

or: https://launchpad.net/datacontroller

Answer 4

MySQL's FEDERATED engine provides functionality similar to SQL Server's Linked Server (and Oracle's dblink) functionality, but doesn't support connecting to vendors other than MySQL. It's not clear from the question if you need the functionality to connect to vendors other than MySQL.

You might want to look into MySQL Proxy. This doesn't match the architecture of Linked Servers/dblink, but you can probably solve a similar set of problems that you would use Linked Servers/dblink to solve.