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

# 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}'`

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.


Now read this

The first paying customer

Line by line your product begins to take shape. Bleary eyed and tired you push on, willing your brain to solve just one more piece of the puzzle before you call it a night. You’re excited. You’re creating something. The thought that... Continue →