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.

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?

| improve this answer | |
  • 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. – Lester Peabody May 8 '12 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.

| improve this answer | |
  • 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 '12 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. – Jeff Ferland May 8 '12 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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