A little while back, I wrote this article that explained some awk magic for extracting the restore information for a single table from a mysqldump file.  Since then I’ve created a Perl script that accomplishes the same thing, but is a little more flexible.  The Perl script is available for download here.  I’ve tested it on Linux, but it should work from Windows as well if you have Perl installed. So now you have three options when needing to get one table out of your mysqldump file.

Method 1: Perl script

This script will parse a full mysqldump file and extract the necessary portions required to restore a single table.  The output is printed to STDOUT, so you’ll want to redirect to a file from the command line, like so: extract_sql.pl > somefile.sql

Usage Summary (run the script with no parameters and you’ll see this):

Usage:  extract_sql.pl -t <table name> -r <restore file> [options]

Required:
-t <table name>       table name to extract from the file

Optional:
-r <restore file>     mysqldump file that you want to parse. Uses STDIN if
nothing is specified
--listTables          If set, then a list of tables existing in
your restore file is returned,
and no other actions are taken
--noExtras            If set, then extra cmds at top of mysqldump file
will not be included (such as disabling foreign key checks).
Usually you will want these things changed before restoring a
table, so the default is for these to be included.
-v                   verbosity - use multiple times for greater effect
-h                   Display this help message

So, to extract the info needed to restore table ‘mytable’ from the mysqldump file ‘mydumpfile’, you’d run:

extract_sql.pl -t mytable -r mydumpfile > mytable.sql

or, if your dump file is gzipped, you could save a little time and space by doing:
cat mydumpfile.gz | gunzip | extract_sql.pl -t mytable > mytable.sql

To see what table names are within your mysqldump file, run:

extract_sql.pl –listTables -r mydumpfile

The script has a lot of extra functions, etc. in it for logging and cmd-line parsing, but the meat of what it does is here (NOTE! This is not the entire script, just an excerpt of it, use the download link near the beginning of this file to obtain the entire script to use it yourself):

if ($conf{'restoreFile'}) {
## open the mysqldump file
open(STDIN, "< $conf{'restoreFile'}") || quit("ERROR => Couldn't open file $conf{'restoreFile'}: $!", 3);
}

my $flag = 0;

## go through the file one line at a time
while (my $line = <stdin>) {

if ($conf{'listTables'}) {
if ($line =~ /^-- Table structure for table `(.*)`/) {
print $1 . "\n";
}
}
else {

## if we're not ignoring extra lines, and we haven't set the flag, and if it's not a 40000 code, then print
if (!$conf{'noExtras'} &amp;amp;&amp;amp; !$flag) {
if ($line =~ /^\/\*!(.....).*\*\//) { print $line unless ($1 == 40000); }
}

## set a flag when we encounter the table we want
if ($line =~ /^-- Table structure for table `$conf{'tableName'}`/) {
$flag = 1;
printmsg("Turning flag on", 1);
}
## turn flag off as soon as we encounter next table definition
elsif ($line =~ /^-- Table structure for table/) {
$flag = 0;
printmsg("Turning flag off", 1);
}

## if flag is set, then print to STDOUT, otherwise just move on
if ($flag) {
print $line;
}
}
}

Method 2: awk

First, you have to know where in your mysqldump output you want to begin your extraction, and where you want to end it. The key here is finding something unique at the beginning and ending of the block that won’t be found anywhere else.

A sample mysqldump contains something like the following:

--
-- Table structure for table `test1`
--
...
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` ( ...
LOCK TABLES `test1` WRITE;
INSERT INTO `test1` VALUES (1,0,’2 ...
UNLOCK TABLES;
...
–-
–- Table structure for table `test2`
–-

As you can see, we have a line with the comment “Table structure for table `test1`”, then all of the dropping, creating, and inserting for the table, and then another comment for the next table. These two lines are perfect for grabbing all of the operations pertinent to our one table.

To extract the dump for a single table from an entire database dump, run the following from a command prompt:

$ awk ‘/Table structure for table .test1./,/Table structure for table .test2./{print}’ mydumpfile.sql > /tmp/extracted_table.sql

The above command searches through the dump file, and as soon as it matches a line containing the first search string (denoted by the first set of slashes), it prints that line and every subsequent line until it encounters a line containing the second search string (denoted by the second set of slashes). FYI, the periods surrounding the table names above are wildcard characters.

Now the extracted_table.sql file contains the SQL to restore your table. One final thing: There are usually various parameters at the top of your mysqldump file that you may need to set before restoring your table, depending on the complexity of your database (i.e. disabling foreign key checks.)

To restore your table, you’d run:

$ mysql -u user -ppassword mydb < /tmp/extracted_table.sql

Voila! – you’re back in business.

Method 3: Restore elsewhere and extract

Another option is to restore your data into a temporary database (assuming you have the disk space), extract the table you’re interested in to it’s own dump file, and then restore that dump file to your original database.

Create a new database, name it something easy to distinguish from your production one, i.e. fakedb.

Restore your data to the fakedb with a command like this:

$ mysql -u user -ppassword fakedb < mydumpfile.sql

From fakedb, grab the data you want from your target table:

mysql> select * from targettable into outfile “/tmp/mytablebackup.bak”;

On the production db, where you have your undesired data, clear it all out with:

mysql> delete from baddatatable;

Import the good stuff back into it:

mysql> load data infile “/tmp/mytablebackup.bak” into table baddatatable;

Now you can rinse and repeat if you want to keep trying whatever your initial operation was until you perfect it. Make your changes, delete everything, load it back in, redeux.

Hope this is useful.

NOTE: A newer article has been written detailing some additional methods for doing this. Read it here

Warning: the content which is to follow contains a lot of acronyms and jargon that is only meant for the technically strong and the very computer-literate. If you do not speak ‘command-line’ and don’t understand the meaning of words such as ‘mysql’ or ‘linux’ or ‘awk’, then my condolences – and you can just skip along to the other fare on our blog. You might enjoy the 38 seconds post or seeing a cougar grabbing a Bull Elk by the jugular.

I thought it was high time that we provide some material that would aid and assist our fellow system admins on the ‘net. When you’re going through the daily grind of taming all of those ones and zeros that just want to be free – anything that can save you a little bit of time and frustration is always welcome.

Someday you may find yourself in a situation where you want to restore a single table of your MySQL database. Hopefully this is in a lab situation or on a personal development box somewhere – but even if it is production you need not fear. There are plenty of articles about restoring your entire database, but not a lot about restoring a single table. What if your coding compadre’s mistakenly change the field for every row in a table on you, or maybe you want to alter all of the data in a table but want an easy way to ‘reset’ things so that you can tweak and try again? If you aren’t taking table level backups restoring that single table can be rather difficult. You’ll locate your MySQL dump, and realize that it is huge (even when zipped), and you don’t want to wait around to restore your entire DB in order to recover one measly table. So what to do? The file is too large to open in a text editor and do anything meaningful with it – it would take way too much time. This is where a little bit of ‘awk‘ magic can save your day. Using awk, you can extract a block of text from the MySQL dump that pertains to the table you want to work with.

First, you have to know where in your mysqldump output you want to begin your extraction, and where you want to end it. The key here is finding something unique at the beginning and ending of the block that won’t be found anywhere else.

A sample mysqldump contains something like the following:

--
-- Table structure for table `test1`
--
. . .
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` ( . . .
LOCK TABLES `test1` WRITE;
INSERT INTO `test1` VALUES (1,0,'2 . . .
UNLOCK TABLES;
. . .
--
-- Table structure for table `test2`
--


As you can see, we have a line with the comment “Table structure for table `test1`”, then all of the dropping, creating, and inserting for the table, and then another comment for the next table. These two lines are perfect for grabbing all of the operations pertinent to our one table.

To extract the dump for a single table from an entire database dump, run the following from a command prompt:

$ awk ‘/Table structure for table .test1./,/Table structure for table .test2./{print}’ mydumpfile.sql > /tmp/extracted_table.sql

The awk command is very powerful – the above command searches through the dump file, and as soon as it matches a line containing the first search string (denoted by the first set of slashes), it prints that line and every subsequent line until it encounters a line containing the second search string (denoted by the second set of slashes).  FYI, the periods surrounding the table names above are wildcard characters.

Now the extracted_table.sql file contains the SQL to restore your table! One final thing: There are usually various parameters at the top of your mysqldump file that you may need to set before restoring your table, depending on the complexity of your database (i.e. disabling foreign key checks.)

To restore your table, you’d run:

$ mysql -u user -ppassword mydb < /tmp/extracted_table.sql

Voila! – you’re back in business.

Alternate Method

A second option is to restore your data into a temporary database (assuming you have the disk space), extract the table you’re interested in to it’s own dump file, and then restore that dump file to your original database.

Create a new database, name it something easy to distinguish from your production one, i.e. fakedb.

Restore your data to the fakedb with a command like this:

$ mysql -u user -ppassword fakedb < mydumpfile.sql

From fakedb, grab the data you want from your target table:

mysql> select * from targettable into outfile “/tmp/mytablebackup.bak”;

On the production db, where you have your undesired data, clear it all out with:

mysql> delete from baddatatable;

Import the good stuff back into it:

mysql> load data infile “/tmp/mytablebackup.bak” into table baddatatable;

Now you can rinse and repeat if you want to keep trying whatever your initial operation was until you perfect it. Make your changes, delete everything, load it back in, redeux.

Hope this is useful.