mysqlclientcross-server

MySQL Cross Server Select Query

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?

Solution

  • 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.