DBA Hub

📋Steps in this guide1/7

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?

mysql backupintermediate
by MYSQL
14 views
1

Overview

How to Restore A Single Database From A Backup File Which Contains Multiple Databases? 1. Environment Goal: Want to Restore A Single Database From A Backup File Which Contains Multiple Databases

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
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

Section 2

Source: Target: 2. How to Backup All MySQL Databases? mysqldump --all-databases > /u01/mysql/data/backup/db_all_bkp_$(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
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

Section 3

db_all_bkp_20210308.sql 3. Transfer Backup files to Target server scp db_all_bkp_20210308.sql rac2:/u01/mysql/data 4. How to Restore A Single Database From A Backup File Which Contains Multiple Databases?

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[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

Section 4

4. How to Restore A Single Database From A Backup File Which Contains Multiple Databases? show databases; mysql --one-database hit < /u01/mysql/data/db_all_bkp_20210308.sql ERROR 1049 (42000): Unknown database 'hit' <--- Need to create DB first. mysql -e "create database hit";

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

Section 5

show databases; --one-database option mysql --one-database hit < /u01/mysql/data/db_all_bkp_20210308.sql 5. Verification

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

Section 6

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; 0.02 | <--- DB restored use hit; show tables; hit_test -- We can able see tables inside DB "hit"
7

Section 7

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!