So like I promised here’s the script I banged together to allow easy recovery of your MySQL backup sets on S3. At the moment, it only does the current day so if it is just after midnight, well, you won’t see any backups! I plan on updateing it to allow the user to choose today or yesterday and then build the list from that selection.

#/bin/bash
# This script will list the most recent backups based on a number prompted by the user
# decrypt and expand them into a temp directory.
# set date variables

cd /opt/s3sync

DAYNOW=$(date +%j)
TIMENOW=$(date +%H%M)
# set the environment
export AWS_ACCESS_KEY_ID=XXXXXX
export AWS_SECRET_ACCESS_KEY=XXXXXX
export SSL_CERT_DIR=/opt/s3sync/certs

echo -e "How many backups would you like to list? \c"
read count
echo
# Get the list of backups on the server using s3cmd
dbsets=$(ruby s3cmd.rb list YOURDB_db_backups:$DAYNOW | tail -n $count)
ARRAY=($dbsets)
# get number of elements in the array
ELEMENTS=${#ARRAY[@]}

# echo each element in array
# for loop
for (( i=0;i<$ELEMENTS;i++)); do
echo $i - ${ARRAY[${i}]:4}
done

# Prompt user for which backup they want to recover
echo
echo -e "Which backup set would you like to recover? \c"
read numbackup
backup=${ARRAY[$numbackup]:4}
tarset=${backup:0:31}
sqlset=${tarset:0:19}

echo "I am fetching your backup $backup now..."

ruby s3cmd.rb get YOURDB_db_backups/$DAYNOW:$backup /mnt/tmp/recovery/$backup

echo
echo "I'm going to decrypt your backup..."

cd /mnt/tmp/recovery

gpg -d $tarset > $sqlset

echo
echo "Cleaning up after myself..."
rm *.gz*
echo
echo "Your backup can be found here /mnt/tmp/recovery/$sqlset"

Next up is a script that easily allows you to chuck files or directories up onto S3 from your EC2 instance or from your local machine.