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

Running SQL queries on a database with phpMyAdmin

This tutorial is to teach you how to run SQL queries on a database with phpMyAdmin. We will...

Managing MySQL Databases With PHPMyAdmin

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

Deleting tables in a database with phpMyAdmin

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

Creating tables in a database with phpMyAdmin

This tutorial is going to teach you how to create a table in a database with phpMyAdmin. We are...

Exporting databases and tables with phpMyAdmin

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