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?
___________________________________________________________________________________________________
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>
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 : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/
Reference:
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-option-summary