Aug
Extract a Single Table from a mysqldump File
Posted by Jared in Tips & TricksTagged: geek talk, mysql, the geeks
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; !$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.
September 23rd, 2008 at 4:59 am
Hi,
I have tried your perl script..but it giving errors in this line
if (!$conf{’noExtras’} && !$flag)
can u give some more insights..
September 23rd, 2008 at 7:57 am
Sure, can you provide more detail as to the error you’re getting?
Thanks,
Jared
September 29th, 2008 at 3:51 am
I have not changed your perl script..
It is giving this kind of errors when i run this command
extract_sql.pl -t mytable -r mydumpfile > mytable.sql
Operator or semicolon missing before & at ./extract_sql.pl line 25.
Ambiguous use of & resolved as operator & at ./extract_sql.pl line 25.
syntax error at ./extract_sql.pl line 25, near “amp;”
syntax error at ./extract_sql.pl line 25, near “$flag) ”
syntax error at ./extract_sql.pl line 44, near “}”
Execution of ./extract_sql.pl aborted due to compilation errors.
without any argument ,it is giving the same errors..
September 29th, 2008 at 12:02 pm
Hello Shishu,
I believe I understand what is happening now. It appears that you copied and pasted the code from the excerpt above into a file, and then tried to run it. The code snippet above is just an excerpt and is not designed to be run by itself. You need to download the entire script via the link provided near the top of the file. Do that, and give it another try.
I’ve modified the post to add a note near the code snippet to hopefully make it clearer that the entire script should be downloaded for use locally.
Hope that helps, let me know if you still experience a problem.
Thanks,
Jared
October 15th, 2008 at 12:47 pm
Very useful. Saved a fair amount of time and bandwidth copying only the data I wanted. Thanks.
October 27th, 2008 at 6:47 am
Thanks for the script, a real lifesaver. I’ve had a silghtly different need, though – restoring a single database from a dumpfile created with “mysqldump –all-databases”. So, I’ve modified your script to do that as well, and, also, optionally list all databases in the dumpfile. I’ve put the modified version here:
http://lkd.home.pl/misc/extract_sql.pl
This being the first time I’ve ever touched any Perl, it may have some rough edges. However, it did process my 14 GB dump correctly, so it seems to be of at least some use:).
February 9th, 2009 at 4:21 am
Very nice! Saved me quite a lot of load on a replicated system, only restoring the needed table!
Thanks a bunch!
March 8th, 2009 at 5:11 pm
Just wanted to comment on the awesomeness and efficiency of this script. I was literally able to extract 600mb out from a 4gb dump file in under 5 minutes with this beauty.
Thank you SO much!
April 6th, 2009 at 12:20 am
great script…it helped me a lot…
May 30th, 2009 at 4:00 am
Hi
Seems the download of your perl script for extracting a table from MySQL dump is not currently available on the links given in the article – is it available elsewhere as it sounds like exactly what I’m looking for !?
Thanks
John
May 30th, 2009 at 5:25 pm
Oops! Thanks for the heads up, John. We were doing a little housecleaning earlier and it looks we messed up a link. Try again and you should be able to get at it now.
Thanks,
Jared
June 1st, 2009 at 2:05 am
Just wanted to say thanks, this little perl script just saved my arse at work! :)
June 2nd, 2009 at 1:53 pm
Glad you found it useful!
Jared
July 16th, 2009 at 12:23 pm
Jared,
Just wanted to say that we just found this script and it served us well…and I imagine it will continue to do so in the future (as we no longer have to do full database restores when a table gets broken).
Many Thanks!
July 17th, 2009 at 6:05 am
Thanks for the feedback, glad it helped!
Jared
September 21st, 2009 at 6:22 am
Hey Jared,
Thanks for the great script! Very useful and worked perfectly.
B.
October 1st, 2009 at 2:38 pm
Brilliant! Method 1 worked perfectly. Thanks so much
October 9th, 2009 at 1:23 am
i usually use the easiest (but kinda silly) solution. it will work only if you need table data and not its structure, while it does not extract table structure correctly (and has some other minor drawbacks):
more dump.sql | grep table_name > new_file.sql
hth,
lukasz
October 24th, 2009 at 2:04 pm
I have a weird problem which is probably insoluble.
Our server is very low on memory and when I try to restore from my dump file mysql uses it all up and server has to be rebooted.
Dump file is:
2974425620 Oct 24 02:33 mike_ips_3.sql
Yes that is 2.9 G
I thought of editing it and splitting it in two – but no editor can edit a file over 2G in size.
I thought of using your script to extract and then restore one table at a time. That’s really impractical though as there are over 100 tables.
Your script is grat, BTW, many thanks.
October 26th, 2009 at 8:59 am
Thanks Coly, yeah, you might look at archiving some of your data off of the server to bring your table sizes down and make it easier to restore in the event of a disaster. That or beef up your server to handle the memory requirements a restore would take. I guess it all depends on what type of data you’re working with and how much of it you’d need to restore in the event of a failure.
Jared
November 10th, 2009 at 12:49 pm
Awk (method 2) worked like a charm! Thank you!
November 24th, 2009 at 9:54 am
Another thank you from me! I tried using grep but it was taking ages (needed context to get all the lines) .
Thought to check on a search engine and found this. The Perl script got it in a matter of minutes.
December 1st, 2009 at 12:05 pm
[...] [UPDATE: see also following similar post: Extract a Single Table from a mysqldump File] [...]
January 28th, 2010 at 7:14 am
I like your perl script, it was fast.
I added an allTables (-a) option to output all tables to separate files like so:
0000-header.sql
0001-user.sql
0002-user_event.sql
9999-footer.sql
February 3rd, 2010 at 8:23 am
That’s great, Mikael! I’ll send you an Email and maybe you can send me a diff so I can incorporate into our version.
Jared
March 25th, 2010 at 8:53 am
[...] This article has a useful perl script which does just that [...]
May 26th, 2010 at 3:45 am
Thanks for making that perl script – its awesome :) Just what i was lookin for!
May 31st, 2010 at 4:34 am
Hey Jared Cheney, your script has helped me a lot. Thanks a ton :))
All the very best
June 2nd, 2010 at 7:03 am
Excellent resource.. used the awk method to extract 2mb from a 5gb dump file.. worked flawlessly. thanks!
August 3rd, 2010 at 11:10 pm
[...] periods surrounding the table names above are wildcard characters. These steps were found on this website, all credit goes to Jared. These steps worked best for me so I posted them here so it’d be [...]