June 21, 2012
Join tables from two different server
Question by Gulrej
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 by Starx
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';
