Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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?

share|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

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.

share|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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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