Load most recent database backup from S3 into MySQL

Like a lot people I back up a production database to S3 quite frequently. I run my development database locally, and need a simple way to keep it up to date with production data.

So, yesterday I threw together this quick bash script that always fetches my most recent backup and loads it into my local MySQL database.

echo -e "Getting latest MySQL backup...\n"

# Basic variables
bucket="s3://db_backups"

# Timestamp (sortable AND readable)
year=`TZ=America/Los_Angeles date +%Y`
month=`TZ=America/Los_Angeles date +%m`
day=`TZ=America/Los_Angeles date +%d`

most_recent_backup=`s3cmd ls $bucket/$year/$month/$day/* | tail -1 | awk '{print $4}'`

tmpfile="/tmp/db_backup.sql.gz"
s3cmd get "$most_recent_backup" "$tmpfile"
gunzip $tmpfile

echo "To load the backup database enter the mysql root user's password"
read -s -p Password: mysqlpass

echo -e "\nLoading DB...\n"
mysql -u root -p$mysqlpass < '/tmp/db_backup.sql'

echo -e "Removing temp backup file...\n"
rm -f "/tmp/db_backup.sql"
echo -e "All done!\n"

This uses the s3cmd tool to download my backuo from S3. In order to use this you’ll need to install that and modify the $bucket variable and the path used in s3cmd ls $bucket/$year/$month/$day/* | tail -1 | awk '{print $4}' to match your S3 bucket structure.