44

I have two different server server1 and server2, now I have db1 in server1 and db2 in server2. I am trying to join these two table in MySQL like this.

Select a.field1,b.field2  
FROM  [server1, 3306].[db1].table1 a  
Inner Join [server2, 3312].[db2].table2 b  
ON a.field1=b.field2

But I am getting error. Is is possible in MYSQL.

Starx
  • 77,474
  • 47
  • 185
  • 261
Gulrej
  • 969
  • 4
  • 15
  • 25

3 Answers3

50

Yes, it is possible in MySQL.

There are similar questions asked previously too. You have to use FEDERATED ENGINE to do this. The idea goes like this:

You have to have a federated table based on the table at another remote location to use the way you want. The structure of the table have to exactly same.

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

[Source Answer]

Community
  • 1
  • 1
Starx
  • 77,474
  • 47
  • 185
  • 261
  • 2
    This is very slow with large database tables. I'm still looking for a better method, but FEDERATED tables don't scale well enough to be an option in many cases. – Brilliand Sep 17 '14 at 15:15
  • Hi, did you find a better solution for doing it? – Nomiluks Sep 08 '17 at 05:33
  • @NomanDilawar, Hi, I didn't have to look for a better method yet. If you find one, I would love to see it though. – Starx Sep 08 '17 at 12:35
  • Whilst it's not the same, replication may be a good solution depending on your use case – John Hunt Apr 25 '18 at 12:44
  • @JohnHunt, I would argue against that, as you mentioned it's not the same. Replication is not joining tables from a different server. – Starx May 02 '18 at 08:37
  • This seems optimal in a same network, using private ip. – Máxima Alekz Jul 31 '20 at 14:35
  • Can it be done between MySql and MS Sql Server? Need to call a select between a table in mysql joining a table on ms sql server (both on different servers fisically). – SammuelMiranda Apr 30 '21 at 12:43
3

Replication will be alternate and suitable solution.

server1 - db1 -> replicate to server2. (now db1 and db2 will be in same server server2. join will be easy).

NOTE: If the server2 is enough capable of take the load of db1 in terms of store/process etc., then wen can do the replication. As @brilliand mentioned yes Federated will make the much manual work and slow in process.

naveen_sfx
  • 727
  • 2
  • 9
  • 18
3

It's kind of a hack, and it's not a join, but I use bash functions to make it feel like I'm doing cross-server queries:

The explicit version:

tb2lst(){
    echo -n "("
    tail -n +2 - | paste -sd, | tr -d "\n"
    echo ")"
}

id_list=$(mysql -h'db_a.hostname' -ume -p'ass' -e "SELECT id FROM foo;" | tb2lst)
mysql -h'db_b.hostname' -ume -p'ass' -e "SELECT * FROM bar WHERE foo_id IN $id_list"

+--------|-----+
| foo_id | val |
+--------|-----+
| 1      | 3   |
| 2      | 4   |
+--------|-----+

I wrote some wrapper functions which I keep in my bashrc, so my perspective it's just one command:

db_b "SELECT * FROM bar WHERE foo_id IN $(db_a "SELECT id FROM foo;" | tb2lst);"

+--------|-----+
| foo_id | val |
+--------|-----+
| 1      | 3   |
| 2      | 4   |
+--------|-----+

At least for my use case, this stitches the two queries together quickly enough that the output is equivalent to the join, and then I can pipe the output into whatever tool needs it.

Keep in mind that the id list from one query ends up as query text in the other query. If you "join" too much data this way, your OS might limit the length of query (https://serverfault.com/a/163390). So be aware that this is a poor solution for very large datasets. I have found that doing the same thing with a mysql library like pymysql works around this limitation.

MatrixManAtYrService
  • 8,023
  • 1
  • 50
  • 61