CODEWITHSUNDEEP
mysql
Mirza Kamran Baig
Mirza Kamran Baig

Reputation: 13

MySql Select on two servers

How can this be possible on two servers? I m using MySQL and c#.net, the Insert is done perfectly but now I don't know how to do select!

server: 127.0.0.1
tbl_student
roll_no| stu_name
1         | abc
2         | def
3         | xyz 


Server:127.0.0.2
tbl_room
room_id| room_name
1           | A1
2           | A2
3           | A3


tbl_info (on server:127.0.0.2)
id | roll_no | room_id
1 | 1          |2
2 | 2          |3
3 | 3          |3


select i.id, i.roll_no, s.stu_name, r.room_name
from tbl_student as s, tbl_room as r, tbl_info as i
where i.roll_no = s.roll_no and i.room_id = r.room_id

Upvotes: 1

Views: 74

Answers (2)

faster2b
faster2b

Reputation: 672

You need FEDERATED Storage Engine for link one table in the second server to the first.

If your main server is: 127.0.0.2 you can mapped the table tbl_student present in the server 127.0.0.1 in the other server, before you need to create a mirror table (pseudo code):

CREATE TABLE `tbl_student `(`roll_no` Int, stu_name VARCHAR(100))) ENGINE=FEDERATED
  CONNECTION='MYSQL://127.0.0.1:3306/dbname/tbl_student ';

Now you can operate only in the main server.

The FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE, and indexes. It does not support ALTER TABLE, or any Data Definition Language statements that directly affect the structure of the table, other than DROP TABLE. The current implementation does not use prepared statements.

Performance on a FEDERATED table is slower.

For more info: http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

I hope you helpful

Upvotes: 0

vinibarr
vinibarr

Reputation: 498

I don't know which version you are using. Try to make a research on DB-Link. This is the term used to what you need.

In a quick research I saw that is a openend ticket on mysql dev group: http://dev.mysql.com/worklog/task/?id=1150

Upvotes: 1

Related Questions