How to Extract Database from MySQL dump file

MySQL is a popular database system that allows you to easily backup one or more databases using MySQLdump tool and restore them using MySQL command. Let us say you have backed up all your databases in a single file, using –all-databases option, in MySQLdump utility, and now want to restore single database from it. In this article, we will learn how to extract database from MySQL dump file.

How to Extract Database from MySQL dump file

Let us say you have created a single .sql dump file consisting of all databases (e.g. db1, db2, db3) on your MySQL/MariaDB system.

$ mysqldump -p --all-databases > all_databases.sql

Now there are two ways to restore or import single database from this file. Either you can directly import the database from .sql file to a MySQL database, or you can extract a part of the single .sql dump file for all databases, to create a separate .sql file just for the single database. We will look at both these approaches.

1. Using MySQL command

In this case, we directly load the database of our choice from the .sql dump file which contains information about all databases, into MySQL, resulting in an actual database, instead of another dump file. For this purpose, we use the –one-database option in MySQL command.

Here is the command to restore database db1 from all_databases.sql file.

$ mysql -p --one-database db1 < all_databases.sql

The –one-database option allows you to import a single database from a dump file that contains multiple databases.

Now if you log into MySQL, you will find your database db1 present in it.

2. Using sed

A dump file is nothing but a set of SQL queries to create, populate and configure the databases. So you can also use a text editor like sed to extract the specific queries pertaining to a single database, and save it to another .sql dump file. Thereafter, you can use the new .sql file to restore your database.

Here is the command to extract queries about single database from all_databases.sql file and store them in db.sql file. Replace db_name with the database that you want to extract, db.sql with the filename of sql dump for this database, and all_databases.sql with the dump file of all databases.

$ sed -n '/^-- Current Database: `db_name`/,/^-- Current Database: `/p' all_databases.sql > db.sql

A dump file consists of separate sections of text for each database. Each section of text consists of SQL queries required to create, populate & configure that database. These SQL queries pertaining to each database are enclosed within the text “Current Database: <database_name>…– Current Database:”.

So we will instruct sed to extract the SQL queries that are present between the “Current Database: … — Current Database” text for desired database.

For example, if you want to extract database db1 from all_databases.sql file and store it in db1.sql file, then run the following command.

sed -n '/^-- Current Database: `db1`/,/^-- Current Database: `/p' all_databases.sql > db1.sql

Once you have the db1.sql file created for db1 database, you can use MySQL command to restore that database.

$ mysql -p db1 < db1.sql

In this article, we have learnt a couple of ways to restore single database from MySQL dump file.

How to Extract Database from MySQL dump file