Extracting a Database From a mysqldump File

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.

12 comments

  1. 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.

    1. 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

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

  6. 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.

  7. Pingback: .pQd’s log » Blog Archive » extracting single database from a [large] mysqldump

  8. I did this and got
    ERROR 1217 (23000) at line xxx: Cannot delete or update a parent row: a foreign key constraint fails

    So you need to disable foreign key checks by adding sql variables to the sql files

    — Add at the top of the sql import file / Disable check
    SET @BACKUP_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
    SET @@FOREIGN_KEY_CHECKS=0;

    — add to the bottom of the sql import file / Re-enable check
    SET @@FOREIGN_KEY_CHECKS=@BACKUP_FOREIGN_

  9. Very useful thanks. Up until the time I found this post I’d always just edited the mysqldump file in vi(m) and deleted all lines before and after it. Very time-consuming if your mysqldump file is 3.1GB.

  10. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘mysql -u admin -p –one-database wordpresstsv < /home/leif/20151215_web_my.sql' at line 1

    Am I blind or is there no syntax error or reserved word? I've tried back-ticking every possible combination. I'm totally new at this so I must be doing something really stupid.

Comments are closed.