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 found this nice solution, you have to download this script on your server and type

$> ./MyDumpSplitter.sh yourfile.sql your_table_name

This will extract your table into your_table_name.sql

---

Optionnal

Now you can rename it using this type of command

$> sed -i 's/`your_table_name`/`your_table_name2`/g' your_table_name.sql

And re-injecting it with

mysql> source your_table_name.sql;

Answer 2

I ran into that problem a while ago and wrote a Perl script. It worked well, but it was an older version of MySQL. Call it like:
extract.pl -table=TABLENAME mysqldumpfile.sql > recovered_table.sql

#!/usr/bin/perl -s -wnl
#extract a single table from a mysql dump
BEGIN {
    $table or warn
    "Usage: $0 -table=TABLE_TO_EXTRACT mysqldumpfile.sql"
    and exit 1;
}

/^DROP TABLE IF EXISTS `$table`/ .. /^UNLOCK TABLES;$/ and print;

Answer 3

simply you can run stream editor for filtering and transforming text command like below:

$ sed -n -e '/CREATE TABLE.*products/,/CREATE TABLE/p' mysql.sql > products.sql

Answer 4

If you want to extract a database you could use:

sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' dumpfile > dbname.sql 2>error

For example:

sed -n '/^-- Current Database: `blogs`/,/^-- Current Database: `/p' dump.sql > blogs.sql 2>error

Answer 5

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 6

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.