Restore Single MySQL Database from Full Backup

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.

  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Managing MySQL Databases With PHPMyAdmin

Let's learn how to Manage MySQL Databases with phpMyAdmin. This tutorial will assume that you...

How To Create A MySQL Database

Let's learn how to create a MySQL Database. MySQL Databases allow you to store lots of...

Importing databases and tables with phpMyAdmin

This tutorial is to teach you how to import databases and tables with phpMyAdmin. We will assume...

Inserting fields into database tables with phpMyAdmin

This tutorial is going to teach about the program phpMyAdmin, and how to Insert fields into...

Deleting tables in a database with phpMyAdmin

This tutorial is to teach you about specific operations in phpMyAdmin. We are going to learn how...