Querying for data from two tables in different databases of different servers?

Question

I have one table t1 on database d1 of the server s1 and now another table t2 is in d2 of the server s2.Now i want some data of the table t1 and its related data from t2 table as resultant data.how can i make it possible?

FEDERATED Storage Engine in MySQL 5.0.related document i have seen any other option...what can i do to get data across two different server?

I think how to get data from 2 different servers in one query (witch is impossible) ? — George Boot, Sep 25 '12 at 09:39
how can i get data of both the database tables in one query ? — Jhanvi, Sep 25 '12 at 09:45
Check [this!](http://stackoverflow.com/questions/5145637/querying-data-by-joining-two-tables-in-two-database-on-different-servers) — Bhuvan Rikka, Sep 25 '12 at 09:54
Err..That's for .net right..sorry.. Federated storage engine is the only way i think.. Check [this](http://dev.mysql.com/doc/refman/5.1/en/federated-create-server.html) for better documentation — Bhuvan Rikka, Sep 25 '12 at 10:17
thanks to all ..okie...so finally i have to use federated engine... :( — Jhanvi, Sep 25 '12 at 10:25

score 1 · Accepted Answer · answered Sep 25 '12 at 10:20

1

There are two solutions to your problem:

1). MySQL based using Federated Engine (as mentioned by you). You can go through this Blog post on Federated Engine

2). (As you are using PHP) Fetching data from one server, then either looping through the data and firing relevant queries to another server, or firing single aggregated query to another server based on the data fetched from first server.

Hope it will help you...

answered Sep 25 '12 at 10:20

jsist

5,223
3
28
43

Can you please explain wide your second option ..if you can please ? – Jhanvi Sep 26 '12 at 08:42
@Jhanvi Further to tell anything, we need take into account what actually is the requirement? How much data needs to be fetched from two servers? We need to analyse as per requirement and data in databases, if we need to fire query to other server for each row or cummulatively for some specific rows on other server... – jsist Sep 27 '12 at 22:06
consider the scenario in which there is a user table on firts database of first server and user-details table i want to put on second database of second server so i want to fetch data of the user and some related data of user-detail table in single query without repetitively making connection for every fetch in all over the site... – Jhanvi Sep 28 '12 at 12:08
If i understand that correctly, then i guess you want to save some data of user on one server, while some other data on some other server and you want to fetch that data by firing single query... IMHO, you should use federated engine in this case. It will take care of joins and other optimization issues itself. Well... What is stopping you from using Federated Engine? – jsist Sep 28 '12 at 15:39
well its bounding me for its usage on each time i fire a query,for particular query i have to assign faderated table and for that i have make connections between both thge servers...so i am searching something which can i use freely sitewide without making connections eachtime – Jhanvi Oct 03 '12 at 04:45

Querying for data from two tables in different databases of different servers?

1 Answers1