Extracting a table from a mysql.sql dump file


This question already has an answer here:


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


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;

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
    $table or warn
    "Usage: $0 -table=TABLE_TO_EXTRACT mysqldumpfile.sql"
    and exit 1;

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

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

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.

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.

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

Need Your Help

How to wrap lines in a jtable cell?

java swing jtable

I'm trying to implement a custom TableRenderer as described in this tutorial.

Does a sequential stream in Java 8 use the combiner parameter on calling collect?

java java-8 java-stream

If I call collect on a sequential stream (eg. from calling Collection.stream()) then will it use the combiner parameter I pass to collect? I presume not but I see nothing in the documentation. If...