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.
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 .
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
0 comments:
Post a Comment