Introduction

In this article, we would like to share you how to set MySQL Database Backup using Shell Script. In the shell script we are using Mutt (text-based command line mail client) which can send the status of backup job whether it is success/failure. As per requirement we can schedule the script using cronjob.

Prerequisites

• Ubuntu/CentOS
• MySQL 5.7

Step 1: Shell Script for MySQL DataBase Backup

In the below shell script, we are defining the env variables to login into mysql. After that it creates directory to store the databackup on daily basis. Finally it sends mails status on success/failure of backup.

#!/bin/bash



########################################################

## Shell Script to Shell Script for MySQL DataBase Backup by FOSSTechNix.com   

########################################################


DATE=`date +"%d%b%Y`
DB_PATH="/home/dump/"
MYSQL_HOST="localhost"
PORT="3306"
MYSQL_USER="adminfoss"
PASSWORD="fosstechnix"
DB="test"
RESULT="$?"

################Below command lets you create new directory and permissions#############

mkdir -p /home/dump/ && chmod -R 777 /home/dump/

############Below command is to take Dump from server and Remote VM also################

mysqldump -h${MYSQL_HOST} -P${PORT} -u${MYSQL_USER} -p${PASSWORD} ${DB} > ${DB_PATH}/${DB}_${DATE}.sql

################<$?-The exit status of the last command executed>#####################

if [ $RESULT -eq 0 ]; then
echo "DataBackup done" 
echo "DataBackup Success" | mutt -s "DB Backup status" [email protected]
else
echo "DataBackup failed"
echo "DataBackup Failure" | mutt -s "DB Backup status" [email protected]
fi

Before proceeding, note that there are two mechanical requirements to enable shell script execution.

• The script must be “executable”.
• The script must be “findable” for execution.

$ sudo chmod +x /home/databackup.sh

OR

$ sudo chmod 0755 /home/databackup.sh 

Step 2: Schedule cronjob

To set the cronjob follow below command and execute on linux terminal.

$ sudo su

# crontab -e

Add the below command in the editor to schedule MySQL backup using shell script daily at 1am.

 * 1 * * /home/dbbackup.sh 

Conclusion

In this article, using the shell sript for mysql database backup, we created backup directory and using MySQL credentials we took the databackup. To take the backup on daily basis, we scheduled cronjob at 1am. Using Mutt we sent mail notifications.

Related Articles:

Shell Script to Start MongoDB Service if Down [2 Steps]