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 only takes a minute to sign up.
Sign up to join this community
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?
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.
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.
