Linux Shell Script for MySQL Database Backup

As a DBA, you must schedule the crontab script to take the backup of MySQL databases. It is important to configure backup of your MySQL databases either running on Linux or Windows so that you can recover your database in case of any problems occur.  MYSQLDUMP is the command using which you can take the backup of mysql databases.

You must check the backup and initiate the fresh backup as a safeguard before upgrading a MySQL database so that you can revert in case of any failure.

Shell Script to Configure Backup of MySQL Database

Backup MySQL Database using Linux Shell Script
[orahow@oradbdb DB_Backup]$ cat .backup_script.sh
#!/bin/bash
# Database credentials
user="root"
password="1Loginxx"
db_name="orahowdb"
v_cnt=0
logfile_path=/DB_Backup
touch "$logfile_path/orahowdb_backup.log"
# Other options
backup_path="/DB_Backup"
date=$(date +"%d-%b-%Y-%H-%M-%p")
# Set default file permissions
#umask 177
# Dump database into SQL file
mysqldump --user=$user --password=$password $db_name >$backup_path/$db_name-$date.sql

# Delete files older than 30 days
find $backup_path/* -mtime +15 -exec rm {} \;


#DB backup log
echo -e "$(date +'%d-%b-%y  %r '):ALERT:Database has been Backuped"    >>$logfile_path/orahowdb_backup.log
#confirmation mail to recepient
if [ "$?" -eq 0 ]
  then
   v_cnt=`expr $v_cnt+1`
mail -s "FYI : Orahow DB Backup Has Been Completed Successfully" -v -r support.alerts@mailserver.com recipient@gmail < /DB_Backup/orahowdb_backup.log
else
  mail -s "ALERT :  ORAHOW DB Backup Has Been Failed" -v -r support.alerts@mailserver.com recipient@support.com < /DB_Backup/orahowdb_backup.log
   exit
fi
rm /DB_Backup/orahowdb_backup.log
[orahow@orahowdb DB_Backup]$

You can copy and edit the above mysql backup script in vi editor and schedule it in the Linux crontab. Hope this will help you to protect you data in case of any issues.