Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Backup All DB's and transfer to S3 with s3cmd + Rolling on the days of the weeks and on the weeks of the months + a montly backup (for paranoiac guys)
#!/bin/bash
BACKUP_DIR="~/db_backups"
DBUSER=""
DBPASSWORD=""
BUCKET="your-unique-bucket-name"
FROM='"Backups" <backups@localhost>'
TO='"Admin" <admin@localhost>'
SUBJECT='Backup Log'
TMPFILE="$(mktemp)"
echo "From: $FROM" > $TMPFILE
echo "To: $TO" >> $TMPFILE
echo "Subject: $SUBJECT" >> $TMPFILE
echo "MIME-Version: 1.0" >> $TMPFILE
echo "Content-Type: text/plain" >> $TMPFILE
for DB in $(mysql --user=$DBUSER --password=$DBPASSWORD -e 'show databases' -s --skip-column-names); do
if [[ $DB != *schema* && $DB != *mysql* ]]; then
TODAY=`date +%d` #PG
TOMORROW=`date +%d -d "1 day"` #PG
# if tomorrow is less than today, today is the end of the month #PG
if [ $TOMORROW -lt $TODAY ]; then #PG
VERSION="mon_$(date +"%m")" #PG
else #PG
if [ $(date +"%w") == 0 ]; then # it's Sunday: time to weekly backup!
# Make a weekly dump (wom_0, wom_1, ... , wom_4 and wom_5 - but only in big months)
# Thanks to Ryan Weal @ http://www.verbosity.ca/linux-training/week-month-calculation-cron
DAY_OF_MONTH=`date +%e`
DAY_OF_WEEK=$((`date +%u`-1))
OFFSET=$(((${DAY_OF_WEEK} + 36 - ${DAY_OF_MONTH}) % 7 ))
WEEK_OF_MONTH=$(((${DAY_OF_MONTH} + ${OFFSET} - 1) / 7))
VERSION="wom_$WEEK_OF_MONTH"
else
# Make a daily dump, rolling every day of the week excluding Sunday
# so dow_1, dow_2, ..., dow_5 will be created
VERSION="dow_$(date +"%u")"
fi
fi #PG
FILE="$DB-$VERSION.sql.gz"
mysqldump --user=$DBUSER --password=$DBPASSWORD --databases $DB | gzip -9 > $BACKUP_DIR/$FILE;
echo "************* $DB *************" >> $TMPFILE
echo -e "DB $FILE Exported" >> $TMPFILE
s3cmd put $BACKUP_DIR/$FILE s3://$BUCKET/$FILE >> $TMPFILE
echo -e "DB $FILE Transferred to S3\r\n" >> $TMPFILE
fi
done
echo "************* SIZE db-bak *************" >> $TMPFILE
s3cmd du s3://$BUCKET/ >> $TMPFILE
# Sync folders
BUCKET="your-unique-bucket-name-for-var-www"
echo "************* /VAR/WWW *************" >> $TMPFILE
s3cmd sync --delete-removed -p /var/www s3://$BUCKET/ >> $TMPFILE
echo "************* SIZE www-bak *************" >> $TMPFILE
s3cmd du s3://$BUCKET/ >> $TMPFILE
cat $TMPFILE | /usr/sbin/sendmail -i -t
# #PG = PARANOIAC GUYS: if you feel better to have a montly backup, you can leave this rows as is.
# If you think it is too paranoiac, then you can safety remove/comment these lines (the ones that end with #PG)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment