Restore Single MySQL Database from Full Backup

We’ve recently ran into a situation where a client accidentally deleted her database and only had a 10 GB mysqldump created with –all-databases.

As you can imagine, trying to grab a single table out of a 10 GB mysqldump backup doesn’t work so well, so instead we figured out a pretty nifty solution to this problem without affecting other sites on the server or having to (somehow) go through the pains of manually grabbing the single database from the backup.

First off, we use ‘grep’ to figure out the line locations of ‘Current Database’:

grep -n ‘Current Database:’ /backups/backup-2010-06-12_20:00:01.sql

This will return something similar to:

19:– Current Database: `db001`
1056:– Current Database: `db002`
5195:– Current Database: `db003`
8341:– Current Database: `db004`
10077:– Current Database: `db005`
16288:– Current Database: `db006`
17296:– Current Database: `db007`
20265:– Current Database: `db008`
29370:– Current Database: `db009`
30704:– Current Database: `db010`
36009:– Current Database: `db011`

….

From there, you can use ‘sed’ as follows to grab only the database you want.

In this example, we want db09:

sed -n 29370,30704p /backups/backup-2010-06-12_20:00:01.sql > db09.sql

This will of created a file containing only the content of lines 29370 to 30704, which is exactly what we needed. From this point, all we need to do is restore it normally via MySQL:

mysql -uUSER -pPASSWORD DATABASE < db09.sql

As a rule of thumb, NEVER do it under mysql’s root users (because we all make mistakes).

Hopefully this saves you some time the next time you only have a full database backup, but only need one database.

Post a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Blog is powered by WordPress and designed by Chico Web Design