Mysql Database Backup In Linux

Taking backup of mysql databases is the most common requirement of the applications.Same requirement came to my project.which needs to take backup of mysql database and send status via mail.This script only be used for small size databases. It is useless for big size databases.

Requirement to run below script :

1)Check sendmail is installed and running using command : service sendmail status.
2)mutt is installed or not :This is used to send mail with attachment.Note if you want to send mail with attachment comment mail command and uncomment mutt command in the below script .

To use the following script replace text with yellow background with following :

1) DATBASE_USERNAME    :Give your database user name
2) USER_PASSWORD           :Give your database user password
3) DATABASE_SERVER_IP  :IP of server where database is located (most of the cases it is localhost)
4) DATABASE_NAME           :Give your database name
5) COMMA_SEPARATED_EMAILID : Give the list of comma separated email id.


Change Backup file paths and log paths as per your requirement.

   Script:

#File Name: DbBackupScript
#
#Created for taking backup of the  application database
#Script will run mysql dump command and will save the content under
#Backup location : /var/mybackups/
#If any error occurse firing mysqldump command, error log will be
#generated under #/var/mybackups/mysqlDumperr.log
#
#After every backup, mail will be send to user defined in EMAILTO variable
#
#Created by user
#
#appended extension to backup file as .e.g backup_wed.sql,backup_mon.sql
TODAY=$(date +"%a")

#these variables are used for holding the backup status string which used while
#sending mail
backupStatus=''

#following command will take backup of database
mysqldump -uDATBASE_USERNAME -pUSER_PASSWORD -h DATABASE_SERVER_IP   --log-error=/var/mybackups/backuplogs/mysqlDumperr.log   --result-file=/var/mybackups/backup_$TODAY.sql   DATABASE_NAME

#check wehter mysqldump command is successfull or not
if [ $? -eq 0 ]; then
# echo "backup mysqldump Command executed successfully.Exit Status $? "
backupStatus="backup mysqldump Command executed successfully"
else
# echo "backup mysqldump Command executon failed  with exit status $?.Please check logs."
backupStatus="backup mysqldump Command executon failed check with exit status $?.Please check logs at /var/mybackups/backuplogs/mysqlDumperr.log."
fi

#Check the size of backup directory
BACKUP_DIR_SIZE=`du -h  /var/mybackups/`

#start mail script

tempBody=tempBody.txt

SUBJECT="$backupStatus  on date :$(date)"
EMAILTO="COMMA_SEPARATED_EMAILID"
MAILBODY="Database backup satus :$backupStatus \n\n All backup directory size is :$BACKUP_DIR_SIZE"
#ATTACHMENT='/var/mybackups/backuplogs/mysqlDumperr.log'

#write a mail body to tempBody.txt
echo -e "$MAILBODY">>$tempBody

#give a tempBody.txt as a mail body to mutt command
#use below this if want to send mail with attachment and mutt is installed
#mutt -s "$SUBJECT" -a "$ATTACHMENT" $EMAILTO < $tempBody

#send status mail
mailx -s "$SUBJECT"  $EMAILTO < $tempBody


#clean temp files
/bin/rm $tempBody

#end of mail script

*Note: Also change the script as per your requirement

Also check below links :

Basic command which needs every day

How to send mail with attachment