Image via Wikipedia

I have started to make more use of some hosting I’ve had for a while and needed to create automated backups from my webhost to a local server, should the worst happen.

I managed to find a few pages that did something similar to what I was looking for but nothing that was quite what I wanted so I downloaded Cygwin (a way to get Linux commands on Windows) and hacked together a couple of scripts to do my biding.

My local server is Windows based so the instructions are for getting this working on Windows. It should be very similar on Linux based systems due to the use of Cygwin but it may be necessary to modify the scripts.

The first one connects to the remote server and uses rsync to download all the files which make up the web sites. Rsync is really cool and will only download the changes between runs. As the content of my sites is stored in databases, this speeds up the download several-fold.

Once the files are downloaded, a 7-zip archive is created and copied to a specific directory. The filename includes a date and time stamp to make it easy to find the relevant file.

#!/bin/bash

PATH=/cygdrive/c/cygwin/bin

host=[remote host]
user=[username]
remoteHtdocs=[remote files directory]
remoteSql=\~[directory where sql files will be created]*.gz # \ required so that variable is for remote host, not local machine
localBase="/cygdrive/[local backup root folder]"
localWorkingFolder="$localBase/working"
monthFolder=$(date +%Y-%m)
archiveName=$(date +%Y%m%d-%k%M%S).7z
localBackupFolder="$localBase/backups/$monthFolder"
localSqlBackupFolder="$localBase/backups/$monthFolder/sql"
allFolders=( "$localWorkingFolder" "$localSqlBackupFolder" )

# check whether all folders exist
for f in ${allFolders[@]}
do
  if [ ! -d "$f" ]; then
    mkdir -p $f
  fi
done

# download sites to working folder
# avoids downloading all files each time
rsync -a $user@$host:$remoteHtdocs $localWorkingFolder

# compress files
cd $localBackupFolder

7z a $archiveName $localWorkingFolder -mx9

# run mysql backup script on server
ssh $user@$host 'bash ~/scripts/backup-db.sh'

rsync -a --remove-sent-files $user@$host:$remoteSql $localSqlBackupFolder

The MySQL backup script on the server is run remotely and contains the following commands:

#/bin/bash
user=[username]
password=[password]
host=[website host]

dest=[local folder on server]
timestamp=$(date -u +%Y%m%d-%k%M%S)
exclude=( 'information_schema' 'mysql' )

databases="$(mysql -u $user -h $host -p$password -Bse 'SHOW DATABASES')"

for db in $databases
do
 backup=1

 for e in $exclude
 do
   if [ $e == $db ];
   then
     backup=0
   fi
 done

 if [ $backup -eq 1 ];
 then
   filename=$dest/$db-$timestamp.gz
   mysqldump -u $user -h $host -p$password $db | gzip -9 > "$filename"
 fi
done

I then created a scheduled task so that the sites would be automatically backed up without me having to worry about remembering. As the scripts are written for bash, the task must call the bash.exe file and pass the script name as a parameter.

The following batch file does that simple task:

c:\cygwin\bin\bash -C /cygdrive/e/backup_website/backup.sh

Update: I found a case where the MySQL backup would not be completed if there was a space in the timestamp. This also led me to notice that the time on the remote server was different to the local time on my machine so all backups are now created with UTC-based timestamps to ensure they work across systems and will not fail if there is a space in the timestamp.