Shell Script for MySQL DataBase Backup

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

Shell Script

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

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 

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

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

We promise not to spam you, and we don't usually send more than one email a week.

 

You have Successfully Subscribed!

0 Shares
Share via
Copy link