{"id":2507,"date":"2019-04-09T06:11:17","date_gmt":"2019-04-09T06:11:17","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2507"},"modified":"2024-01-27T17:28:26","modified_gmt":"2024-01-27T17:28:26","slug":"mysql-database-backup-shell-script-with-status-email","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/mysql-database-backup-shell-script-with-status-email","title":{"rendered":"MySQL backup shell script with status email"},"content":{"rendered":"\n

This post is for the backup script for MySQL database on Linux with mail. It’s a linux shell script for taking logical backup using mysqldump and sending status email.<\/p>\n\n\n\n

The backup shell script works as follows:
– The script takes backup using mysqldump and compresses it.
– Upon success, it will attempt to ship the backup to specified offsite location.
– Upon detecting failure in any of the above step, it will send out failure email.
– Upon overall success, it will sendout success email with execution time and present backups list.<\/p>\n\n\n\n

You might be looking for setting up physical backups for mysql using Holland backup framework<\/a>.<\/em><\/p>\n\n\n\n

Following is the code for MySQL backup shell script using mysqldump with status email.<\/p>\n\n\n\n\n\n\n\n

\n#\/bin\/bash\n## Script: \n# Backup script to take mysqldump\n# Retain 7 days of backup\n# Move backup to offsite\n# Send success\/failure email\n## http:\/\/kedar.nitty-witty.com\n## Ver.1 2018-01-20\n#  set up all the mysqldump variables\nFILE=database.sql.`date +\"%Y%m%d\"`.gz\nDBSERVER=127.0.0.1\nREMOTE_SERVER=127.0.0.1 # move database to offsite for backup\nDATABASE=dbname\nDBUSER=backup_user\nPASS=s3cr3tp@ssw0rd\n\nSRC_DIR=\/home\/admin\/script\nREMOTE_BACKUP_DIR=\/root\/mysql\/backup\nNOW=`date \"+%Y%m%d-%H%M\"`\nemail_list=\"kedar@nitty-witty.com\"\nsuccess_email=\"kedar+success@nitty-witty.com\"\nfailure_email=\"kedar+failure@nitty-witty.com\";\nSTART=$(date +%s)\n\n# function to send backup status email\nsendEmail() {\n        scripttime=0;\n        END=$(date +%s)\n        DIFF=$(( $END - $START ))\n        if [ $DIFF -le 60 ]; then\n                scripttime=\"$DIFF seconds.\";\n        else\n                DIFF=$(( $DIFF \/ 60 ))\n                scripttime=\"$DIFF minutes.\";\n        fi;\n        content=\"$content. Log: Backup duration: $scripttime\"\n        echo $content  | mail -s \"$subject\"  $email_list\n        exit;\n}\n\n# pipeline will return failure code if the mysqldump command fails\nset -o pipefail\n# Taking backup of all databases\nmysqldump --opt --user=${DBUSER} --password=${PASS} --no-data --single-transaction --all-databases | gzip > ${SRC_DIR}\/${FILE} 2>\/dev\/null\n\n# Verify backup is success\/failure\nRESULT=$?\nif [ $RESULT -ne 0 ]; then\n        subject=\"Backup-FAILURE\";\n        content=\"Backup appears to have been failed for $NOW. The mysqldump command returned failure status. Please login to $DBSERVER and check the status.\"\n        email_list=$failure_email\n        echo \"[`date`]Backup failure.\"\n        sendEmail\nfi\n\n# Transfer to remote host\nscp ${SRC_DIR}\/${FILE} root@${REMOTE_SERVER}:${REMOTE_BACKUP_DIR} 2>\/dev\/null\nRESULT=$?\nif [ $RESULT -ne 0 ]; then\n        subject=\"Backup-FAILURE\";\n        content=\"Backup appears to have been completed for $NOW. But SCP to remote server failed.\"\n        email_list=$failure_email\n        echo \"[`date`]SCP failure.\"\n        sendEmail\nfi\n\n# Delete 7 days old file from remote host\nBACKUP_FILE=${REMOTE_BACKUP_DIR}\/database.sql.`date -d\"-7 days\" +\"%Y%m%d\"`.gz 2>\/dev\/null\n# in case you run this more than once a day, remove the previous version of the file\necho \"[`date`] Removing backup file: $BACKUP_FILE from ${REMOTE_SERVER}.\"\nssh root@${REMOTE_SERVER} \"rm $BACKUP_FILE\" 2>\/dev\/null\n\n# Keep only last 7 days worth backup on database server.\nfind $SRC_DIR -mtime +7 -name '*.gz' -exec rm {} \\;\n\n# Finally send successful backup completion email.\nsubject=\"Backup-SUCCESS\"\ncontent=\"The backup file is on database server $SRC_DIR\/$FILE. Remote location is Backup ${REMOTE_SERVER}:$REMOTE_BACKUP_DIR is successful.\"\ncontent=$content.`ls -lhtr $SRC_DIR\/ | awk '{print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,\"\\r\"}'`\nemail_list=$success_email;\necho \"[`date`]Backup Success.\"\nsendEmail\nexit 0;\n<\/code><\/pre>\n\n\n\n
You may also choose to fork it on my Github repo.<\/p>\n","protected":false},"excerpt":{"rendered":"This post is for the backup script for MySQL database on Linux with mail. It’s a linux shell script for taking logical backup using mysqldump and sending status email. The…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,870,377,6],"tags":[473,475,474,76,476,477],"class_list":["post-2507","post","type-post","status-publish","format-standard","category-mysql","category-mysql-tools","category-mysql-articles","category-technical","tag-backup-script-with-status-email","tag-mysql-backup","tag-mysql-backup-script-and-send-email","tag-mysqldump","tag-mysqldump-shell-script","tag-shell-script-send-email"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t