How to Restore One Database From All Database mysqldump

In this tutorial we are going to learn how to restore single MySQL database from a mysql backup file, which contains multiple databases.

What if you backed up all databases in your MySQL/MariaDB Server using –all-databases option, and now you want to restore one database from the all database backup file?

The easiest way is to use -o or –one-database option of the mysql command when restoring the database.

mysql -o database_name < all_databases.sql

The database_name is the database you want to restore, all_databases.sql is the mysql backup file which contains all databases, or multiple databases.

Example : MySQL –one-database Restore One Database

For example, let’s say you backup the all databases in your MySQL/MariaDB Server using the mysqldump command.

mysqldump --all-databases > all_databases.sql

Now, you want to restore one database called dbname from the dump file.

mysql --one-database dbname < all_databases.sql

The above command will restore the database db1 from the all_databases.sql file.

Extract One database using sed Command

Another option is to extract the database you want to restore from the mysqldump file using sed command, Then restore the database using mysql command.

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

The db_name is the name of the database you want to extract, all_databases.sql is the mysql backup file which contains all databases and db.sql is the output file.

Example

In the Following example, I am going to extract a database called ‘database1’ from the large mysql dump file using the sed command.

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

This will extract database1 from the all_databases.sql dump and output it to the database1.sql file. Then, I can restore the database1 using the mysql command.

mysql database1 < database1.sql

The database1 will restore using the database1.sql backup file.

That’s all.

By anup

Leave a Reply

Your email address will not be published. Required fields are marked *