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.

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>

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 ~]#

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 ~]#

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 ~]#

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 ~]#

[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 ~]#

[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 ~]#

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 ~]#

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>