How to Backup Multiple MySQL Databases Archives

How To Backup MySQL Database Using mysqldump?

Table of Contents
___________________________________________________________________________________________________

0. Overview
1. How to Login MySQL With Out password?
2. How to Find Database Size?
3. How to Backup a Single MySQL Database?
4. How to Backup Multiple MySQL Databases?
5. How to Backup All MySQL Databases?
6. How to Backup All MySQL databases into separate Backup file for each database?
7. How to Create a Compressed MySQL Database Backup?
8. How to Run MySQL Database Backup in nohup?
9. How to Backup Tables on MySQL Database?
10. How to Backup Tables into separate backup file for each table?
11. How to Find Table Size in MySQL?
___________________________________________________________________________________________________

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?

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>

2. How to find Database Size?

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>

3. How to Backup a Single MySQL Database?

[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. How to Backup Multiple MySQL Databases?

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. How to Backup All MySQL Databases?

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

6. How to Backup All MySQL databases into separate Backup file for each database?

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. How to Create a Compressed MySQL Database Backup?

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. How to Run MySQL Database Backup in nohup?

[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. How to Tables Backup on Database?

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

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

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>

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 : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Reference:
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-option-summary

Bright DBA

ORACLE DBA – Tips and Techniques

Tag Archives: How to Backup Multiple MySQL Databases