MySQL – How to Copy Data to a Different Server Using SELECT * INTO

MySQLselecttable

I'm not a DBA by any means. I'm just knowledgeable enough with the MySQL command line to be capable half the time and dangerous the rest.

Today I was working on exporting data from a one DB into another DB. The exercise was to take production data back into staging. Since the table name exists in both places, a new table needed to be created in staging with a name like "_prod_copy"

First I did an export on the production server and scp'd the file over to the staging server. On staging, I created the table in staging using:

CREATE TABLE table_name_prod_copy AS (SELECT * FROM table_name);
TRUNCATE table_name_prod_copy;

Then I used the command line to import.

Everything worked great.

I did research afterward and found this command in an example:

SELECT * INTO newtable [IN externaldb] FROM table1;

I'm going to RTFM, but also wanted to ask here:

Is there a way to tell mysql that the other DB, 'externaldb' in this case is on another server? I know there are command line ways to export from one DB server into another DB server using SSH. Just wondering if this command could do something similarly internal to the tool.

Thanks,

Michael J

Best Answer

You might be interested in FEDERATED table engine. It allows a local server to see tables in different server. I don't recommend it in normal usage, but to move table data around, it could be an option.

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

Related Question