How to Restore A Single Database From A Backup File Which Contains Multiple Databases

How to Restore A Single Database From A Backup File Which Contains Multiple Databases?

Table of Contents
___________________________________________________________________________________________________

1. Environment
2. How to Backup All MySQL Databases?
3. Transfer Backup files to Target server
4. How to Restore A Single Database From A Backup File Which Contains Multiple Databases?
5. Verification
___________________________________________________________________________________________________


1. Environment

Goal: Want to Restore A Single Database From A Backup File Which Contains Multiple Databases

You backed up all your databases using the -all-databases option and you want to restore a single database from a backup file which contains multiple databases use the --one-database option.

Source:

Hostname		: rac1
Database Name	        : orcl,hit and all internal databases (Backup file contains all databases)


Target:

Hostname		: rac2
Database Name	        : hit  <---- Need to restore Database "hit" from RAC1 databases's backup.



2. How to Backup All MySQL Databases?

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hit                |
| information_schema |
| mysql              |
| orcl               |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql>

[root@rac1 ~]# mysqldump --all-databases > /u01/mysql/data/backup/db_all_bkp_$(date +%Y%m%d).sql
[root@rac1 ~]#
[root@rac1 ~]# ls -ltr /u01/mysql/data/backup/db_all_bkp_*
-rw-r--r--. 1 root root 89902589 Mar  8 22:12 /u01/mysql/data/backup/db_all_bkp_20210308.sql
[root@rac1 ~]#


3. Transfer Backup files to Target server

[root@rac1 ~]# cd /u01/mysql/data/backup
[root@rac1 backup]# scp db_all_bkp_20210308.sql rac2:/u01/mysql/data
root@rac2's password:
db_all_bkp_20210308.sql                100%   86MB  48.5MB/s   00:01
[root@rac1 backup]#

[root@rac2 ~]# cd /u01/mysql/data
[root@rac2 data]# ls -ltr db_all_bkp_20210308.sql
-rw-r--r--. 1 root root 89902589 Mar  8 22:14 db_all_bkp_20210308.sql
[root@rac2 data]#


4. How to Restore A Single Database From A Backup File Which Contains Multiple Databases?

[root@rac2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

[root@rac2 ~]# mysql --one-database hit < /u01/mysql/data/db_all_bkp_20210308.sql 
ERROR 1049 (42000): Unknown database 'hit' <--- Need to create DB first.
[root@rac2 ~]# 
[root@rac2 ~]# mysql -e "create database hit"; 
[root@rac2 ~]# 
[root@rac2 ~]# mysql 
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hit                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>
-- Restore A database using --one-database option 
[root@rac2 ~]# mysql --one-database hit < /u01/mysql/data/db_all_bkp_20210308.sql
[root@rac2 ~]#


5. Verification

[root@rac2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT table_schema AS "Database",
    -> ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
    -> FROM information_schema.TABLES
    -> GROUP BY table_schema;
+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| mysql              |      2.48 |
| information_schema |      0.00 |
| performance_schema |      0.00 |
| sys                |      0.02 |
| hit                |      0.02 |  <--- DB restored
+--------------------+-----------+
5 rows in set (0.02 sec)

mysql> use hit;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_hit |
+---------------+
| hit_test      |  <-- We can able see tables inside DB "hit"
+---------------+
1 row in set (0.01 sec)

mysql>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

1 thought on “How to Restore A Single Database From A Backup File Which Contains Multiple Databases

Leave a Reply

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