Is it possible to write a cross server select query using MySQL Client. Basically the setup is like follows.
Server IP Database
I want to write a query that will select rows from a table in the Test Database on 126.96.36.199 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.
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.