mysql - I need to join table from other database and sometimes other server

Question

My project has its own database. Also, I use table of users, which is on other database. Two offices have their data on the same server, but third one has its own user table on other server.

So, in lots of queries I need to join either table some_db.users or other_server.some_db.users

What solution would you advise for this scenario?

I use MySQL.

Answer 1

There is Federated tables in MySQL:

The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.

First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the sakila database and is defined like this:

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL
    PRIMARY KEY  (id)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

Next, create a FEDERATED table on the local server for accessing the remote table:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL
    PRIMARY KEY  (id)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user:fed_user@197.186.1.199:3306/sakila/test_table';

Sample connection strings:

CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'

The basic structure of this table should match that of the remote table, except that the ENGINE table option should be FEDERATED.

Execute:

show variables like '%federated%'; 

to check if FEDERATED storage engine is available on your local server.

The table federated_table in localhost becomes virtual table of test_table in remote server.

Now you can use the JOIN between the tables in a DB in the localhost server. If there is a table called test in your localhost server, and you want to JOIN with the former sakila.test_table which is in the remote server, write a query like the one shown below:

SELECT * FROM `federated_table` JOIN `test`;

The federated_table in the query will actually refer to test_table in remote server.

---

On enabling FEDERATED Storage Engine

The FEDERATED storage engine is not enabled by default in the running server; to enable FEDERATED, you must start the MySQL server binary using the --federated option.

NOTE:
Optional storage engines require privileges and will fail to load when --skip-grant-tables is specified.

The result the entire db will fail to load and the following error will appear in the logs:

110318 21:37:23 [ERROR] /usr/local/libexec/mysqld: unknown option '--federated'

This in turn means that an upgrade from 5.x needs to be done in two steps if you have federated tables. Once with --skip-grant-tables and without --federated, the once without --skip-grant-tables and with --federated.

Source: The FEDERATED Storage Engine