MySQL -- Joins Between Databases On Different Servers Using Python?

Question

In MySQL, I have two different databases -- let's call them A and B.

Database A resides on server server1, while database B resides on server server2.

Both servers {A, B} are physically close to each other, but are on different machines and have different connection parameters (different username, different password etc).

In such a case, is it possible to perform a join between a table that is in database A, to a table that is in database B?

If so, how do I go about it, programatically, in python? (I am using python's MySQLDB to separately interact with each one of the databases).

Would it be better if you would have asked for a mean to do a distributed transaction over multiple databases hosted by different rdbms ? — Jérôme Radix, May 02 '11 at 15:40
Refer this it would be helpful: http://winashwin.wordpress.com/2012/08/22/mysql-federated-table/ — Sathish D, Aug 24 '12 at 07:40

score 22 · Accepted Answer · edited Apr 08 '13 at 08:14

22

Try to use FEDERATED Storage Engine.

Workaround: it is possible to use another DBMS to retrieve data between two databases, for example you could do it using linked servers in MS SQL Server (see sp_addlinkedserver stored procedure). From the documentation:

A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources.

edited Apr 08 '13 at 08:14

bluish

26,356
27
122
180

answered Apr 29 '11 at 13:55

Devart

119,203
23
166
186

Ah ha. That's a new one on me. +1 – Jaydee Apr 29 '11 at 15:49
interesting. When I create a `FEDERATED Storage Engine`, I need to 'link' to the database where the data actually is, right? in such a case, are the connection details saved securely in the database whose creating the connection? – user3262424 Apr 29 '11 at 16:38
1

@user540009 I have not found a lot in the manual. But there is something - http://dev.mysql.com/doc/refman/5.1/en/federated-create-server.html. It allows to store connection properties in another system table `mysql`.`servers`. – Devart May 05 '11 at 06:45

score 4 · Answer 2 · answered Apr 29 '11 at 13:41

4

It is very simple - select data from one server, select data from another server and aggregate using Python. If you would like to have SQL query with JOIN - put result from both servers into separate tables in local SQLite database and write SELECT with JOIN.

answered Apr 29 '11 at 13:41

Anatolij

597
3
11

2

but you might have to select millions of rows from 2 servers everytime, which kind of counterfeits the advantage of the JOIN doesnt it? – Flo Apr 29 '11 at 13:47
1

that is right, but if you need to aggregate some data for some kind of report - this is the quick and dirty solution. – Anatolij Apr 29 '11 at 13:58
1

@Flo federated storage engines also holds data in order to make joins possible, performace problem also persists in case of heavy tables. I would opt to use this method instead of federated storage engines. – Krishan Gopal Mar 04 '13 at 12:32

score 3 · Answer 3 · answered Apr 29 '11 at 13:53

No. It is not possible to do the join as you would like. But you may be able to sort something out by replicating one of the servers to the other for the individual database.

One data set is under the control of one copy of MySQL and the other dataset is under the control of the other copy of MySQL. The query can only be processed by one of the (MySQL) servers.

If you create a copy of the second database on the first server or vice versa (the one that gets the fewest updates is best) you can set up replication to keep the copy up to date. You will then be able to run the query as you want.

MySQL -- Joins Between Databases On Different Servers Using Python?

3 Answers3

Linked