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

Question

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 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, the records relate through uniqid'which are the same for lines with related content.

I thought about creating a REST API, but I don’t know how to use this information in a JOIN, for example. How do you make this work? What other solutions would be relevant to solving this problem, and why? PDO / Mysqli already brings some native resource so you can "merge" two database connections into separate servers?

Answer 1

Try to use Federated Tables, you create a replica of the 'pointing' table for 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 the join normal.

More information here.

Answer 2

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

Application close to a database directly access the other database

One simple way is to make your language access the local table first, see what data you should 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 is simple and the typical approximation to be done when complexity.

Perks

• Simple. Works even in shared accommodations

Disadvantages

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

Change your application so that Apis talk and exchange information

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

A common use case for this is user authentication, where only initially the data is obtained, but it is not a constant access.

Perks

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

Disadvantages

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

Replication of the database

This method is similar to Federated tables, however you exchange additional complexity for immediate performance and advantages in case the remote host is inaccessible and avoid the helpless Single Point of Failure.

See documentation on how to replicate.

Perks

• Allows all type of compared operation of a normal table, including Joins
• Evita Single Point of Failure. Even if the remote server becomes inaccessible, you will still have backup of the data
• It’s easier to configure than to create a REST API

Disadvantages

• Disk space is larger. The same content will be stored in different databases (is not usually a big problem near the advantages)
• It is necessary to have privileges of SUPER, which prevents it from being used if you use shared database hosts.