Home of Devon Schreiner and Skewed Motion Media

Backup MySQL To Amazon S3

Today I was tasked with writing a backup script for our new MySQL Server. I’m a scripter/programmer by absolutely no means but I was kind of proud of this so I thought I would share. This particular script lists the databases and for each one, dumps it, appends the date to the end of the file, zips it wish 7zip, uploads it to Amazon S3 and then deletes the leftover files. Add this script to your crontab and it will be backing up your MySQL databases to Amazon S3 whenever you want. Let me know what you think!

# List Databases to Dump
 DBS="$(mysql -u %username% -h %hostname% -p%password% -Bse 'show databases')"
 for db in $DBS
   DMPFILE="$db"_$(date +"%Y%m%d").dmp
   BACKUPFILE="$db"_$(date +"%Y%m%d").7z

   # Dump Database
   mysqldump -u %username% -h %hostname% -p%password% $db > $DMPFILE

   # Zip Dump

   # Upload Zip
   MONTH=$(date +"%Y%m")
   sudo s3cmd put $BACKUPFILE s3://bucket/$MONTH/$DMPFILE

   # Delete Local Dump and Zip

Would you like to download and try it? You can find it over at Spiceworks, right here.

Leave a comment

Your email address will not be published. Required fields are marked *