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. 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.