In MySQL, I have two different databases -- let's call them A and B.
在MySQL中，我有两个不同的数据库 - 让我们称它们为A和B.
Database A resides on server server1, while database B resides on server server2.
数据库A驻留在服务器server1上，而数据库B驻留在服务器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).
两个服务器{A，B}在物理上彼此靠近，但是在不同的机器上并且具有不同的连接参数（不同的用户名，不同的密码等）。
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?
在这种情况下，是否可以在数据库A中的表与数据库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).
如果是这样，我如何以编程方式在python中进行呢？ （我使用python的MySQLDB分别与每个数据库进行交互）。
3 个解决方案
#1
22
Try to use FEDERATED Storage Engine.
尝试使用FEDERATED存储引擎。
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:
解决方法：可以使用另一个DBMS在两个数据库之间检索数据，例如，您可以使用MS SQL Server中的链接服务器来执行此操作（请参阅sp_addlinkedserver存储过程）。从文档：
A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources.
链接服务器允许访问针对OLE DB数据源的分布式异构查询。
#2
3
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.
它非常简单 - 从一台服务器中选择数据，从另一台服务器中选择数据并使用Python进行聚合。如果您希望使用JOIN进行SQL查询 - 将两个服务器的结果放入本地SQLite数据库中的单独表中，并使用JOIN编写SELECT。
#3
3
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.
一个数据集由一个MySQL副本控制，另一个数据集在另一个MySQL副本的控制之下。该查询只能由其中一个（MySQL）服务器处理。
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.
如果您在第一台服务器上创建第二个数据库的副本，反之亦然（获得最少更新的数据库最好），您可以设置复制以使副本保持最新。然后，您就可以根据需要运行查询。
#1
22
Try to use FEDERATED Storage Engine.
尝试使用FEDERATED存储引擎。
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:
解决方法：可以使用另一个DBMS在两个数据库之间检索数据，例如，您可以使用MS SQL Server中的链接服务器来执行此操作（请参阅sp_addlinkedserver存储过程）。从文档：
A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources.
链接服务器允许访问针对OLE DB数据源的分布式异构查询。
#2
3
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.
它非常简单 - 从一台服务器中选择数据，从另一台服务器中选择数据并使用Python进行聚合。如果您希望使用JOIN进行SQL查询 - 将两个服务器的结果放入本地SQLite数据库中的单独表中，并使用JOIN编写SELECT。
#3
3
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.
一个数据集由一个MySQL副本控制，另一个数据集在另一个MySQL副本的控制之下。该查询只能由其中一个（MySQL）服务器处理。
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.
如果您在第一台服务器上创建第二个数据库的副本，反之亦然（获得最少更新的数据库最好），您可以设置复制以使副本保持最新。然后，您就可以根据需要运行查询。