MySQL Cross Server Select Query

Question

Hi,
Is it possible to write a cross server select query using MySQL Client. Basically the setup is like follows.

Server IP       Database
---------       --------
1.2.3.4       Test
a.b.c.d       Test

I want to write a query that will select rows from a table in the Test Database on 1.2.3.4 and insert the result in a table into the Test Database on a.b.c.d
My servers are located miles apart so I will be opening a SSH tunnel to connect the two.

Any pointers?

Answer 1

Since the mysql client can only connect to one server at a time, the short answer is No. But there is always a way...

Recent versions of mysqldump support a --where parameter that can be used to limit the data dumped. This means you have a way of running a simple SELECT (i.e. all columns on one table) and producing valid SQL to INSERT it. You can then pipe the output of such a mysqldump command for the source server to a mysql command to the destination server.

You probably want to include a few options like --no-create-info and --no-add-locks on the mysqldump command. Test it until the output is exactly what you want.

Answer 2

mysqldump could be a solution as mentioned already or you could try using the SELECT ... INTO OUTFILE and then LOAD DATA INFILE ... commands.

MySQL does have the federated storage engine which might be useful to you. Here's some more documentation on it http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html I have to confess that I've not had huge success with it but it might work for you.

The third solution would be to do the work in your application. Read in the results of the SELECT query line by line and INSERT to the other server line by line. You might run into some issues with data types and null handling that way though.

Answer 3

how about using federated tables on one of the servers? create the federated tables based on the remote tables you will use in the query and just run your query as if your database was all local. example below from MySQL site

The procedure for using FEDERATED tables is very simple. Normally, you have two servers running, either both on the same host or on different hosts. (It is possible for a FEDERATED table to use another table that is managed by the same server, although there is little point in doing so.)

First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the federated database and is defined like this:

CREATE TABLE test_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=MyISAM 
CHARSET=latin1;

The example uses a MyISAM table, but the table could use any storage engine.

Next, create a FEDERATED table on the local server for accessing the remote table:

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';

(Before MySQL 5.0.13, use COMMENT rather than CONNECTION.)