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

Asked

Viewed 11,900 times

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 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?

php mysql

  • For example, sites like Stack Exchange, I believe that not everything is in the same server, but there is still the interaction between the sites of the network. Example: I am in Sopt, and when opening Soen, I am already logged in there too. There is cross-referencing of profile information, etc.

  • Actually I wanted a way not to go out copying the same information in various databases of network servers, in this case, login information, for example.

  • I may be wrong, but I don’t see this as good practice. You don’t have to replicate everything for every bank an app uses. But you can seek this information in other ways, such as the alternatives that Emerson Rocha Luiz cited.

2 answers

14


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.

  • I would face some performance issue, or ACID?

  • 1

    When it is used Federated tables no data is left in the local table, every query is executed in the remote database. Your performance will depend on the connection and processing of the remote bank. However Federated tables has other limitations also that can be seen here: http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html

  • "There is no way for the FEDERATED engine to know if the remote table has changed. The Reason for this is that this table must work like a data file that would Never be Written to by Anything other than the database system. The Integrity of the data in the local table could be breached if there was any change to the remote database."

  • 1

    This is not about performance, what happens is you create a local table with 1 field called codigo and in the remote bank that single column change the name to id in this you "break" the table.

13

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.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.