Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Monday, 12 November 2018

Restore one database from Single dump of multiple databases

Database backup and recovery is an important task for DBA's and every DBA's wants to work there task without any issue with minimum time.

I'm writing this article because of we had faced a sudden critical issue . Actually due to some mistake , one of our database had dropped by our team member and we have only single mysqldump which executed with --all-database . So I was trying to restore only dropped database from this dump. 

We had tried all possible ways like (by googling and referenced from many tutorials and websites) 

[root@localhost tmp]# mysql -u user -p -D dropdb < /tmp/alldb2.sql ###alldb2.sql my single dump said above

[root@localhost tmp]# mysql -u user -p --one-database dropdb < /tmp/alldb2.sql
etc...but all above ways are wrong . Either it could restore all db's with overwrite or getting error.

After all research and experiment , we had decided that if we have single dump of multiple databases then it could not be possible to restore only specific database from any direct one line command using command-line or any other tools.
But if any one has any other solution / answer so kindly reply us . @comment

To achieve the same please follow below steps.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| getmysqlgroup      |
| mysql              |
| mysqld1_7_224      |
| performance_schema |
| test      |
| test1              |
+--------------------+
7 rows in set (0.00 sec)

Now we are going to take full database dump..

[root@localhost tmp]# mysqldump -uuser -p'pass' --all-databases > /tmp/alldb2.sql

After taking full dump drop test&test1

mysql> drop database test;
mysql> drop database test1;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| getmysqlgroup      |
| mysql              |
| mysqld1_7_224      |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

Now we want to restore only "test" database from all-database dump(alldb2.sql).

>>Now we extract all the statement only for "test" database.

Follow the below steps one-by-one.

[root@localhost tmp]# grep -n 'Current Database' alldb2.sql
19:-- Current Database: `futuregroup`
132:-- Current Database: `mysql`
986:-- Current Database: `mysqld1_7_224`
1018:-- Current Database: `test`
1049:-- Current Database: `test1`


In above resultset,first column showing statement position , so we are going to pick start position of database "test" and start position of next database. It means that between the position 1018 and 1049 all statement exist only for database "test" and export it into new file (alldbtest.sql).

[root@localhost tmp]# sed -n '1018,1049 p' /tmp/alldb2.sql > /tmp/alldbtest.sql

After that restore this new one with only one database "test".

[root@localhost tmp]# mysql -uroot -p < alldbtest.sql
Enter password:
[root@localhost tmp]# mysql -uroot -p



mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| futuregroup        |
| mysql              |
| mysqld1_7_224      |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)




No comments:

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...