How to access a database in a different server?

Question

Today I have 2 databases (DB_A and DB_B) in the same server (SERVER_1). When I need to access tables from one to the other - in a trigger, for example - all I need to do is to refer to the desired table like this:

-- Code in DB_A accessing DB_B
...
select * from DB_B.TableInB
...

But the code above will fail if I move DB_B from SERVER_1 to SERVER_2.

In MS-SQL we can use a linked server, and simply use SERVER_2.DB_B.Schema.TableInB, but I can not find anything similar in MySQL.

You might be interested in [this](http://stackoverflow.com/questions/508100/mysql-select-from-another-server). Now, I'm no expert at all with federated tables, but that should hopefully point you in the right direction. — Chris Forrence, Feb 18 '14 at 14:56

score 0 · Accepted Answer · edited May 23 '17 at 11:57

0

It turns out ChrisForrence is right: federated tables, although limited, are the way to go to link different servers.

edited May 23 '17 at 11:57

Community

1
1

answered Feb 24 '14 at 16:51

tcbrazil

1,315
12
25

score -1 · Answer 2 · answered Feb 18 '14 at 14:56

-1

Use OPENROWSET

SELECT * FROM OPENROWSET('MSDASQL', 'dsn=mydsn;uid=myid;pwd=mypwd;',
'SELECT * FROM TableInB')

answered Feb 18 '14 at 14:56

Ganz

187
5

How to access a database in a different server?

2 Answers2