Extracting a table from a mysql.sql dump file

Question

I have a products table in a mysql.sql file. I wish to extract that one table and place it in it's own file. How would you go about doing this?

Answer 1

I know of no tool to parse raw mySQL dump files this way.

Either copy+paste it (potentially cumbersome) or import it into a temporary database, drop everything else, and dump the table back into a file.

Answer 2

If the dump was done with the extended insert syntax, then the actual table data will be done as a single line within the dump file:

INSERT INTO tablename (field, ...) VALUES (data, ...), (data, ...), (etc..)

which you could simply grep out. Extracting the actual table definition will be harder, though it SHOULD be immediately above the data line. Don't have access to a proper shell at the moment, but going off the top of my head, something like this might do the trick (in pseudo-code):

# retrieve line # that data insertion for the wanted table occurs on
DATALINE=`grep -l 'INSERT INTO tablename' dumpfile.sql`

# limiting ourselves to the part of the file up to the data line, find the line number
# of the last CREATE TABLE, which SHOULD be the one creating the table for the data
CREATELINE=`head -$DATALINE|grep -l 'CREATE TABLE'|tail -1|awk '{"print $1"}'`

The you can extract the creation DDL with judicious head/tail and the line numbers we just retrieved:

CREATE=`head -$CREATELINE dumpfile.sql|tail -$($CREATELINE - $DATALINE - 1)`

bear in mind that I'm going off the top of my head on this, so it's almost guaranteed to not work, but should be enough to get you started.