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.

  • Digg
  • del.icio.us
  • Facebook
  • Google Bookmarks
  • LinkedIn
  • MySpace
  • Reddit
  • Slashdot
  • Technorati
  • TwitThis

Tags: ,

Comments

6 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

Leave a Reply




  • Gary Pendergast, noun: Gamer, developer, MySQL, WordPress, web and music geek. Currently taking a leave of absence from life as a MySQL Support Engineer for Oracle to tour around Europe. Can be observed in his natural habit at pento.net, or usually in Bologna, Italy.

  • My Twitter