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.
Comments
6 Responses to “Extracting a Database From a mysqldump File”
Leave a Reply



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.
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
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:
and another gotcha comes when the “DROP TABLE” is not included in the dump (option –skip-add-drop-table)
Thanks for the great tips, a big help!
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