Backup all MySQL Databases with a MySQL Backup Script

In this article, we will create a simple MySQL backup script for taking backups of all your MySQL databases periodically.

If your website or web app runs over a LAMP stack or you’re running a Linux OS whether it is Ubuntu or Centos, you can write a simple script for taking MySQL backups at a given time.

Why we use MySQL Backup Script

If you are having a GUI like PHPMyAdmin then you can also take backup manually from the browser, which we will discuss in another blog post. But if you want to automate the backup process then you should create a Bash script and set a cronjob for this.

So let’s have a look at our MySQL backup script :

#!/bin/bash
#
####################################################################
##	 Shell script to backup all MySql database with single User
##   
##	 MySQL Database Backup Script 
##   Written By: Amol Jhod
##   URL: https://www.itsupportwale.com/blog/learn-how-to-backup-up-all-mysql-databases-using-a-single-user-with-a-simple-bash-script
##   Last Update: Apr 25, 2019
##     
##   For more scripts please visit : www.itsupportwale.com
## 
#####################################################################
#####################################################################
#### Caution : This script is takes backup of all databases #########
#############   on which the given user is having access. ###########
############## And Delete the backups older then BKP_DAYS ##########
#####################################################################
#####################################################################
########### You Have to Update the Below Values #####################
#####################################################################
#
#
BKP_USER="myuser"     # Enter the username for backup
BKP_PASS="mypassword"       	# Enter the password of the backup user 
#
BKP_DEST="/backup"			# Enter the Backup directory,change this if you have someother location
#
## Note: Scripts will delete all backup which are older then BKP_DAYS##
#
BKP_DAYS="2"				# Enter how many days backup you want,
#
########### Use This for only local server #############################
MYSQL_HOST="localhost"  
#
#
########################################################################
########### Thats Enough!! NO NEED TO CHANGE THE BELOW VALUES ##########
########################################################################
#
##################### Get Backup DATE ##################################
#
BKP_DATE="$(date +"%d-%m-%Y-%H:%M:%S-%a")";
#
########## Ignore these default databases shen taking backup ############
#
IGNORE_DB="information_schema mysql performance_schema"
#
########## Creating backup dir if not exist #############################
#
[ ! -d $BKP_DEST ] && mkdir -p $BKP_DEST || :
#
################# Autodetect the linux bin path #########################
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
#
###################### Get database list ################################
#
DB_LIST="$($MYSQL -u $BKP_USER -h $MYSQL_HOST -p$BKP_PASS -Bse 'show databases')"
#
for db in $DB_LIST
do
    skipdb=-1
    if [ "$IGNORE_DB" != "" ];
    then
	for i in $IGNORE_DB
	do
	    [ "$db" == "$i" ] && skipdb=1 || :
	done
    fi
 
    if [ "$skipdb" == "-1" ] ; then
	BKP_FILENAME="$BKP_DEST/$db.$BKP_DATE.gz"
#
################ Using MYSQLDUMP for bakup and Gzip for compression ###################
#
        $MYSQLDUMP -u $BKP_USER -h $MYSQL_HOST -p$BKP_PASS $db | $GZIP -9 > $BKP_FILENAME
    fi
done
#########To delete all backup files older then BKP_DAYS #################
#
find $BKP_DEST -type f -mtime +$BKP_DAYS -delete
#
#
#### End of script ####

Now lets, discuss the various sections of the script.

BKP_USER="myuser"
BKP_PASS="mypassword"

At the very first section, you have to fill the details of your user. It is recommended that you have to create a separate user for taking backups. On that user give permissions to only backup database.

You can learn : “How to create user for only backup MySQL database“

On the next section, You have to give the name of the backup folder. Where you want to store the MySQL backups.

BKP_DEST="/backup"

That’s it. No need to do more than that. You just have to run the command manually or set a cronjob for it.

For example, if you want daily database backup, then put this line in your crontab config :

@daily /path_to_script/my_backup_script.sh

Summary

MySQL is a widely used Database software. Almost every system administrator should know how to take a backup of MySQL databases using commands. We discussed and present a simple backup script in this article for that. You guys can share your views in the comment section. Happy Learning ..!!

Also Read: “Execute SSH Commands on Multiple Systems Using Single Command“