Join tables from two different server

Question

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.

Answer 1

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]

Answer 2

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.