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!
#!/bin/sh # List Databases to Dump DBS="$(mysql -u %username% -h %hostname% -p%password% -Bse 'show databases')" for db in $DBS do 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 7za a $BACKUPFILE $DMPFILE # Upload Zip MONTH=$(date +"%Y%m") sudo s3cmd put $BACKUPFILE s3://bucket/$MONTH/$DMPFILE # Delete Local Dump and Zip sudo rm $DMPFILE $BACKUPFILE done
Would you like to download and try it? You can find it over at Spiceworks, right here.