How to Restore Database from Single MySQL Database Backup

How to Restore Database from Single MySQL Database Backup?

Table of Contents
___________________________________________________________________________________________________

1. Environment
2. How to Backup a Single MySQL Database?
3. Transfer Backup files to Target server
4. How to Restore Database from Single MySQL Database Backup?
5. Verification
___________________________________________________________________________________________________


1. Environment

Source:

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

Target:

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


2. How to Backup a Single MySQL Database?

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 84988986 Mar  8 17:32 /u01/mysql/data/backup/db_backup_orcl_20210308.sql
[root@rac1 ~]#


3. Transfer Backup files to Target server

[root@rac1 ~]# ls -ltr /u01/mysql/data/backup/db_backup_orcl_*
-rw-r--r--. 1 root root 84988986 Mar  8 17:32 /u01/mysql/data/backup/db_backup_orcl_20210308.sql
[root@rac1 ~]# cd /u01/mysql/data/backup
[root@rac1 backup]# scp db_backup_orcl_20210308.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_20210308.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 84988986 Mar  8 17:41 db_backup_orcl_20210308.sql
[root@rac2 backup]#


4. How to Restore Database from Single MySQL Database 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_20210308.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_20210308.sql
[root@rac2 ~]#


5. Verification

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

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/

Leave a Reply

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