You always have to get these things fixed from the top.
Is the current backup strategy backed by and understood by management? If not, it's useless.
The executive management needs to know about the problems and what risks are involved (losing financial data that you need to bring out legally to survive, or customer data that has taken years to collect?) and weigh that in deciding on actions, or deciding on letting someone (like you) take action.
If you can't get to management, try business controllers or other financial positions where data retrieval and its integrity is of high importance to the company's reports. They in turn can "start the storm" if needed...
I do this, and it works for me (as in, the backups run fine, and I have done several restores). I use bacula, which supports it. For my money, pointers to getting this right include:
1) The decryption key is kept (unencrypted) on a CD-R, not in my head. There are several copies of the CD-R, and none of them is with me. I only need to do restores once every few months, and frankly I'd probably forget a password that I used that infrequently. This also means my security isn't limited by the length of a memorable passphrase.
2) The backup software needs to support this already; don't start hacking this into your favourite tool, because you may get it wrong, and you won't know until it's vital that it work (ie, restore time).
3) You've a point about bit-flips, but those can ruin any backup. I clean my tape heads every time the drive requests it, rotate the tapes every few years, and above all keep lots of incrementals. If a bit-flip really did ruin yesterday's incremental, I can always go back to the day before, which will save most of my bum.
4) Document the restore procedure. Encryption always complicates things, more if it's done well, and you don't want to have to rediscover the wheel when you're under pressure to get the accounts database back. I wrote a short README with lots of detail that's very specific to my setup (a real step-by-step guide, all pathnames explicitly listed, that sort of thing) and it's burned to the same CDs as the decryption keys.
5) Above all, test it a lot. You should be testing your restores regularly anyway, but once you've done something clever like this it becomes absolutely critical that you have confidence that things are working as they should.
Pros arising from this practice include not having to care when offsite storage loses a tape or two, as - being only human - they do from time to time; securely destroying old tapes is easy (throw in bin); and having all my file systems encrypted on-disc is no longer undermined by having a stack of unencrypted backup tapes in the fire safe next door.
Best Answer
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 avar/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):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:
popt
libbz2
librsync
Run the following commands:
Update Duplicity
The current stable version as of October 6, 2016 is 0.7.10.
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:
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:
Create config file:
PyDrive uses this file to store credentials and configuration settings for the Google API.
nano /home/<username>/.duplicity/credentials
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:
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 withpub
. See https://security.stackexchange.com/a/110146/74909.Add the passphrase that you set for your GPG key to a secret file:
.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:
Then on your local machine:
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
Step 8: Put the database dump and Duplicity command together into a
cron
scriptSet 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:
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:
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):
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