====== 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, [[automated-backups#rsync-to-strongspace|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.


===== PostgreSQL =====

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

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

Replace DBONE etc. with the names of your databases.

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

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

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:

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

===== MySQL =====

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

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

Then, for each database:

<code>
% mysqldump --user=USERNAME -p -Q --database DBNAME > ~/backup/mysql/DBNAME/DATE.sql
% find ~/backup/mysql/DBNAME/*.sql -mtime +56 -exec rm {} \;
</code>
If you're going to do this for multiple databases, you can convert it into a shell function like this (assuming 'bash' shell):

<code>
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
}
</code>

Then, call it with:

<code>
mysql_backup your_database your_username your_password
</code>

===== Subversion =====

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:

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

Then, for each repository (compressing for good measure):((You can use either "svnadmin hotcopy" or "svnadmin dump" to create a backup (read more about it [[http://svnbook.red-bean.com/en/1.4/svn.reposadmin.maint.html|here]]).  I prefer "svnadmin dump"; if you know enough to have an opinion then you don't need my help implementing it.))

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

===== rsync to Strongspace =====

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):

<code>
% ssh-keygen -t dsa
</code>

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 ((Download the file:
<code>
% scp USERNAME@ORG.strongspace.com:.ssh/authorized_keys ./authorized_keys-ss
Password:
</code>
Append the new key:
<code>
% cat ~/.ssh/id_dsa.pub >> ./authorized_keys-ss
</code>
Re-upload it:
<code>
% scp ./authorized_keys-ss USERNAME@ORG.strongspace.com:.ssh/authorized_keys
Password:
% rm ./authorized_keys-ss
</code>)).

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

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).

<code>
% 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
</code>

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

<code>
% sftp USERNAME@ORG.strongspace.com
Connecting to ORG.strongspace.com...
sftp>
</code>

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):

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

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.

===== Cronjob script =====

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).

<code>
#!/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}
</code>

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

<code>
% chmod 700 ~/backup/backup.sh
</code>

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

<code>
% ~/backup/backup.sh
</code>

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.