If you run into a situation where you require a specific database from a full backup created with –all-databases.
As you can imagine, trying to grab a single table out of a mysqldump backup doesn’t work so well, so instead we figured out a pretty nifty solution to this problem.
First off, we use ‘grep’ to figure out the line locations of ‘Current Database’:
grep -n ‘Current Database:’ /backups/backup-full-backup-mysql.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.
