Wednesday, September 25, 2013

Arvixe Hosting: Database Backups

Having just switched from ASP to Linux hosting at Arvixe, I needed to setup daily backups of my databases. ASP hosting made this very easy; however, the Linux move required me to dust the cobwebs off my shell scripting skills.

I had three MySQL databases to backup daily. I wanted to archive the backups and place the archive in a specific directory. I also wanted to delete any backup archives older than 7 days. The solution was quite easy starting with the backup script:


# cd to the backup diretory
cd ~/backups/

# backup the databases
mysqldump -ukeidav_critters -pPassword keidav_critters > keidav_critters.sql
mysqldump -ukeidav_mant164 -pPassword keidav_mant164 > keidav_mant164.sql
mysqldump -ukeidav_pier357 -pPassword keidav_pier357 > keidav_pier357.sql

# place the backups in an archive
FILE=sqlbackup-day$(date +%w).bz2
tar -jcvf $FILE *.sql --remove-files

# delete any archives older than 7 days
find . -type f -mtime +7 -delete

I placed the script in ~/bin and set the file to be executable (this can also be accomplished using the cpanel file manager):

chmod 755

The last step was to create a cron job set to run the script at the beginning of every day using cpanel:

There you have it! No hard at all, daily backups with a rolling week in a bzip archive.