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.