Darren Mothersele

Software Developer

Warning: You are viewing old, legacy content. Kept for posterity. Information is out of date. Code samples probably don't work. My opinions have probably changed. Browse at your own risk.

Restoring a single database from a complete MySQL database dump

Jan 10, 2012

web-dev

I had a collection of database dumps from a server that had been created using MySQL's --all-databases option. This contained the databases for several Drupal websites, but I wanted to restore the Drupal database for just one of the sites. After a bit of Googling I came across two simple solutions:

The first option is to pass the whole SQL dump to MySQL command line, but restrict it's operation using the --one-database option.

mysql -u root -p --one-database destdbname < alldatabases.sql

In the above code substitute destdbname with the database name you want to restore, and alldatabases.sql with the name of your full DB backup.

If you would rather just extract the database dump of the single database from the --all-databases dump file, you can do this with sed using this command:

sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' alldatabases.sql > output.sql

Where dbname is replaced with the database name of the database to extract, and alldatabases.sql is the name of your dump file. The result will be saved into the file output.sql.