DBA Hub

📋Steps in this guide1/7

How to Restore Database from Single MySQL Database Backup?

How to Restore Database from Single MySQL Database Backup?

mysql backupintermediate
by MYSQL
14 views
1

Overview

How to Restore Database from Single MySQL Database Backup? 1. Environment Source: Target:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
Source:
Hostname
: rac1
Database Name	        : orcl
Database Size	        : 67 MB
Target:
Hostname
: rac2
Database Name	        : orcl
Free space on mysql data dir : 10GB
2

Section 2

2. How to Backup a Single MySQL Database? use orcl; | data | | test_data | | test_data2 | mysqldump orcl > /u01/mysql/data/backup/db_backup_orcl_$(date +%Y%m%d).sql

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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

Section 3

db_backup_orcl_20210308.sql 3. Transfer Backup files to Target server scp db_backup_orcl_20210308.sql rac2:/u01/mysql/data/orcl/backup root@rac2's password: db_backup_orcl_20210308.sql 100%

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[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

Section 4

rac2 db_backup_orcl_20210308.sql 4. How to Restore Database from Single MySQL Database Backup? mysql

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
[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

Section 5

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; mysql orcl < /u01/mysql/data/orcl/backup/db_backup_orcl_20210308.sql ERROR 1049 (42000): Unknown database 'orcl' <---- -- Create Database before restore create database orcl; <---- Created manually
6

Section 6

-- Restore Database mysql orcl < /u01/mysql/data/orcl/backup/db_backup_orcl_20210308.sql 5. Verification orcl

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
[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>
7

Section 7

<-------- Restored. use orcl; show tables; 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. 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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!