MySQL – Extract or Restore a Single Table From a Huge mysqldump File

Published

A while back, I wrote an 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 four options when needing to get one table out of your mysqldump file. They’re documented below, in order of speed to complete (fastest first, of course).

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, hopefully the usage summary is self-explanatory about those.

Method 2: sed

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:

$ cat mydumpfile.sql | sed -n -e ‘/Table structure for table .test1./,/Table structure for table .test2./p’ > /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: awk

This is just like our method above with sed, but using awk instead. 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.

Referring to our example mysqldump snippet above, 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. Don’t forget 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

Ta-da! – you’re ready to keep on truckin’.

Method 4: 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.

Busy Developers
Integrate With Us
2

60 Comments

  1. shishu says:

    Hi,

    I have tried your perl script..but it giving errors in this line
    if (!$conf{‘noExtras’} &amp;&amp; !$flag)
    can u give some more insights..

    [Reply]

  2. Jared says:

    Sure, can you provide more detail as to the error you’re getting?
    Thanks,
    Jared

    [Reply]

  3. shishu says:

    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 &amp 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..

    [Reply]

  4. Jared says:

    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

    [Reply]

  5. Melissa says:

    Very useful. Saved a fair amount of time and bandwidth copying only the data I wanted. Thanks.

    [Reply]

  6. Luc Levain says:

    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:).

    [Reply]

  7. Sverre Marvik says:

    Very nice! Saved me quite a lot of load on a replicated system, only restoring the needed table!
    Thanks a bunch!

    [Reply]

  8. Garrett says:

    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!

    [Reply]

  9. Raja Krishnan says:

    great script…it helped me a lot…

    [Reply]

  10. John says:

    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

    [Reply]

  11. Jared says:

    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

    [Reply]

  12. Matt says:

    Just wanted to say thanks, this little perl script just saved my arse at work! :)

    [Reply]

  13. Jared says:

    Glad you found it useful!
    Jared

    [Reply]

  14. JDW says:

    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!

    [Reply]

  15. Jared says:

    Thanks for the feedback, glad it helped!
    Jared

    [Reply]

  16. BO says:

    Hey Jared,

    Thanks for the great script! Very useful and worked perfectly.
    B.

    [Reply]

  17. daniel says:

    Brilliant! Method 1 worked perfectly. Thanks so much

    [Reply]

  18. lukasz says:

    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

    [Reply]

  19. Coly Moore says:

    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.

    [Reply]

  20. Jared says:

    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

    [Reply]

  21. Greg says:

    Awk (method 2) worked like a charm! Thank you!

    [Reply]

  22. Barry Hunter says:

    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.

    [Reply]

  23. […] [UPDATE: see also following similar post: Extract a Single Table from a mysqldump File] […]

  24. Mikael Fridh says:

    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

    [Reply]

  25. Jared says:

    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

    [Reply]

  26. […] This article has a useful perl script which does just that […]

  27. Dan says:

    Thanks for making that perl script – its awesome :) Just what i was lookin for!

    [Reply]

  28. Archana says:

    Hey Jared Cheney, your script has helped me a lot. Thanks a ton :))
    All the very best

    [Reply]

  29. Adria says:

    Excellent resource.. used the awk method to extract 2mb from a 5gb dump file.. worked flawlessly. thanks!

    [Reply]

  30. […] 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 […]

  31. sirmee says:

    Thanks a lot for the script. It saved me a lot of time.

    [Reply]

  32. gcstang says:

    EXCELLENT!!!! Thank you very much works great on a 7.8GB file in a matter of seconds to pull a single table.

    [Reply]

  33. RichSad says:

    This is awesome. Thanks. I am using the perl script on a Mac and it worked great. Using OS X 10.6.6 and Perl 5.8.9 (the version that came bundled with OS X). I did notice a slight typo in the text of the article. It says:

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

    extract_sql.pl –listTables -r mydumpfile

    But the correct command is:

    extract_sql.pl -–listTables -r mydumpfile

    Note the TWO dashes in front of listTables. No big deal, just wanted to let you know.

    [Reply]

  34. Jared says:

    Thanks RichSad, glad it worked for you!

    It looks like our blog was treating my two dashes as a single long dash. I’ve modified it so that it’s using some explicit ‘minus’ entities now for the double-dash, thanks for pointing it out!

    Jared

    [Reply]

  35. JC says:

    Anyone have the script that handles mysql dumps with multiple databases that was linked to by Luc Levian (the link no longer works)? I have a mysql dump with multiple databases in it that have the same table in each of them but only want to restore it for a specific database.

    [Reply]

    Jared Reply:

    JC, I’m not able to get to it either, and I didn’t grab a copy when the grabbing was good :( Sorry

    [Reply]

  36. piyush bagri says:

    Great! Method 1 worked perfectly. Thanks so much.
    Your script save my hours of work

    [Reply]

  37. nicorellius says:

    Awesome little app. Used it and it worked like a charm.

    [Reply]

  38. JT says:

    Saved my ass. Thanks.

    [Reply]

  39. […] Extract a Single Table from a mysqldump File:这篇文章提到了三个办法,分别是:perl脚本(我这里的做法基本“雷同”),awk解析后切割,先恢复到临时库(对大文件这个不现实…)。对比了我们的Perl脚本,这里做了几个改进:可以同时解析出多个表;完成目标表的切割后,则立刻退出,不再扫描剩余部分;会把mysqldump头部输出放到每一个切割文件中,方便各种字符集的恢复; […]

  40. Roger says:

    Thanks man! Your Perl script saved my bacon after accidentally deleting a table.

    [Reply]

  41. Deepak says:

    Thanks buddy, It simply gr8 :)

    [Reply]

  42. Paul Littlefield says:

    Brilliant PERL script, thank you very much! :-)

    [Reply]

    Victoria Reply:

    @Paul Littlefield, I’m glad to hear that our awesome developers were able to help you out! And, of course, you’re very welcome, I hope that you’ll continue to visit our blog for more fun tips and tricks from our dev team :D

    [Reply]

  43. Keith Denby says:

    Great little utility – thanks very much indeed!

    [Reply]

  44. Victoria says:

    @Keith Denby, You are very welcome! The TSheets Crew is always happy to help out!

    Thanks for reading :)
    Victoria

    [Reply]

  45. origami_ns says:

    Extra! AWK rulz, thank you for sharing!

    [Reply]

  46. sed worked better than awk for me:
    zcat mydb.mysqldump.gz | sed -n -e ‘/Table structure for table `share`/,/Table structure for table/p’ | mysql -uroot mydb
    (see jfgagne, http://stackoverflow.com/questions/7103531/how-to-get-the-part-of-file-after-the-line-that-matches-grep-expression-first)

    [Reply]

    Jared Reply:

    @webb phillips, excellent! Thanks for sharing. I had never realized you could use sed this way. I’ve updated the article to mention this method as well. I also found this to be very good reading for understanding how the pattern matching from one spot to another can be used with sed: http://www.thegeekstuff.com/2009/09/unix-sed-tutorial-printing-file-lines-using-address-and-patterns/

    [Reply]

  47. Freaking Lifesaver man! Thanks!

    [Reply]

  48. Peter says:

    Thank you, script works perfect (Debian 6.0.3),time saver!

    [Reply]

  49. J says:

    Method 4 does not work if you have many databases in the backupscript. New databases will be created, and not everything put in “fakedb”.

    [Reply]

    Jared Reply:

    @J,

    If you have multiple databases within a single backup file, you can still use method 4, but you’d have to add a few more command line params, like so: mysql -u user -ppassword -D targetDB –one-database < mydumpfile.sql

    This basically says to ignore any statements except for the ones pertaining to targetDB. (the –one-database param should have 2 dashes in front of it, not sure if that’ll render correctly in a comment)

    Note that you don't have the luxury of using 'fakedb' in this case, you need to use the actual targetDB's name, so don't do this on your production box unless you want to overwrite that DB! Restore it to a non-prod box somewhere. You'll need to create an empty DB named 'targetDB' first, and then do the restore.

    [Reply]

  50. I’m using csplit, which works and performs well, even on very big dumps. Checkou this gist https://gist.github.com/1608062

    [Reply]

  51. Nik Osipov says:

    Thank you so much for the Perl script!!! It saved me a lot of time! Real lifesaver!!! Thank you!!!

    [Reply]

  52. […] The article with the script and other methods of doing this is here. […]

  53. greg says:

    Thank you so much for the time saved by this little awesome perl script.

    [Reply]

  54. Paul says:

    Thank you so MUCH. The SED method #2 described here worked like a charm. I had been extracting tables from huge multi database data dumps via emacs and it was difficult and time consuming. SED is a breeze. First I extract the database I need with the SED delimiters and the I extract the table from that. Table names repeated in these big data dumbs, so database, then table extracts are key, but with SED and using less to view the file I was able to do in minutes what use to take me an hour to do.
    Thank you.

    [Reply]

Leave a Reply

Your email address will not be published. Required fields are marked *