1

This seems like a fairly obvious bit of functionality that mysql command would include, but I can't seem to pin it down anywhere on the man page for it. My question is this: given a database dump generated by mysqldump ... <databasename> > file.sql can you import all of the tables (and just the tables) included into a separate database? I'm probably missing something obvious, I'm a bit of a noob, sorry.

2 Answers 2

4

Sure:

mysql -uusername -ppassword -hdbserver someotherdatabasename < file.sql

There might be some confusion because you use mysqldump to extract, but mysql to load. Note that what comes out of the mysqldump command are SQL statements, so you can just direct that into the commandline mysql command. Or am I misunderstanding your question?

1
  • I needed to remove a table from the dumped file first, so I just went into the dump file and removed the appropriate table and removed that first, then did the import as you specified. Thanks for the tip cjc. Cheers. May 8, 2012 at 20:04
0

If you want just the table structure to be dumped, create your dump file using mysqldump -d .... That will exclude all data from your dump, leaving just the structure and any other details you might choose to include (triggers, etc.).

If you're stuck with the dump you've got, then you can use some text parsing to find your way about. Except for quoted lines that have a line return in them, passing your file through grep -v '^INSERT' will remove any lines that are placing data into your database.

If you do have more complicated entries where there are line returns in the text, you'll have to use further regex filters to parse out lines that aren't valid SQL commands.

2
  • Instead of parsing through it with regexes, I would just import the whole thing someplace temporary, then dump it out with the -d option. Safer.
    – cjc
    May 8, 2012 at 19:39
  • @cjc If you can afford to load the whole whole thing, then yes that's safer. Load, truncate, dump again... or even skip the truncation and use -d. I've worked with some big dumps where loading the full dump is not practical. To that end, I've also created some state machine perl scripts that turn dumping on / off from a file for select tables only. May 8, 2012 at 20:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .