Encrypt Database Backup And Save On Google Cloud Platform

To improve my backup strategy I add syncing encrypted backups to GCP with a CronJob, OpenSSL, and a simple Bash script. This Tutorial will explain how everyone can implement my personal strategy.

Introduction

After I set up my database backup using a cronjob I was thinking about a good solution to have also backups in case of a server crash.

You can read how I created my own simple backup strategy here:

The first idea was something like a rsync job which runs on my laptop and downloads the DB every day. But this is not a really good solution so I thought about one including cloud storage. Because GCP has $300 in free credits at the moment I choose it over AWS.

I created a free account here and then I created my first Storage Bucket.

Installing Google Cloud SDK

After the initial setup within the cloud console was done I performed the following steps to connect my server to the google storage. If you are working with a Docker Swarm this procedure has to be repeated on EVERY worker within my swarm. If you want to set up a Docker Swarm, you can read about it in another article from me.

Downloading Google Cloud SDK

curl -O https://dl.google.com/dl/cloudsdk/channels/rapid/downloads/google-cloud-sdk-353.0.0-linux-x86_64.tar.gz

Extract it

tar -xf google-cloud-sdk-353.0.0-linux-x86_64.tar.gz

Install the SDK

./google-cloud-sdk/install.sh

After the installation process is done you have to reconnect the shell!

Initialize the SDK

./google-cloud-skd/bin/gcloud init

During the initialization, I had to login into my google account

Find the bucket

gsutil ls

Now the machine is connected to GCP and I can use gsutil to backup my data.

Encryption

Now that I have a google connection on every server I could start to create the backup script. But I first gathered information on how to zip the database backups and store them in an encrypted way. Google should not have my database! I know: paranoia

I choose a procedure for encryption that uses a password file on the system (which I also downloaded to my machine). So I had to create a secure password and export it into a variable which then can be used within a script.

Generate a random password in a file:

head -c 100 /dev/urandom | strings -n1 | tr -d '[:space:]' | head -c 40 >> ~/.pass

Export file to .bashrc to use it while I am within the shell:

echo "export PASS=~/.pass" >> ~/.bashrc && source ~/.bashrc

Make file “safe”:

chmod 400 ~/.pass

With these .pass file I can encrypt and zip a folder with:

tar czf - . | openssl enc -e -aes-256-cbc -pbkdf2 -iter 100000 -salt -out archive.enc -pass env:PASS

To decrypt I have to use:

openssl enc -d -aes-256-cbc -pbkdf2 -iter 100000 -salt -in archive.enc -pass env:PASS | tar zxf - --directory backup_restore

Creating the backup shell script

The last step was to really create a shell-script which will then be executed within a cronjob. After researching and testing this script was created:

#!/bin/bash
TIME=$(date +%Y-%m-%d)
HOST=$(hostname)
BACKUPSAVEPATH=/YOUR_BACKUP_STORAGE_PATH/backups_encrypted/
BACKEDUPFILE=$BACKUPSAVEPATH/db-backups-$HOST-$TIME.enc
TARGET=gs://YOUR_BUCKET_HERE

cd /YOUR_BACKUP_STORAGE_PATH/ || exit 1
if [ -z "$(ls -A .)" ]; then
   exit 1
else
  tar czf - . | openssl enc -e -aes-256-cbc -pbkdf2 -iter 100000 -salt -out $BACKEDUPFILE -pass env:PASS
  /PATH_TO_GOOGLE_SDK_FOLDER/google-cloud-sdk/bin/gsutil -m rsync -r $BACKUPSAVEPATH $TARGET || exit 1
  rm -rf /YOUR_BACKUP_STORAGE_PATH/backups/*
fi

Important to know if you read/use this file:

1. Replace YOUR_BACKUP_STORAGE_PATH with your path where you store your backups. This folder has to have two subdirectories: backups_encrypted and backups
2. YOUR_BUCKET_HERE should be replaced with your bucket which you want to use. Find it with gsutil ls
3. PATH_TO_GOOGLE_SDK_FOLDER is the folder in which you installed the Google Cloud SDK

This script can now be used within a cronjob:

30 1 * * 6 export PASS=/root/.pass; /bin/sh /PATH_TO_SCRIPT/SCRIPTNAME.sh

This cronjob is executed at 1:30 every Saturday. Use crontab.guru for crontab creation

VERY IMPORTANT: You have to export the PASS variable before executing the script because a cronjob is not executed within the same bash in which you are as a user and then the prior exported PASS variable does not exist

ALSO VERY IMPORTANT: Remember to recreate the cronjob on every worker node within the swarm (if you use a Docker Swarm). Otherwise, data will be lost.

Closing Notes

Within this simple How-To, I explained how I used Google Cloud SDK to save an encrypted zip file in my Google Bucket here.

I have another Tutorial that explains How you can upload files to Amazon. You can copy the encryption part from this tutorial and combine it with the file upload to S3:

I hope you find this article helpful and are also able to save your valuable backup within the Cloud. I would love to hear your thoughts and if you have any questions please contact me.

Connect with me on Twitter, LinkedIn, Medium, and GitHub!