6

In MySQL I can query information on another server using federated tables, as long as I've defined the same table structure locally.

In MS SQL Server, however, I can run any SQL statement against a linked server. Is it possible to do the same thing in MySQL?

2
  • possible duplicate of How to create linked server MySQL
    – Kermit
    Mar 22 '13 at 17:37
  • 1
    Not a duplicate. The answer to that other question was to use federated tables. Federated tables requires you to create the same table structure locally which is a bit tedious. I'm interested in running arbitary SQL which federated engine doesn't seem to do.
    – Fidel
    Mar 22 '13 at 17:41
0

How is a "linked server" connection different than any other connection defined by a MySQL client?

In MS SQL Server, however, I can run any SQL statement against a linked server. Is it possible to do the same thing in MySQL?

Maybe I'm missing something but yes, just setup a connection.

mysql -u <user> -p <password> -D <database> -H 127.0.0.1

import MySQLdb
db = MySQLdb.connect(host="localhost", user="<user>", passwd="<password>", db="<database>")

You could go so far as setting up a reverse tunnel with SSH.

mysql -u <user> -p <password> -D <database> -H 127.0.0.1 -P 33061

Other than that, FEDERATED tables are the closet thing.

I must be missing something.

2
  • Hi Justin, it's not always possible to connect a client to a particular database server (for security reasons for example). In my particular case I want to write join queries which get data from tables residing on two different db servers. In ms-sql I can create a linked server and query any table on that server (eg. SELECT * FROM SERVER2.AdventureWorks.Production.Product). But to do the same in mysql I have to define the 'Product' table locally and it has to match the remote table. Cheers mate, Fidel
    – Fidel
    Mar 23 '13 at 11:53
  • Oh and linked servers lets you execute stored procedures as well: EXECUTE SERVER2.AdventureWorks.dbo.uspGetBillofMaterials 718,'2000-06-26'
    – Fidel
    Mar 23 '13 at 14:32
0

TL; DR; No, you can't.

--

You can't run arbitrary SQL against a remote server table without using FEDERATED. See this response.

Although, I don't see the problem with defining locally the tables, specially if you want to use this in production. Read the limitations of the FEDERATED engine beforehand.

If several tables are involved, use CREATE SERVER and then, CONNECTION clause pointing to the 'server/table' as specified in this example. Using it, you can make the process of creating the tables locally a bit easier. Also, you can create a flat SQL file with all the tables that you need at once, then you maintain that file with the additions. Is not the most comfortable, yet is the most organized way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.