Extracting a Database From a mysqldump File

April 16, 2009 · Posted in MySQL 

Restoring a single database from a full dump is pretty easy, using the mysql command line client’s --one-database option:

mysql> mysql -u root -p --one-database db_to_restore < fulldump.sql

But what if you don’t want to restore the database, you just want to extract it out of the dump file? Well, that happens to be easy as well, thanks to the magic of sed:

shell> sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql > test.sql

You just need to change “test” to be the name of the database you want extracted.

Tags: ,

Comments

8 Responses to “Extracting a Database From a mysqldump File”

  1. krteQ on April 16th, 2009 11:23 am

    Hi, I have tested analogic approach to extract a single table:

    sed -n ‘/^CREATE TABLE `MyTable`/,/^DROP TABLE /p’ test.sql

    These tasks can also be done using awk easily, but I haven’t tested the performance difference.

  2. Scott on April 16th, 2009 12:42 pm

    I’m not a fan of the –one-database parameter; it seems seriously deficient in its current implementation.

    http://bugs.mysql.com/bug.php?id=40477

  3. Gary Pendergast on April 16th, 2009 12:59 pm

    Yes, that’ll work for the file produced, though may not work on the full dump if you have two tables of the same name in different database. You could also include them on one line:

    shell> sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql | sed -n '/^CREATE TABLE `MyTable`/,/^DROP TABLE /p' > test.MyTable.sql
  4. krteQ on April 16th, 2009 3:19 pm

    and another gotcha comes when the “DROP TABLE” is not included in the dump (option –skip-add-drop-table)

  5. Joey on December 6th, 2009 3:38 pm

    Thanks for the great tips, a big help!

  6. kedar on February 25th, 2010 12:23 pm

    yes… here is the automated script for tables as well as database:
    http://kedar.nitty-witty.com/blog/mydumpsplitter-extract-tables-from-mysql-dump-shell-script/comment-page-1/#comment-184

  7. Farhad Khan on October 1st, 2010 3:22 pm

    Wonderful tips!
    I needed to extract a db from a 30 MB dump .. did it manually copy/pasting and later found this article.

  8. Jeremy Heslop on February 9th, 2011 10:57 pm

    This was a great solution for what I needed. I had a huge mysqldump as a .tar.gz file. I did not want to expand the full 9GB sql file so I piped gunzip out like so:

    gunzip -c mysqldump-201102050200.sql.gz | sed -n ‘/^– Current Database: `test`/,/^– Current Database: `/p’ > test.sql

    Thanks for the info to get me started.

Leave a Reply