In this post we will discuss about how to restore single database from a full backup dump taken using mysqldump.
I’m writing this post because as a MySQL DBA I have faced this scenario where we have a full backup file with more than 100 databases backup, and we are required to restore a single database from that dump.
Let’s say we have a backup taken with below command:
mysqldump -uuser -p --all-databases > fullbackup.sql
There are 2 ways to restore single database from full mysqldump:
- using –one-database, -0: We can restore a single database using the –one-database command, below is the syntax suppose we need to restore ‘jira’ database:
mysql -uuser -p --one-database jira < fullbackup.sql
Now what it does is it ignores all the statements when there is any other database name, when it sees the database ‘jira’ in the dump it will execute the successive statement, it works similar to ‘USE DATABASE’ and then execute all the DDL and DML statements from the dump file.
But it is not the safest method as it can also execute statement of another database in some situations, you can check the details about why here on mysql official documentation. Tough it is safe in most of the scenarios I’ve faced.
- Using sed command: This is the safest method and it is quite simple as well, you just need to extract the ‘jira’ database commands from the fullbackup.sql and that’s it, you can use the below command and replace the desired database name.
sed -n '/^-- Current Database: `jira`/,/^-- Current Database: `/p' fullbackup.sql > output.sqlnow your output.sql file can be restore simply using:
mysql -uuser -p < output.sql
These are few of the ways it can be done and what I’ll recommend is use the ‘sed’ method as it is really safe, and it will not touch any other database on your system while other method can modify other databases as well in few scenarios while restore single database from full dump file.
Tell me in comments if this helped you or do you have any other method to do this it would be really helpful. Thank you.