DBA Hub

📋Steps in this guide1/12

How To Backup MySQL Database Using mysqldump?

How To Backup MySQL Database Using mysqldump?

mysql backupintermediate
by MYSQL
13 views
1

Overview

How To Backup MySQL Database Using mysqldump? 0. Overview How to backup MySQL databases from the command line using the mysqldump utility. The backup files created by the mysqldump utility are basically a set of SQL statements that can be used to recreate the original database. The mysqldump command can also generate files in CSV and XML format. You can also use the mysqldump utility to transfer your MySQL database to another MySQL server. mysqldump does not dump the INFORMATION_SCHEMA database by default. 1. How to Login MySQL With Out password?

Code/Command (click line numbers to comment):

1
2
3
4
How to backup MySQL databases from the command line using the mysqldump utility.
The backup files created by the mysqldump utility are basically a set of SQL statements that can be used to recreate the original database. The mysqldump command can also generate files in CSV and XML format.
You can also use the mysqldump utility to transfer your MySQL database to another MySQL server.
mysqldump does not dump the INFORMATION_SCHEMA database by default.
2

Section 2

1. How to Login MySQL With Out password? Please add below lines to file /etc/my.cnf and then save and exit [client] user = root password = Mysql@123 cat /etc/my.cnf | egrep 'client]|user =|password =' 2. How to find Database Size?

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
Please add below lines to file /etc/my.cnf and then save and exit
[client]
user = root
password = Mysql@123
[root@rac1 ~]#
cat /etc/my.cnf | egrep 'client]|user =|password ='
[client]
user = root
password = Mysql@123
[root@rac1 ~]#

[root@rac1 ~]#
mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2809
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>
3

Section 3

--- For specific database SELECT table_schema AS "Database", -> ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" -> FROM information_schema.TABLES where table_schema='orcl' -> GROUP BY table_schema; 3. How to Backup a Single MySQL Database?

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
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 |
| orcl               |     66.64 |
+--------------------+-----------+
5 rows in set (0.01 sec)

mysql>
mysql>
--- For specific database
mysql>
SELECT table_schema AS "Database",
    -> ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
    -> FROM information_schema.TABLES where table_schema='orcl'
    -> GROUP BY table_schema;
+----------+-----------+
| Database | Size (MB) |
+----------+-----------+
| orcl     |     66.64 |
+----------+-----------+
1 row in set (0.00 sec)

mysql>

[root@rac1 ~]#
mysqldump -u root -p orcl > /u01/mysql/data/backup/database_orcl_bkp.sql
Enter password:
[root@rac1 ~]#

[root@rac1 ~]#
ls -lrth /u01/mysql/data/backup/database_orcl_bkp.sql
-rw-r--r--. 1 root root 76M Mar  8 00:34 /u01/mysql/data/backup/
database_orcl_bkp.sql
[root@rac1 ~]#
-- Create a Backup with Timestamp
mysqldump -u root -p orcl > /u01/mysql/data/backup/database_orcl_bkp_$(date +%Y%m%d).sql
[root@rac1 ~]#
ls -lrth /u01/mysql/data/backup/database_orcl_bkp_*
-rw-r--r--. 1 root root 81M Mar  8 00:39
/u01/mysql/data/backup/database_orcl_bkp_20210308.sql
[root@rac1 ~]#
4

Section 4

mysqldump -u root -p orcl > /u01/mysql/data/backup/database_orcl_bkp.sql -- Create a Backup with Timestamp mysqldump -u root -p orcl > /u01/mysql/data/backup/database_orcl_bkp_$(date +%Y%m%d).sql 4. How to Backup Multiple MySQL Databases? mysqldump -u root -p --databases database1 database2 > databases_1_2.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
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
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 |
| orcl               |     66.64 |
| hit                |      4.52 |
+--------------------+-----------+
6 rows in set (0.09 sec)

mysql>
mysqldump -u root -p --databases database1 database2 > databases_1_2.sql
[root@rac1 ~]#
mysqldump -u root -p --databases orcl hit > /u01/mysql/data/backup/databases_orcl_hit_bkp_$(date +%Y%m%d).sql
Enter password:
[root@rac1 ~]#

[root@rac1 ~]#
ls -ltr /u01/mysql/data/backup/databases_orcl_hit_bkp_*
-rw-r--r--. 1 root root 88757223 Mar  8 00:54
/u01/mysql/data/backup/databases_orcl_hit_bkp_20210308.sql
[root@rac1 ~]#
--Backup MySQL databases to separate files
for i in $(mysql -e 'show databases' -s --skip-column-names | egrep 'hit|orcl'); do
mysqldump $i > /u01/mysql/data/backup/database_backup_"$i"_$(date +%Y%m%d).sql
done
[root@rac1 ~]#
for i in $(mysql -e 'show databases' -s --skip-column-names | egrep 'hit|orcl'); do
>     mysqldump $i > /u01/mysql/data/backup/database_backup_"$i"_$(date +%Y%m%d).sql
> done
[root@rac1 ~]#

[root@rac1 ~]#
ls -lrth /u01/mysql/data/backup/database_backup_*.sql
-rw-r--r--. 1 root root 3.6M Mar  8 01:24 /u01/mysql/data/backup/
database_backup_
hit
_20210308.sql
-rw-r--r--. 1 root root  82M Mar  8 01:24 /u01/mysql/data/backup/
database_backup_
orcl
_20210308.sql
[root@rac1 ~]# date
Mon Mar  8 01:25:36 +08 2021
[root@rac1 ~]#
5

Section 5

mysqldump -u root -p --databases orcl hit > /u01/mysql/data/backup/databases_orcl_hit_bkp_$(date +%Y%m%d).sql --Backup MySQL databases to separate files for i in $(mysql -e 'show databases' -s --skip-column-names | egrep 'hit|orcl'); do mysqldump $i > /u01/mysql/data/backup/database_backup_"$i"_$(date +%Y%m%d).sql done hit orcl 5. How to Backup All MySQL Databases?
6

Section 6

mysqldump -u root -p --all-databases > all_databases.sql mysqldump -u root -p --all-databases > /u01/mysql/data/backup/databases_all_bkp_$(date +%Y%m%d).sql 6. How to Backup All MySQL databases into separate Backup file for each database?

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
mysqldump -u root -p --all-databases > all_databases.sql
[root@rac1 ~]#
mysqldump -u root -p --all-databases > /u01/mysql/data/backup/databases_all_bkp_$(date +%Y%m%d).sql
Enter password:
[root@rac1 ~]#
[root@rac1 ~]#
ls -lrth /u01/mysql/data/backup/databases_all_bkp_*
-rw-r--r--. 1 root root 86M Mar  8 00:57 /u01/mysql/data/backup/
databases_all_bkp_20210308.sql
[root@rac1 ~]#

for i in $(mysql -e 'show databases' -s --skip-column-names); do
mysqldump --single-transaction $i > /u01/mysql/data/backup/db_backup_"$i"_$(date +%Y%m%d).sql
done
[root@rac1 ~]#
for i in $(mysql -e 'show databases' -s --skip-column-names); do
> mysqldump --single-transaction $i > /u01/mysql/data/backup/db_backup_"$i"_$(date +%Y%m%d).sql
> done
mysqldump: Dumping 'information_schema' DB content is not supported
[root@rac1 ~]#
[root@rac1 ~]# date
Mon Mar  8 01:39:59 +08 2021
[root@rac1 ~]#
ls -lrth /u01/mysql/data/backup/db_backup_*
-rw-r--r--. 1 root root 3.6M Mar  8 01:39 /u01/mysql/data/backup/
db_backup_hit_20210308.sql
-rw-r--r--. 1 root root  791 Mar  8 01:39 /u01/mysql/data/backup/
db_backup_information_schema_20210308.sql
-rw-r--r--. 1 root root 1.1M Mar  8 01:39 /u01/mysql/data/backup/
db_backup_mysql_20210308.sql
-rw-r--r--. 1 root root  82M Mar  8 01:39 /u01/mysql/data/backup/
db_backup_orcl_20210308.sql
-rw-r--r--. 1 root root  40M Mar  8 01:39 /u01/mysql/data/backup/
db_backup_performance_schema_20210308.sql
-rw-r--r--. 1 root root 293K Mar  8 01:39 /u01/mysql/data/backup/
db_backup_sys_20210308.sql
[root@rac1 ~]#
7

Section 7

for i in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump --single-transaction $i > /u01/mysql/data/backup/db_backup_"$i"_$(date +%Y%m%d).sql done for i in $(mysql -e 'show databases' -s --skip-column-names); do > mysqldump --single-transaction $i > /u01/mysql/data/backup/db_backup_"$i"_$(date +%Y%m%d).sql > done mysqldump: Dumping 'information_schema' DB content is not supported 7. How to Create a Compressed MySQL Database Backup? mysqldump db_name | gzip > db_name.sql.gz

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
mysqldump db_name | gzip > db_name.sql.gz
[root@rac1 ~]#
mysqldump -u root -p orcl | gzip > /u01/mysql/data/backup/db_backup_orcl.sql.gz
Enter password:
[root@rac1 ~]#
[root@rac1 ~]#
ls -lrth /u01/mysql/data/backup/db_backup_orcl.sql.gz
-rw-r--r--. 1 root root 24M Mar  8 01:50 /u01/mysql/data/backup/
db_backup_orcl.sql.gz
[root@rac1 ~]#
8

Section 8

8. How to Run MySQL Database Backup in nohup? nohup mysqldump orcl | gzip > /u01/mysql/data/backup/db_backup_orcl$(date +%Y%m%d).sql.gz & db_backup_orcl20210308.sql.gz 9. How to Tables Backup on Database?

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
[root@rac1 ~]#
nohup mysqldump orcl | gzip > /u01/mysql/data/backup/db_backup_orcl$(date +%Y%m%d).sql.gz &
[1] 30969
[root@rac1 ~]# nohup: ignoring input and redirecting stderr to stdout

[root@rac1 ~]#
jobs -l
[1]+ 30968 Running                 nohup mysqldump orcl
     30969                       | gzip > /u01/mysql/data/backup/db_backup_orcl$(date +%Y%m%d).sql.gz &
[root@rac1 ~]#
[1]+  Done
nohup mysqldump orcl | gzip > /u01/mysql/data/backup/db_backup_orcl$(date +%Y%m%d).sql.gz
[root@rac1 ~]#
[root@rac1 ~]#
ls -lrth /u01/mysql/data/backup/db_backup_orcl*.gz
-rw-r--r--. 1 root root 24M Mar  8 01:54 /u01/mysql/data/backup/
db_backup_orcl20210308.sql.gz
[root@rac1 ~]# date
Mon Mar  8 01:54:56 +08 2021
[root@rac1 ~]#
9

Section 9

9. How to Tables Backup on Database? data test_data test_data2 mysqldump -u root -p orcl data test_data test_data2 > /u01/mysql/data/backup/db_orcl_tables_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
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
61
62
63
64
65
66
[root@rac1 ~]#
mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2810
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              |
| orcl               |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

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>
mysql>
show tables;
+----------------+
| Tables_in_orcl |
+----------------+
|
data
|
|
test_data
|
|
test_data2
|
+----------------+
3 rows in set (0.00 sec)

mysql>

[root@rac1 ~]#
mysqldump -u root -p orcl data test_data test_data2 > /u01/mysql/data/backup/db_orcl_tables_bkp_$(date +%Y%m%d).sql
Enter password:
[root@rac1 ~]#
[root@rac1 ~]# date
Mon Mar  8 02:13:48 +08 2021
[root@rac1 ~]#
[root@rac1 ~]#
ls -lrth /u01/mysql/data/backup/db_orcl_tables_bkp*
-rw-r--r--. 1 root root 82M Mar  8 02:13 /u01/mysql/data/backup/
db_orcl_tables_bkp_20210308.sql
[root@rac1 ~]#
10

Section 10

10. How to Backup Tables into separate backup file for each table? for i in $(mysql orcl -e 'show tables' -s --skip-column-names | egrep 'data|test_data' | grep -v 'test_data2'); do mysqldump orcl $i > /u01/mysql/data/backup/orcl_table_backup_"$i"_$(date +%Y%m%d).sql done orcl_table_backup_ data _20210308.sql orcl_table_backup_ test_data _20210308.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
for i in $(mysql orcl -e 'show tables' -s --skip-column-names | egrep 'data|test_data' | grep -v 'test_data2'); do
mysqldump orcl $i > /u01/mysql/data/backup/orcl_table_backup_"$i"_$(date +%Y%m%d).sql
done
[root@rac1 ~]#
for i in $(mysql orcl -e 'show tables' -s --skip-column-names | egrep 'data|test_data' | grep -v 'test_data2'); do
> mysqldump orcl $i > /u01/mysql/data/backup/orcl_table_backup_"$i"_$(date +%Y%m%d).sql
> done
[root@rac1 ~]#
[root@rac1 ~]#
ls -ltrh /u01/mysql/data/backup/orcl_table_backup_*
-rw-r--r--. 1 root root 7.3M Mar  8 02:27 /u01/mysql/data/backup/
orcl_table_backup_
data
_20210308.sql
-rw-r--r--. 1 root root  37M Mar  8 02:27 /u01/mysql/data/backup/
orcl_table_backup_
test_data
_20210308.sql
[root@rac1 ~]#
11

Section 11

11. How to Find Table Size in MySQL? SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "orcl" ORDER BY (data_length + index_length) DESC; "orcl" <---- DB Name is orcl +------------+-----------+ | Table | Size (MB) | +------------+-----------+ | test_data | 39.58 | | test_data2 | 18.55 | | data | 8.52 | +------------+-----------+

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
SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "orcl"
ORDER BY (data_length + index_length) DESC;
mysql>
SELECT table_name AS "Table",
    -> ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
    -> FROM information_schema.TABLES
    -> WHERE table_schema =
"orcl" <---- DB Name is
orcl
-> ORDER BY (data_length + index_length) DESC;
+------------+-----------+
| Table      | Size (MB) |
+------------+-----------+
| test_data  |     39.58 |
| test_data2 |     18.55 |
| data       |      8.52 |
+------------+-----------+
3 rows in set (0.00 sec)

mysql>
12

Section 12

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. Reference: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-option-summary Reference:

Comments (0)

Please to add comments

No comments yet. Be the first to comment!