How to Restore Database from Single MySQL Database Backup

Source:

Hostname		: rac1
Database Name	        : orcl
Database Size	        : 67 MB

Target:

Hostname		: rac2
Database Name	        : orcl
Free space on mysql data dir : 10GB

mysql> use orcl;
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_orcl |
+----------------+
| data           |
| test_data      |
| test_data2     |
+----------------+
3 rows in set (0.00 sec)

mysql>

[root@rac1 ~]# mysqldump orcl > /u01/mysql/data/backup/db_backup_orcl_$(date +%Y%m%d).sql
[root@rac1 ~]#
[root@rac1 ~]# ls -ltr /u01/mysql/data/backup/db_backup_orcl_*
-rw-r--r--. 1 root root  Mar  8 17:32 /u01/mysql/data/backup/db_backup_orcl_.sql
[root@rac1 ~]#

[root@rac1 ~]# ls -ltr /u01/mysql/data/backup/db_backup_orcl_*
-rw-r--r--. 1 root root  Mar  8 17:32 /u01/mysql/data/backup/db_backup_orcl_.sql
[root@rac1 ~]# cd /u01/mysql/data/backup
[root@rac1 backup]# scp db_backup_orcl_.sql rac2:/u01/mysql/data/orcl/backup
The authenticity of host 'rac2 (192.168.2.102)' can't be established.
ECDSA key fingerprint is SHA256:Jn2hrEtLe/2u2u0ZiOm0EbeniTbP3NEPx2r8Kxqsi0k.
ECDSA key fingerprint is MD5:88:4b:43:a5:d6:08:b1:79:8d:1d:69:63:0e:a9:6f:91.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'rac2,192.168.2.102' (ECDSA) to the list of known hosts.
root@rac2's password:
db_backup_orcl_.sql                                         100%   81MB  48.2MB/s   00:01
[root@rac1 backup]#

[root@rac2 ~]# cd /u01/mysql/data/orcl/backup
[root@rac2 backup]# ls -ltr
total 83000
-rw-r--r--. 1 root root  Mar  8 17:41 db_backup_orcl_.sql
[root@rac2 backup]#

[root@rac2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
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) |
+--------------------+-----------+
| information_schema |      0.00 |
| mysql              |      2.48 |
| performance_schema |      0.00 |
| sys                |      0.02 |
+--------------------+-----------+
4 rows in set (0.19 sec)  <-----

mysql>

[root@rac2 ~]# mysql orcl < /u01/mysql/data/orcl/backup/db_backup_orcl_.sql 
ERROR 1049 (42000): Unknown database 'orcl' <----
[root@rac2 ~]# 

-- Create Database before restore
mysql> create database orcl;
Query OK, 1 row affected (0.00 sec)

mysql>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| orcl               | <---- Created manually 
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

-- Restore Database 
[root@rac2 ~]# mysql orcl < /u01/mysql/data/orcl/backup/db_backup_orcl_.sql
[root@rac2 ~]#

[root@rac2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
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) |
+--------------------+-----------+
| information_schema |      0.00 |
| mysql              |      2.48 |
| orcl               |     91.67 |  <-------- Restored.
| performance_schema |      0.00 |
| sys                |      0.02 |
+--------------------+-----------+
5 rows in set (0.02 sec)

mysql>

mysql> use orcl;
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_orcl |
+----------------+
| data           |
| test_data      |
| test_data2     |
+----------------+
3 rows in set (0.00 sec)

mysql>