How to make a JOIN between two tables from two different databases?

Navigation
20

I have the personal data (login, password, name, etc.) of the users on a different server than I will use in a new project. I would like to know how I can cross-reference information between two tables, from databases on different servers, with different login credentials as well. I intend to use only the PDO / MySQLi resources to make these connections, since I use shared hosts .

Example of what I wanted, in concept: 

SELECT * FROM 'serverLocal'.'banco'.'tabela' 
  INNER JOIN 'serverRemoto'.'banco'.'tabela' 
  ON 'serverLocal'.'banco'.'tabela'.'uniqid' = 'serverRemoto'.'banco'.'tabela'.'uniqid'
WHERE 'serverRemoto'.'banco'.'tabela'.'email' = '[email protected]';

In this example, records are related through uniqid 's that are the same for lines with related content.

I thought about creating a REST API, but I do not know how to use this information in JOIN , for example. How to make this work? What other solutions would be relevant to solve this problem, and why? Does PDO / MySQLi already have some native functionality so you can "join" two connections to banks on different servers?

    
asked by anonymous 05.02.2014 / 22:04

2 answers

14

Try using Federated tables , you create a replica of the table 'pointing' to the remote bank.

Example: 

CREATE TABLE federated_tabela (
    codigo INT(20) NOT NULL AUTO_INCREMENT,
    nome   VARCHAR(32) NOT NULL
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://usuario:[email protected]:3306/banco/tabela';

Then just do join normal.

More information here .

    
05.02.2014 / 22:25
13

Federated tables , is a good option, but if for your case it fails for some limitation, you have at least three more options

Application next to a database directly access the other database

A simple way is to have your language first access the local table, see what data to get from the remote table, and then access the remote table and get only the data of interest. It is not a database-level JOIN, but in the application. It's simple and the typical approach to be made when complexity.

Advantages

  • Simple. Works even on shared hosting

Disadvantages

  • Requires more work in the application. It is much simpler than the next option, and may be simpler than the last
  • Is less flexible than table replication

Change your application, so that APIs can talk and exchange information

You can create an API close to each database that can be accessed by the other database (such as a REST service) or unify access to shared tables in a single service.

A common case of this is user authentication, where only data is initially obtained, but is not a constant access.

Advantages

  • For some cases, such as user data and authentication, centralization could be done in LDAP, and some systems already have LDAP ready code
  • Allows you to access too many places without having to pass the password of your direct database
  • Enables additional checks, easier to do if you master more your language than SQL
  • Allows caching

Disadvantages

  • It's complex to implement
  • Does not allow JOIN
  • Requires significant rewriting of the routines you want

Database Replication

This method is similar to Federated tables , but you will exchange additional complexity for immediate performance and advantages in case the remote host is inaccessible and prevent the Single Fault Point .

See documentation on how to do replication .

Advantages

  • Allows all types of operations compared to a normal table, including JOINs
  • Avoid Single Point of Failure . Even if the remote server is inaccessible, you will still have data backup
  • It's simpler to configure than creating a REST API

Disadvantages

  • Disk space is larger. The same content will be stored in different databases ( not usually a big problem near the advantages )
  • You must have SUPER privileges, which will not be used if you use shared database hosting.
10.02.2014 / 11:46
Why using Sessions is not a good alternative in authentication? What is Cross-Cutting and what is its relationship to Aspect Oriented Programming (AOP)?