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
12 responses to “Extracting a Database From a mysqldump File”
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:
and another gotcha comes when the “DROP TABLE” is not included in the dump (option –skip-add-drop-table)
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
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
Wonderful tips!
I needed to extract a db from a 30 MB dump .. did it manually copy/pasting and later found this article.
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.
[…] this blog] Tags: mysql Category: tech, unimportant | Comment (RSS) […]
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_
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.
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.