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.

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 ~]#

[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]#

[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 ~]#

[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>