MySQL

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.

Standard

10 thoughts on “Extracting a Database From a mysqldump File

  1. krteQ says:

    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.

    • 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
  2. 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.

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

  4. LC says:

    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_

Leave a Reply