MySQL: SELECT from another server

Question

I'm afraid that I already know the answer to my question, but I'll ask it anyway:

When there are two MySQL DB servers, can I access data that is stored on the other server?

In other words: Can I somehow do this:

INSERT INTO table (x, y, z)
   SELECT x, y, x+y
      FROM [otherserver].[database].[table]

Is the answer really as short as "No"?

Use federated table. Refer this: http://winashwin.wordpress.com/2012/08/22/mysql-federated-table/ — Jacob, Aug 24 '12 at 18:38
This article on ["Running MySQL queries on multiple servers"](http://www.linux.com/feature/52390) sounds like it explains how to do what you want to do. The link is currently dead, so here is the content [from archive.org](http://web.archive.org/web/20090302102331/http://www.linux.com/feature/52390?): — Paolo Bergantino, Feb 03 '09 at 17:35
Thought about something like that for a moment, but I can't do it that way, because we use replication for... well... replication - between other servers. :) — BlaM, Feb 03 '09 at 17:49

score 18 · Accepted Answer · edited Sep 07 '16 at 16:40

18

You can set up federated tables in MySQL to accomplish what you're trying to do. There are some limitations.

http://dev.mysql.com/doc/refman/en/federated-storage-engine.html http://dev.mysql.com/doc/refman/en/federated-usagenotes.html

edited Sep 07 '16 at 16:40

Murilo Garcia

4,537
1
10
11

answered Feb 03 '09 at 18:04

volatilsis

516
4
6

This looks really interesting :) – BlaM Feb 03 '09 at 18:23
4

My recommendation: never ever consider touching federeated tables. They seem like unmaintained for years, instable stuff, horrible to maintain and totally unoptimized. An unlimited amount of troubles ahead – John May 28 '20 at 23:51

score 1 · Answer 2 · answered Feb 10 '18 at 17:52

1

CREATE TABLE `remote_table`(
  `foo` VARCHAR(100),
  UNIQUE KEY(`foo`(30))
) ENGINE=FEDERATED CONNECTION='mysql://thedomain.com:3306/remotedbname/remotetablename';

Then query it like any other table with SELECT, UPDATE, INSERT, DELETE.

answered Feb 10 '18 at 17:52

tim

1,819
2
21
37

score -1 · Answer 3 · answered Jan 12 '21 at 12:15

-1

You can also do this. Let's say you're in the target box (where you want to copy the tables TO):

-bash$ mysqldump -u user -p password -h remote_host --databases \
db_name --tables table1 table2 table3 | mysql -u user -p password \
-D local_db_name

This will pull the data from the remote host with mysqldump and insert it into your local host.

answered Jan 12 '21 at 12:15

user14010443

1
1

Obviously you can do that, but the whole point is to access data that lives on another server, not to (occasionally) copy over the data. – BlaM Jan 19 '21 at 13:16

MySQL: SELECT from another server

3 Answers3

Linked

Related