Last active
July 11, 2018 16:26
-
-
Save rob-watts/c6d9ea48b56ecd474afc18f4d125d346 to your computer and use it in GitHub Desktop.
MySQL Backup with remote logging (longly) and sms notifications (callfire)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash | |
| # Script Description: this script backs up the database defined in $DB_NAME to $DB_PATH and offloads the product to S3 | |
| # Requirements: Amazon S3 account & credentials, s3 command line tools, mysql credentials, loggly account, call fire account | |
| # Note: This script reveals tokens and credentials for mysql, amazon, loggly and callfire. | |
| # Run: via cron or ./ | |
| # Rob Watts | |
| # Version 1.1 | |
| # Modified: July 11th 2018 | |
| # CallfireSms.sh located https://gist.github.com/rob-watts/02ac94a9b9ce42f0cf59c3e25160c332 | |
| # ========================================================================================================= | |
| # script variables | |
| # ========================================================================================================= | |
| # a phone to send sms messages to (relies on external script, callfiresms.sh) remove this and all calls to the same, if you don't need SMS notices) | |
| SMS1=2225551212 | |
| # path to callfire sms script | |
| SMS_PATH="/path/to/callfiresms.sh" | |
| # the remote s3 bucket to save your backups | |
| AWS_SAVE_PATH="bucket/path/" | |
| # the local path to save mysqldump files | |
| DB_PATH=/local/path/to/your/mysql-backups | |
| # the ip-host of your mysql database | |
| DB_HOST=127.0.0.1 | |
| # mysql login | |
| DB_USER=root | |
| # mysql password (yeah it's risky, so are no backups) | |
| DB_PASSWORD=password | |
| # db name | |
| DB_NAME=my_db | |
| # script name (should match the filename) | |
| SCRIPT_NAME=backup.sh | |
| # loggly token (if you don't use loggly, remove this and all the CURL code) | |
| TOKEN=00000000-1111-2222-3333-0000000000 | |
| # path to mysqldump. if mysqldump is in the $PATH, then you can remove this variable and and replace the $MYSQLDUMP line with the regular program name, mysqldump. | |
| MYSQLDUMP=/Applications/MAMP/Library/bin/mysqldump | |
| # from cron variable - set to TRUE if you're using loggly and running this from cron | |
| FROM_CRON="false" | |
| # | |
| # other variables. you don't need to mess with these | |
| # | |
| CURRENT_DATE=`date +%F` | |
| CURRENT_TIMESTAMP=$(date "+%Y-%m-%d-%H%M%S") | |
| SCRIPT_DIRECTORY="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )" | |
| #copy to offsite on these days | |
| OFFSITEDAYS=( 3 5 7 ) #wednesday friday sunday. options are 1 2 3 4 5 6 7 | |
| #current day of week: monday is 1 | |
| DAYOFWEEK=$(date +%u) | |
| # ========================================================================================================= | |
| # work area | |
| # ========================================================================================================= | |
| # | |
| # check to see if the backup path exists | |
| # | |
| if [ ! -d $DB_PATH ]; then | |
| # | |
| # make a folder to hold the sql backups | |
| # | |
| mkdir -p $DB_PATH | |
| # | |
| # double-check and exit if the folder path that needed to be created, wasn't | |
| # | |
| if [ ! -d $DB_PATH ]; then | |
| curl -H "content-type:application/x-www-form-urlencoded" \ | |
| -d "{ | |
| \"host\":\"$(hostname)\", | |
| \"command\":\"s3cmd\", | |
| \"appName\":\"$SCRIPT_DIRECTORY/$SCRIPT_NAME\", | |
| \"method\":\"bash\", | |
| \"fromCron\":\"$FROM_CRON\", | |
| \"message\":\"$(hostname) data backup folder ($DB_PATH) creation failed. backup halted prematurely.\" | |
| }" http://logs-01.loggly.com/inputs/$TOKEN/tag/backup/ | |
| $SMS_PATH -p $SMS1 -m "$(hostname) data backup folder ($DB_PATH) creation failed. backup halted prematurely." | |
| # | |
| # exit here | |
| # | |
| exit 1 | |
| fi | |
| fi | |
| # | |
| # find and remove .gz and .sql files older than 3 days. (if you remove this, the backups have unrestricted growth on the local file system) | |
| # WARNING: a malformed 'find and delete' command like this can have **terrible** consequences, especially if run as sudo. | |
| # | |
| find $DB_PATH/*.sql -type f -mtime +3 -exec rm {} \; | |
| find $DB_PATH/*.gz -type f -mtime +3 -exec rm {} \; | |
| # | |
| # perform sql backup of $DB_NAME to the $DB_PATH | |
| # replace $MYSQLDUMP with the regular mysqldump when mysqldump is in your path | |
| # | |
| $MYSQLDUMP --routines -h $DB_HOST -u $DB_USER -p$DB_PASSWORD $DB_NAME | gzip -f > $DB_PATH/$DB_NAME-$CURRENT_DATE.sql.gz | |
| # | |
| # make sure the gzip backup file was created. | |
| # | |
| if [ ! -f $DB_PATH/$DB_NAME-$CURRENT_DATE.sql.gz ]; then | |
| # | |
| # the zip file doesn't exist, so it cannot be copied to amazon, BUT the local backup file "might" still exist. | |
| # | |
| # loggly | |
| curl -H "content-type:application/x-www-form-urlencoded" \ | |
| -d "{ | |
| \"host\":\"$(hostname)\", | |
| \"command\":\"s3cmd\", | |
| \"appName\":\"$SCRIPT_DIRECTORY/$SCRIPT_NAME\", | |
| \"method\":\"bash\", | |
| \"fromCron\":\"$FROM_CRON\", | |
| \"message\":\"$(hostname) Backup of ($DB_NAME) may have completed but .gz file does not exist\" | |
| }" http://logs-01.loggly.com/inputs/$TOKEN/tag/backup/ | |
| # sms | |
| $SMS_PATH -p $SMS1 -m "$(hostname) Backup of ($DB_NAME) may have completed but .gz file does not exist." | |
| else | |
| DIRSIZE=$(du -sh $DB_PATH | cut -f -1) | |
| # | |
| # everything completed as expected, so we can now offload the gz file to Amazon S3 & send our notifications | |
| # | |
| # s3 | |
| if [[ " ${offSiteDays[@]} " =~ " ${dayOfWeek} " ]]; then | |
| # when the current day of the week is one of the off site days, we offload the local backup to amazon s3 | |
| s3cmd put $DB_PATH/$DB_NAME-$CURRENT_DATE.sql.gz s3://$AWS_SAVE_PATH --continue | |
| # loggly | |
| curl -H "content-type:application/x-www-form-urlencoded" \ | |
| -d "{ | |
| \"host\":\"$(hostname)\", | |
| \"command\":\"s3cmd\", | |
| \"appName\":\"$SCRIPT_DIRECTORY/$SCRIPT_NAME\", | |
| \"method\":\"bash\", | |
| \"fromCron\":\"$FROM_CRON\", | |
| \"message\":\"Offsite backup of zipped database ($DB_NAME) has completed ( current $DB_PATH size is $DIRSIZE )\" | |
| }" http://logs-01.loggly.com/inputs/$TOKEN/tag/backup/ | |
| # sms | |
| $SMS_PATH -p $SMS1 -m "S3 $(hostname) database backup of ($DB_NAME) completed.\ncurrent $DB_PATH size is $DIRSIZE" | |
| else | |
| # just send sms notification | |
| .$SMS_PATH -p $SMS1 -m "local $(hostname) database backup of ($DB_NAME) completed.\ncurrent $DB_PATH size is $DIRSIZE" | |
| fi | |
| fi |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
How to use this script and all it's features:
chmod +x backup.shandchmod +x callfiresms.shbackup.shfile.Get the callfiresms.sh script here:
https://gist.github.com/rob-watts/02ac94a9b9ce42f0cf59c3e25160c332
Place in your crontab if you want to to run automatically
For S3:
You need to have installed and configured, an amazon s3 account for remote backup if you want to use this feature. Remove the
s3cmdcall on line 147 and also the variableAWS_SAVE_PATHFor SMS
To receive SMS notices, you need a
callfire.comaccount and API tokens. This is pretty cheap at the moment. If you don't want SMS notices, remove all the SMS lines and variables.For Remote Logging
You need a free or paid account with
loggly.comto use the remote logging. All you need to do is place your API token in theTOKENvariable. Remove that variable and all thecurlcalls if you don't want that feature.