Skip to main content

Raymii.org Logo (IEC resistor symbol)logo

Quis custodiet ipsos custodes?
Home | About | All pages | RSS Feed | Gopher

Basic website and database backups with Duplicity

Published: 15-02-2013 | Author: Remy van Elst | Text only version of this article


Table of Contents


This tutorial will show you how you can back up your website and database withDuplicity. Duplicity is a very useful tool to make remote unassistedbackups. The backups can be incremental, encrypted, and over a variety oftransports.

If you like this article, consider sponsoring me by trying out a Digital OceanVPS. With this link you'll get $100 credit for 60 days). (referral link)

There are a few steps involved in the process of setting this up, and also a fewpreconditions:

Basic web application backup

First I'll try to educate you a little bit with some theory.

This tutorial works for all web applications, since they almost all share thesame common structure:

We are going to back up files. Two of the three things above are already files,and the database will be exported to a file. The database will be exported sothat it can be imported again using native tools (mysqldump, mongodump etc.),since just copying the database folders and files will almost always result in acorrupted database.

Duplicity has a few advantages to other backup tools and scripts:

I myself have the following set up for almost all of my web application backups:

Your situation

For this tutorial I'm going to backup a basic Joomla website. Joomla is aPHP/MySQL CMS. The files are located in /var/www/joomla, and the MySQLdatabase is named db_054. I'm also giving the same examples for a Node.jsapplication using mongodb. The path for that application is /home/appusr/www/and that database is named uptime.

You will have to know some things yourself:

In this example we will use mysqldump to backup the MySQL database, and useduplicity to backup the database dumps, the /var/www/joomla folder and the/etc/apache2 folder (which has the webserver configuration). If this site ranover SSL then I would also backup the folder where my SSL certificate was.

We are going to put it all in a cronjob, which runs every 8 hours. We will alsohave a full weekly backup, via another cron job.

Software

Make sure you have the required software installed:

sudo apt-get install duplicity gzip python python-paramiko

MySQL

The mysqldump command to backup the database:

mysqldump --single-transaction --routines --events --triggers --add-drop-table --extended-insert -u db_054_u -h 127.0.0.1 -pMyDatabsePassw0rd db_054 | gzip -9 > /var/backups/sql/db_054_$(date +"%H:%M_%d-%m-%Y").sql.gz

You will have to replace the -u db_054_u with your database user, the-pMyDatabasePassw0rd with your database password and db_054 with the name ofyour database.

The above line will result in a file named like this:db_054_15:28_16-02-2013.sql.gz in the folder /var/backups/sql. You will needto create that folder and make sure you have write permissions to it.

MongoDB

For Mongodb I use the mongodump command to create a backup of that database:

mongodump --host 127.0.0.1:28017 --db uptime --user uptime_u --password uptime_password --out /var/backups/mongo/ 

Note that if you want to backup all mongodb databases you can omit the --dbdatabase option.

Again replace the corresponding options with your values, and make sure the/var/backups/mongo folder exists and is writable to you.

Duplicity

Note: I will not cover the setup of encrypted backups with Duplicity. My backupsgo to a trusted server (located at my home). If you need that because you arebacking up to S3, use a search engine to find tutorials on how to set up that.

First we need to create a ssh key to use. We do this with the ssh-keygenprogram:

~$ ssh-keygen -C 'Duplicity Backup Key'Generating public/private rsa key pair.Enter file in which to save the key (/home/remy/.ssh/id_rsa): /home/remy/.ssh/backup_rsaEnter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/remy/.ssh/backup_rsa.Your public key has been saved in /home/remy/.ssh/backup_rsa.pub.

Make sure you do not enter a passphrase. If you do that, the backup process doesnot work unattended.

Now copy over that key to your other (trusted) server:

ssh-copy-id -i /home/remy/.ssh/backup_rsa.pub "-p 22 trusted-backup-server.org"

And test if you can login without a password with that key:

ssh -i /home/remy/.ssh/backup_rsa -p 22 remy@trusted-backup-server.org

If that all works continue.

We are now going to set up duplicity. I use the following command to back up allthe folders:

duplicity incremental --no-encryption --ssh-options="-oProtocol=2 -oIdentityFile=/home/remy/.ssh/backup_rsa" --include="/var/backups/sql" --include="/etc/apache2/" --include="/var/www/joomla" --exclude="**" /  ssh://remy@trusted-backup-server.org:22/backups/joomla/

If you need to backup more folders, add another --include="/path/to/folder"option. The --exclude="**" / option is a trick to backup everything which isin the include list and nothing else.

For the Node.js/Mongodb application, I would use the following command:

duplicity incremental --no-encryption --ssh-options="-oProtocol=2 -oIdentityFile=/home/remy/.ssh/backup_rsa" --include="/var/backups/mongo" --include="/home/appuser/www/" --exclude="**" /  ssh://remy@trusted-backup-server.org:22/backups/uptime/

Cron

Now putting everything in a cronjob. By using the ; character after a command,you can specify multiple commands in one cronjob which run after each other. Wecombine the database dump with the duplicity command to set up the back up:

10 01 * * 1,2,3,4,5,6 mysqldump --single-transaction --routines --events --triggers --add-drop-table --extended-insert -u db_054_u -h 127.0.0.1 -pMyDatabsePassw0rd db_054 | gzip -9 > /var/backups/sql/db_054_$(date +"%H:%M_%d-%m-%Y").sql.gz; duplicity incremental --no-encryption --ssh-options="-oProtocol=2 -oIdentityFile=/home/remy/.ssh/backup_rsa" --include="/var/backups/sql" --include="/etc/apache2/" --include="/var/www/joomla" --exclude="**" /  ssh://remy@trusted-backup-server.org:22/backups/joomla/

This schedule will create an incremental backup every day of the week at 01:10AM except sunday. For sunday we have a different cronjob. The only thingdifferent is the day of the week and the duplicity command has theincremental parameter replaced by full. This forces duplicity to do a fullbackup:

10 01 * * 7 mysqldump --single-transaction --routines --events --triggers --add-drop-table --extended-insert -u db_054_u -h 127.0.0.1 -pMyDatabsePassw0rd db_054 | gzip -9 > /var/backups/sql/db_054_$(date +"%H:%M_%d-%m-%Y").sql.gz; duplicity full --no-encryption --ssh-options="-oProtocol=2 -oIdentityFile=/home/remy/.ssh/backup_rsa" --include="/var/backups/sql" --include="/etc/apache2/" --include="/var/www/joomla" --exclude="**" /  ssh://remy@trusted-backup-server.org:22/backups/joomla/

For the node.js/mongodb application we use the following cronjobs:

Incremental:

10 01 * * 1,2,3,4,5,6 mongodump --host 127.0.0.1:28017 --db uptime --user uptime_u --password uptime_password --out /var/backups/mongo/; duplicity incremental --no-encryption --ssh-options="-oProtocol=2 -oIdentityFile=/home/remy/.ssh/backup_rsa" --include="/var/backups/mongo" --include="/home/appuser/www/" --exclude="**" /  ssh://remy@trusted-backup-server.org:22/backups/uptime/

Full on sunday:

10 01 * * 7 mongodump --host 127.0.0.1:28017 --db uptime --user uptime_u --password uptime_password --out /var/backups/mongo/; duplicity full --no-encryption --ssh-options="-oProtocol=2 -oIdentityFile=/home/remy/.ssh/backup_rsa" --include="/var/backups/mongo" --include="/home/appuser/www/" --exclude="**" /  ssh://remy@trusted-backup-server.org:22/backups/uptime/

Restoring

Now that we have backups, how do we restore them? Files can just be copied tothe right place, databases need to be imported.

First we get the backups from the backup server:

duplicity --no-encryption --ssh-options="-oProtocol=2 -oIdentityFile=/home/remy/.ssh/backup_rsa" --file-to-restore / ssh://remy@trusted-backup-server.org:22/backups/joomla/

If you need a backup from an earlier date:

duplicity --no-encryption --ssh-options="-oProtocol=2 -oIdentityFile=/home/remy/.ssh/backup_rsa" -t 3D --file-to-restore / ssh://remy@trusted-backup-server.org:22/backups/joomla/

The -t 3D option means restore a backup from three days ago. Things like -t1M (for one month ago) or -t 5H (for 5 hours ago) also work.

You will now have the folders and files you backed up. Copy the files back inplace using cp. For the databases we use their respective tools.

For MySQL, first gunzip the archive, and then import it:

gunzip db_054_15:28_16-02-2013.sql.gz

And then restore it:

mysql -u db_054_u -pMyDatabasePassw0rd db_054 < db_054_15:28_16-02-2013.sql

For MongoDB:

mongorestore mongo/

Make sure you try to restore your backups at least one or twice a month. Thiswill make sure the backups are usable when you need them!

Tags: backup, duplicity, mongodb, mysql, ssh, tutorials