Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 24 '13 at 11:10

This question came from our site for professional and enthusiast programmers.

    
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

1 Answer 1

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.