Automated backups to Strongspace

This is how I backup my account on a Shared Accelerator over to Strongspace. The basic principle is to create a dump file of each component that isn't directly represented on the filesystem (PostgreSQL databases, for example), and then rsync the whole of /users/home/USERNAME over. I perform the backup once a week, and keep the last eight dump files; of course, you should tailor this depending on how your data is changing.

What I'll do is explain how I create a dump of each component in term (this is purely informational; you can skip ahead to the next section, rsync to Strongspace, if you want). I'll then talk a bit about rsync-ing to Strongspace. Finally, I'll explain how to tie this all together into a shell script called via a cronjob.

One last thing to note is that the options used mean that nothing gets echoed unless there's a problem. This is to do with a personal preference about cronjobs, and will get mentioned in that section.

The first thing to do is create the directory where the SQL dumps are going to be stored.

% mkdir -p ~/backup/postgresql/{DBONE,DBTWO,DBTHREE}

Replace DBONE etc. with the names of your databases.

For each database, then, we want to run (replacing USERNAME, DBNAME and DATE):

% pg_dump -h localhost -U USERNAME -cOx -f ~/backup/postgresql/DBNAME/DATE.sql DBNAME

You may want to tweak the options, such as -o to include OIDs; “pg_dump --help” is your friend.

Remove all backups more than eight weeks old:

% find ~/backup/postgresql/DBNAME/*.sql -mtime +56 -exec rm {} \;

A similar procedure for MySQL. First, create the directory structure:

% mkdir -p ~/backup/mysql/{DBONE,DBTWO,DBTHREE}

Then, for each database:

% mysqldump --user=USERNAME -p -Q --database DBNAME > ~/backup/mysql/DBNAME/DATE.sql
% find ~/backup/mysql/DBNAME/*.sql -mtime +56 -exec rm {} \;

If you're going to do this for multiple databases, you can convert it into a shell function like this (assuming 'bash' shell):

function mysql_backup
{
    database=$1
    shift
    username=$1
    shift
    password=$1
    shift

    mkdir -p $BACKUPDIR/mysql/$database

    echo "$database : mysqldump"
    output=$BACKUPDIR/mysql/$database/`date +%Y%m%d`.sql.gz
    mysqldump --user=$username --password=$password -Q --database $database | gzip > $output
    ls -lhart $output

    # Delete backups older than 3 days
    echo "$database : deleting old backups"
    find $BACKUPDIR/mysql/$database/*.sql.gz -mtime +3 -exec rm {} \;
    echo
}

Then, call it with:

mysql_backup your_database your_username your_password

Subversion repositories are actually stored under your home directory (/users/home/USERNAME/svn/REPOS for the primary domain and /users/home/USERNAME/domains/DOMAINNAME.TLD/svn/REPOS for others). Whilst in theory one could just leave them and then rsync across, the risk is that the repository will be copied in a transitional state (such as mid-commit) and you'll have a faulty backup.

So, let's create a directory structure:

% mkdir -p ~/backup/subversion/{REPOSONE,REPOSTWO,REPOSTHREE}

Then, for each repository (compressing for good measure):¹⁾

% svnadmin dump -q ~/svn/REPOSNAME | gzip > ~/backup/subversion/REPOSNAME/DATE.gz
% find ~/backup/subversion/REPOSNAME/*.gz -mtime +56 -exec rm {} \;

The first thing to do is to set up passwordless logins to Strongspace. This is done using an authentication key which doesn't have a password on it.

First, let's create the key (press <return> at every prompt without typing anything):

% ssh-keygen -t dsa

Next, upload the public part of the key to ~/.ssh/authorized_files on Strongspace (replace USERNAME and ORG with your Strongspace details). If you've already got a key in authorized_files, then be sure to just append it, because the code below will overwrite it ²⁾.

% cd ~/.ssh
% sftp USERNAME@ORG.strongspace.com
Conecting to ORG.strongspace.com...
Password:
sftp> mkdir .ssh
sftp> put id_dsa.pub authorized_keys

As anyone who can access the private key can login to your Strongspace account without a password, we want to make sure that the permissions on everything are appropriate (it can also stop passwordless logins working correctly if they're not).

% chmod 700 ~/.ssh && chmod 600 ~/.ssh/id_dsa
% sftp USERNAME@ORG.strongspace.com
Conecting to ORG.strongspace.com...
Password:
sftp> chmod 700 .ssh
Changing mode on /home/USERNAME/.ssh
sftp> chmod 600 .ssh/authorized_keys
Changing mode on /home/USERNAME/.ssh/authorized_keys

Verify that the passwordless login now works. You should get an SFTP prompt without being asked for a password:

% sftp USERNAME@ORG.strongspace.com
Connecting to ORG.strongspace.com...
sftp>

Finally, the rsync command that we're using is (note that JOYUSER and SSUSER aren't necessarily the same; replace BACKUPDIR as appropriate—I use cooper, the name of my Shared Accelerator):

% rsync -rltpqz --delete /users/home/JOYUSER/ SSUSER@ORG.strongspace.com:BACKUPDIR

rsync performs an incremental one-way sync, so the first time you run it it could take quite a while as it needs to copy everything over (after that, it only copies changes). It is therefore advisable to run rsync once now before setting up the cronjob (replace -q with -v as well) to avoid a really long first cronjob.

Having explained a bit about each component, I'm now going to tie that all together by creating a shell script that can be called via a cronjob; it doesn't pre-suppose that you've done any of the above sections except for set up passwordless rsyncs to Strongspace. Note that there are a few changes due to the fact that it's called via cron.

Put this script in ~/backup/backup.sh (nano is oft-cited as an easy-to-use text editor), replacing values in the configuration section as appropriate (replace the bit to the right of the equals sign).

#!/bin/sh

# THIS IS THE CONFIGURATION SECTION ############################################

# The primary username that you use to login to the Shared Accelerator, 
# PostgreSQL and MySQL (i.e. /users/home/username).
JOYUSER=username

# Your PostgreSQL password.  For most people, this is the same as their primary 
# user's password (unless you've deliberately unlinked them via Virtualmin).
PGPASSWORD=password

# A space-separated list of PostgreSQL database names to backup.
PGDBS='psql_dbone psql_dbtwo'

# Your MySQL password.  Once again, this is probably the same as your primary
# user's password.
MYPASSWORD=password

# A space-separated list of MySQL database names to backup.
MYDBS='mysql_dbthree mysql_dbfour'

# A space-separated list of Subversion repositories to backup.  If they're under
# the primary domain, then just specify the name.  Otherwise, specify them as
# domainname.tld/reposname.
SVNREPOS='reposone repostwo domain.com/reposthree anotherdomain.net/reposfour'

# Your Strongspace username.
SSUSER=username

# Your Strongspace 'organisation' name (if you login to 
# me.strongspace.com, then it's "me").
SSORG=org

# The directory under which to store the backup on Strongspace.  I use "cooper",
# the name of my Shared Accelerator.
SSDIR=joyentsabackupdir

# How many days to keep backups for.  For example, if you backup once a week and
# want to keep eight backups on disk, set this to 56.
MTIME=56


# DON'T EDIT ANYTHING BELOW HERE, UNLESS YOU'RE DOING SOMETHING CUSTOM #########

DATESTAMP=`date "+%Y-%m-%d"`
BASEDIR="/users/home/${JOYUSER}/backup"

for db in ${PGDBS}
do
  /usr/bin/mkdir -p ${BASEDIR}/postgresql/${db}
  PGPASSWORD=${PGPASSWORD} /usr/local/bin/pg_dump -h localhost -U ${JOYUSER} -cOx -f ${BASEDIR}/postgresql/${db}/${DATESTAMP}.sql ${db}
  /usr/bin/find ${BASEDIR}/postgresql/${db}/*.sql -mtime +${MTIME} -exec /usr/bin/rm {} \;
done
unset db

for db in ${MYDBS}
do
  /usr/bin/mkdir -p ${BASEDIR}/mysql/${db}
  /usr/local/bin/mysqldump --user=${JOYUSER} --password=${MYPASSWORD} -Q --database ${db} > ${BASEDIR}/mysql/${db}/${DATESTAMP}.sql
  /usr/bin/find ${BASEDIR}/mysql/${db}/*.sql -mtime +${MTIME} -exec /usr/bin/rm {} \;
done

for repos in ${SVNREPOS}
do
  if [ `/usr/bin/echo ${repos} | /usr/bin/grep -c '/'` = '1' ]
  then
    reposdir="/users/home/${JOYUSER}/domains/`/usr/bin/echo ${repos} | /usr/bin/sed -e 's:/:/svn/:'`"
    reposbackupdir="${BASEDIR}/subversion/`/usr/bin/echo ${repos} | /usr/bin/sed -e 's:/:_:'`"
  else
    reposdir="/users/home/${JOYUSER}/svn/${repos}"
    reposbackupdir="${BASEDIR}/subversion/primary_${repos}"
  fi
  mkdir -p ${reposbackupdir}
  /usr/local/bin/svnadmin dump -q ${reposdir} | /usr/bin/gzip > ${reposbackupdir}/${DATESTAMP}.gz
  /usr/bin/find ${reposbackupdir}/*.gz -mtime +${MTIME} -exec /usr/bin/rm {} \;
done

/usr/local/bin/rsync -rltpqz --delete /users/home/${JOYUSER}/ ${SSUSER}@${SSORG}.strongspace.com:${SSDIR}

Next, we want to make the script executable, and visible by no-one else.

% chmod 700 ~/backup/backup.sh

At this stage, check that it runs okay (if all's well, then it shouldn't output a message.

% ~/backup/backup.sh

Finally, add it as a cronjob. To do this, sign in to Virtualmin. Under Webmin Modules, click Scheduled Cron Jobs, and then Create a new scheduled cron job. In the input box labelled Command put ”/users/home/USERNAME/backup/backup.sh” (without the quotes). Finally, set when you want it to execute (I've set mine to 0500 every Monday), hit Create to finish and you're good to go.

It's worth noting, as previously mentioned, that the script doesn't echo anything out unless it encounters a problem, in which case it'll e-mail you. You can test this out by setting one of the passwords to something incorrect, running the cronjob, and then checking your inbox.