Here’s how to perform backups with duplicity and xtrabackup with some scripts I wrote and which you can find on Github at https://github.com/vitobotta/admin-scripts.

xtrabackup

Xtrabackup is a great tool for taking MySQL hot backups (both full and incremental) of your MySQL databases without bringing them offline. When you first launch the script – admin-scripts/backup/xtrabackup.sh – without arguments, it will generate the simple configuration file as ~/.xtrabackup.config, containing the following configuration settings – you only need to set the MySQL credentials, customise the paths of source and destination, and choose how many backup chains to keep:

MYSQL_USER="..." MYSQL_PASS="..." MYSQL_DATA_DIR=/var/lib/mysql BACKUPS_DIRECTORY=/backup/mysql/ MAX_BACKUP_CHAINS=4

A backup chain is as usual made of one full backup and subsequent incrementals. The script – admin-scripts/backup/xtrabackup.sh accepts a single argument when you are taking backups, either full or incr. As these may suggest, in the first case a full backup will be taken, while the second case it will be an incremental. Backups are stored in the destination directory with the structure below:

/backup/mysql ├── full │ ├── 2014-03-04_20-39-39 │ ├── 2014-03-09_02-00-04 │ ├── 2014-03-16_02-00-01 │ └── 2014-03-23_02-00-02 └── incr ├── 2014-03-04_20-39-53 ├── 2014-03-04_20-41-21 ├── 2014-03-05_02-00-02 ├── 2014-03-05_13-00-02 ├── 2014-03-06_02-00-07

I choose to store the incrementals separately from the full backups so to always have full backups ready for a simple copy if needed, but restoring from incrementals will work just fine. In order to restore, you can choose from any of the backups available – either full or incremental. To see the list of all the backups available you can use the list argument, which shows something like this:

> admin-scripts/backup/xtrabackup.sh list Loading configuration from /root/.xtrabackup.config. Available backup chains (from oldest to latest): Backup chain 1: ... Backup chain 2: ... Backup chain 3: Full: 2014-03-16_02-00-01 Incremental: 2014-03-16_13-00-01 Incremental: 2014-03-17_02-00-02 ... Incremental: 2014-03-21_13-00-01 Incremental: 2014-03-22_02-00-01 Incremental: 2014-03-22_13-00-02 Backup chain 4: Full: 2014-03-23_02-00-02 Incremental: 2014-03-23_13-00-01 Incremental: 2014-03-24_02-00-03 Incremental: 2014-03-24_13-00-01 Incremental: 2014-03-25_02-00-01 Incremental: 2014-03-25_13-00-02 Latest backup available: Incremental: 2014-03-25_13-00-02

Then, to restore any of the backups available you can run the script with the restore argument, e.g.

admin-scripts/backup/xtrabackup.sh restore 2014-03-25_02-00-01 <destination directory>

Once the restore is complete, the final result will be a destination directory ready for use with MySQL, so all you need to do at this stage (as the script will suggest) is:

stop MySQL

replace the content of MySQL’s datadir with the contents of the destination directory you’ve used for the restore

ensure the MySQL datadir is owned by the mysql user

start MySQL again

MySQL should happily work again with the restored data.

duplicity

The other script is a useful wrapper which makes it a bit easier to take backups of data with duplicity; like the other script, this script also uses a configuration file instead of lots of options and arguments, and this configuration file is generated as ~/.duplicity.config when you first run the script with no arguments. The content of this configuration file is as follows:

INCLUDE=(/backup /etc /home /root /usr/local/configuration /var/log /var/lib/mysql /var/www) BACKUPS_REPOSITORY="rsync://[email protected]//backup_destination_directory/" MAX_FULL_BACKUPS_TO_RETAIN=4 MAX_AGE_INCREMENTALS_TO_RETAIN=1W MAX_AGE_CHAINS_TO_RETAIN=2M MAX_VOLUME_SIZE=250 ENCRYPTION=1 PASSPHRASE=... # Set ENCRYPT_KEY if you want to use GPG pub key encryption. Otherwise duplicity will just use symmetric encryption. # ENCRYPT_KEY= # Optionally use a different key for signing # SIGN_KEY= # SIGN_KEY_PASSPHRASE= COMPRESSION_LEVEL=6 # 1-9; 0 disables compression; it currently works only if encryption is enabled VERBOSITY=4 # 0 Error, 2 Warning, 4 Notice (default), 8 Info, 9 Debug (noisiest) # Comment out the following if you want to run one or more scripts before duplicity backup. RUN_BEFORE=(/root/admin-scripts/backup/xtrabackup.sh) # Comment out the following if you want to run one or more scripts after duplicity backup. #RUN_AFTER=()

Most of these settings should be self-explanatory. backups_repository uses by default duplicity’s rsync backend, so of course you need to have SSH access to the destination server. max_volume_size: duplicity automatically splits the backup into volumes and the script will use settings that have duplicity generate one volume while the previous one is being asynchronously transferred to the destination. This should make backups faster. The ideal value for max_vol_size is really difficult to determine as it depends on many things, but in my case I have found that a value of 250 with the other settings I use for compression and encryption, makes backups fairly fast. encryption of course enables/disables the encryption of the backup; if you are doing on site backup to servers you own and that noone else controls, then I’d disable this option so to make backups quicker. Otherwise I recommend to enable it if others have access to the backup files. Encryption can be done both with (GPG) keys, or without keys, using symmetric encryption with a passphrase. Then, you can set the compression level; I’d recommend the value 6 as from my tests higher compression slows down backups for little gain. As the comment in the configuration file suggests, compression is currently available only when encryption is also enabled.

Lastly, as you can see you can choose to run other scripts before and/or after the backup with duplicity is performed. In the configuration above you can also see that I normally run the backup with the xtrabackup script first, so that the backup taken with duplicity also includes the latest MySQL backup. I find this pretty useful. Like for the other script, you need to specify the full or incr argument when taking backups; this argument will automatically be passed to the scripts specified in run_before and run_after so, for example, when taking an incremental backup with duplicity, an incremental backup with xtrabackup is taken first.

Restoring latest backup available

Example:

Note: Duplicity will not overwrite an existing file.

duplicity – other useful commands

Restoring from backups with duplicity is a little more straightforward than backing up, so I haven’t added any commands for this in the script really. However I’ll add here, for reference, some useful commands you may likely need when restoring or else directly with duplicity. These are examples assuming you use duplicity with symmetric encryption, in which case you need to have the PASSPHRASE environment variable set and available:

export PASSPHRASE=... # the passphrase you've used in the configuration file; you'll need this will all

If you add these commands in some other scripts, remember to unset this variable with

unset PASSPHRASE

Listing available backups

Listing all files in current backup

Restoring by date / specific files (e.g. 3 days ago)

duplicity -v debug -t 3D --file-to-restore FILENAME rsync://[email protected]//backup_directory <destination>

Also:

Note: timestamps shown when listing available backups are in already in timezone, while the time on the server is in UTC. So a backup made e.g. on 24/2/2014 at 02:00 on the server means it will be listed as Mon Feb 24 04:00:35 2014. Restoring this backup means using the timestamp xxxx-xx-xxT02:00:00+02:00.

If you are looking to use free tools, these scripts and commands should have your backup needs on servers covered in most cases.