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.