How can I set up automated, encrypted backups of live MySQL databases on a Ubuntu VPS to Google Drive using Duplicity?
We're using DigitalOcean as our VPS provider. They have a tutorial on how to set up Duplicity for encrypted backups, but it doesn't cover database backups or the Google Drive endpoint.
I found a few other tutorials online regarding Duplicity and Google Drive, but they all seem to be either incomplete or out of date.
It took me a little over 12 hours to cobble together all the information I needed, but here is the process:
Step 1: Google account
Choose an existing, or create a new Google account where you will store your backups. I like to have a separate account so my business backup data doesn't get mixed in with my personal Google account stuff.
We will refer to the Google account name as <google_account_name>.
Step 2: Set up database dumps
To do this, we'll create a special directory for database dumps, and use the mysqldump command.
Create a backup directory and give yourself necessary permissions
My distribution of Ubuntu came with a backup user and group and a var/backups directory, so why not use those?
Make the group backup owner of /var/backups:
sudo chgrp backup /var/backups
Give the backup group read and write permissions on this directory:
sudo chmod g+rw /var/backups
Add your own account to the backup group:
sudo usermod -aG backup <username>
This will make it easier for you to access the contents of the backup directory.
You may need to log out and then log back in for the new group membership to take effect. To check your group membership, use the command groups.
Create a special SQL user account for performing backups
Log into MySQL through the command line:
mysql -u root -p
Create a new database user account
We don't want to make ourselves vulnerable by giving any more permissions than we absolutely have to. Thus, we will create a new database user account with read-only privileges. To stay consistent, I'm calling this user backup. Pick a very strong password for <db_password>.
CREATE USER 'backup'@'localhost' IDENTIFIED BY '<db_password>'
Grant read-only privileges:
GRANT SELECT,EVENT,TRIGGER,SHOW DATABASES ON *.* TO 'backup'@'localhost';
Set up the DB backup command:
Test out the dump command (replace <db_password> with the password you set earlier for the new MySQL user):
mysqldump --single-transaction --routines --events --triggers --add-drop-table --extended-insert -u backup -h 127.0.0.1 -p<db_password> --all-databases | gzip -9 > /var/backups/sql/all_$(date +"%Y_week_%U").sql.gz
This command will dump all databases to a single file, labeled with the year and current week number. Each time we run this, it will update the current dump file. However when a new week commences, it will end up creating a new file instead. Thus, we maintain a history of weekly snapshots of our databases. You can adjust the date portion to make these snapshots more or less frequent, depending on the size of your database and the space you're willing to donate for these snapshots.
Step 3: Install dependencies for Duplicity
We need the latest versions of the following libraries to be able to use the latest version of Duplicity:
poptlibbz2librsync
Run the following commands:
sudo apt-get install libpopt-dev libpopt0
sudo apt-get install libbz2-dev
cd ~
wget https://github.com/librsync/librsync/archive/v2.0.0.tar.gz
tar xzvf v2.0.0.tar.gz -C librsync
cd librsync
sudo cmake .
sudo make all check
sudo make && sudo make install
Update Duplicity
The current stable version as of October 6, 2016 is 0.7.10.
cd ~
wget https://code.launchpad.net/duplicity/0.7-series/0.7.10/+download/duplicity-0.7.10.tar.gz
tar xzvf duplicity*
cd duplicity*
sudo python setup.py install
Set up a definition for LD_LIBRARY_PATH (see How to set $LD_LIBRARY_PATH in Ubuntu?):
Duplicity needs this environment variable so that it can find the librsync shared library objects that were installed earlier.
sudo nano /etc/ld.so.conf.d/librsync.so.2.conf
librsync.so.2.conf:
/usr/local/lib
You must now reload Ubuntu's ldconfig cache:
sudo ldconfig
Install PyDrive
This is the library that handles the OAuth2 negotiation between Duplicity and the Google Drive API.
pip install pydrive
Step 4: Set up Google Drive authentication via OAuth2
Create API credentials
Do this through Google's Developer Console. See:
- https://stackoverflow.com/questions/31370102/how-do-i-backup-to-google-drive-using-duplicity
- http://6ftdan.com/danielpclark/2016/04/21/encrypted-linux-backup-with-google-drive-and-duplicity/
Create config file:
PyDrive uses this file to store credentials and configuration settings for the Google API.
nano /home/<username>/.duplicity/credentials
client_config_backend: settings
client_config:
client_id: <your client ID>.apps.googleusercontent.com
client_secret: <your client secret>
save_credentials: True
save_credentials_backend: file
save_credentials_file: /home/<username>/.duplicity/gdrive.cache
get_refresh_token: True
Set up the GOOGLE_DRIVE_SETTINGS environment variable:
export GOOGLE_DRIVE_SETTINGS=/home/<username>/.duplicity/credentials
I would also recommend adding GOOGLE_DRIVE_SETTINGS to sudo environment variables:
sudo visudo
Add the following line at the end:
Defaults env_keep += "GOOGLE_DRIVE_SETTINGS"
Step 5: Test unencrypted fake backup
(Reference: https://www.digitalocean.com/community/tutorials/how-to-use-duplicity-with-gpg-to-securely-automate-backups-on-ubuntu)
We'll create some test files, just to check that we can transfer them to Google Drive using Duplicity successfully.
Create test files:
cd ~
mkdir test
touch test/file{1..100}
Run Duplicity:
duplicity ~/test gdocs://<google_account_name>@gmail.com/backup
Follow the verification link it creates, and copy-paste the verification code you receive back into the prompt. Duplicity should store the auth token it creates in /home/<username>/.duplicity/gdrive.cache so that we don't have to do the verification step again (and so our system can automatically do this every night without our input).
Step 6: Create GPG Key
You will need a key for GPG to encrypt your backup data before it is sent to Google Drive. To generate the key, simply run the command:
gpg --gen-key
Follow the instructions it provides, and make sure you choose a good passphrase. If it gets stuck with a message about "not enough entropy", you can try running sudo apt-get install rng-tools. The installation itself should generate enough entropy that GPG can generate a truly random key. See https://stackoverflow.com/a/12716881/2970321.
The GPG "fingerprint" will be displayed after this completes. You will need the primary public key id from this fingerprint. This is simply the 8-digit hex code after the / on the line that begins with pub. See https://security.stackexchange.com/a/110146/74909.
Add the passphrase that you set for your GPG key to a secret file:
sudo nano /root/.passphrase
sudo chmod 700 /root/.passphrase
.passphrase:
PASSPHRASE="my passphrase"
Backup your GPG key:
If you lose your GPG key, your encrypted backups will become useless. So, you should back up your GPG key to some place besides your VPS.
For example, to backup to your local machine:
gpg --list-keys
gpg -ao ~/gpg-public.key --export <gpg_public_key_id>
gpg --list-secret-keys
gpg -ao ~/gpg-private.key --export-secret-keys <gpg_private_key_id>
Then on your local machine:
scp <username>@<vps_host>:~/gpg-public.key ~/gpg-public.key
scp <username>@<vps_host>:~/gpg-private.key ~/gpg-private.key
See:
https://help.ubuntu.com/community/GnuPrivacyGuardHowto#Backing_up_and_restoring_your_keypair
Depending on the nature of your data, you may want to consider putting the private portion of your GPG key on a piece of paper, and then storing that piece of paper in a safe.
Step 7: Test encrypted backup of SQL dumps
duplicity --encrypt-key <gpg_public_key_id> --exclude="**" --include="/var/backups/sql" / gdocs://<google_account_name>@gmail.com/backup
Step 8: Put the database dump and Duplicity command together into a cron script
Set up daily incremental backup
This will run every night, creating incremental backups. Duplicity by default tries to back up ALL files on disk, which we probably don't want on a VPS. So, we use the --exclude parameter so that it ignores everything except the directories we include via --include. You can use multiple --include parameters to include multiple directories.
sudo nano /etc/cron.daily/duplicity.inc
duplicity.inc:
#!/bin/sh
test -x $(which duplicity) || exit 0
. /root/.passphrase
export PASSPHRASE
export GOOGLE_DRIVE_SETTINGS=/home/<username>/.duplicity/credentials
# This lets the script find your GPG keys when it is running as root
export GNUPGHOME=/home/<username>/.gnupg
# Run MySQL dump. This will create a weekly file, and then update the file every additional time this script is run
mysqldump --single-transaction --routines --events --triggers --add-drop-table --extended-insert -u backup -h 127.0.0.1 -p<password> --all-databases | gzip -9 > /var/backups/sql/all_$(date +"%Y_week_%U").sql.gz
# Performs an incremental backup by default. Since we create a new dump file every week, we have a history
# of weekly snapshots, and the current week is incrementally updated each day.
duplicity --encrypt-key <gpg_public_key_id> --include="/var/backups/sql" --exclude="**" / gdocs://<google_account_name>@gmail.com/backup
Set permissions:
chmod 755 /etc/cron.daily/duplicity.inc
Set up a weekly full backup
This will run once a week, creating a full backup and clearing out all but the last three full backups to save space. Again, you can adjust this frequency and number of backups to retain, to your situation.
sudo nano /etc/cron.weekly/duplicity.full
duplicity.full:
#!/bin/sh
test -x $(which duplicity) || exit 0
. /root/.passphrase
export PASSPHRASE
export GOOGLE_DRIVE_SETTINGS=/home/<username>/.duplicity/credentials
# This lets the script find your GPG keys when it is running as root
export GNUPGHOME=/home/<username>/.gnupg
# Run MySQL dump. This will create a weekly file, and then update the file every additional time this script is run
mysqldump --single-transaction --routines --events --triggers --add-drop-table --extended-insert -u backup -h 127.0.0.1 -p<password> --all-databases | gzip -9 > /var/backups/sql/all_$(date +"%Y_week_%U").sql.gz
# Create a brand new full backup, which contains all the weekly dumps located in /var/backups/sql
duplicity full --encrypt-key <gpg_public_key_id> --include="/var/backups/sql" --exclude="**" / gdocs://<google_account_name>@gmail.com/backup
# Clean out old full backups
duplicity remove-all-but-n-full 3 --force gdocs://<google_account_name>@gmail.com/backup
Set permissions:
chmod 755 /etc/cron.weekly/duplicity.full
If your tasks in these cron.* directories aren't being run automatically for some reason (often times, due to problems with permissions), you can add these tasks to the root cron file:
sudo crontab -e
Add the lines (try to pick odd times):
# Incremental backup every day at HH:MM
MM HH * * * /etc/cron.daily/duplicity.inc >> /var/log/backups.log 2>&1
# Full backup every Saturday at HH:MM
MM HH * * 6 /etc/cron.weekly/duplicity.full >> /var/log/backups.log 2>&1
Save and exit.
Step 9: Test and verify backup
You can try downloading your backup from Google Drive back into ~/test:
sudo duplicity gdocs://<google_account_name>@gmail.com/backup ~/test