Skip to content

Extracting one Database From a full mysqldump File

Extracting one Database From a full mysqldump File

Normal system administrators make a full database dump as a safety guard against server or disk failure with the following command:

shell>mysqldump -e -u root –password=securepasswd –all-databases > fulldump.sql

The idea behind a full dump, when recovering from a crash it is easy to recover all databases in one go. You can restore all databases with a single command (watch out do not use this command on a live databaseserver it will overwrite all existing databases and tables):

shell> mysql -u root -p securepassword database_name < fulldump.sql

If you want to recover online ONE single database from a full dump file use the following command with the –one-database flag!

mysql> mysql -u root -p –one-database testdb < fullserverdump.sql

If you don’t want to restore the database, but you only want to extract the database testdb out of the full dump file you can do this with sed:

shell> sed -n ‘/^– Current Database: `test`/,/^– Current Database: `/p’ fulldump.sql > test.sql

if the above does not work (older mysql versions) try:

shell> sed -n ‘/^– Current Database: test/,/^– Current Database: /p’ fulldump.sql > test.sql

“You just need to change “testdb” to be the name of the database you want extracted.”

Rudolf Maduro – Life Coach and IT specialist

www.rudolfmaduro.com

Disclaimer: Test all commands on your sandbox system first. I assume no responsibility if you break your system using this information.

Published inMySQL

Be First to Comment

Leave a Reply

Your email address will not be published.