backups for mysql using Holland backup framework<\/a>.<\/em><\/p>\n\n\n\nFollowing 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\nYou 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":{"0":"post-2507","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-tools","8":"category-mysql-articles","9":"category-technical","10":"tag-backup-script-with-status-email","11":"tag-mysql-backup","12":"tag-mysql-backup-script-and-send-email","13":"tag-mysqldump","14":"tag-mysqldump-shell-script","15":"tag-shell-script-send-email"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2507","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2507"}],"version-history":[{"count":8,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2507\/revisions"}],"predecessor-version":[{"id":2848,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2507\/revisions\/2848"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2507"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2507"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2507"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}