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)

backup_all_dbs.sh

#!/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)