1

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

Improve this question
2
  • This is not an answer to your question, but a suggestion to use "CREATE TABLE table_name_prod_copy LIKE table_name;" instead of creating by selecting and then truncating. [This way you also keep the indexes in the structure]
    – Jehad Keriaki
    Nov 11, 2014 at 22:18
  • Thanks for the tip @JehadKeriaki! I was actually wanting to do something like this because I really just wanted to create the new table with the original table's structure to begin with and not copy in the actual data. I figured there had to be a one liner for it. Thanks again!
    – Michael J
    Nov 14, 2014 at 18:05

1 Answer 1

Reset to default
1

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

Improve this answer
2
  • Thanks for this suggestion. I will RTFM on how to use it!
    – Michael J
    Nov 14, 2014 at 18:06
  • Manny Calavera! Nice to meet you :D
    – FrankyHollywood
    Sep 11 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.