Encrypt Database Backup And Save On Google Cloud Platform
Tags: tutorial, programming, devops, googlecloud
Published: 2022-11-12 22:48:20

[Paul Knulst](https://www.paulsblog.dev/)  in  [Programming](https://www.paulsblog.dev/tag/programming/) • Dec 7, 2021 • 4 min read

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: https://www.paulsblog.dev/everybody-needs-backups-a-lesson-learned-the-hard-way/

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](https://console.cloud.google.com/) 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](https://www.paulsblog.dev/docker-swarm-in-a-nutshell/).**

**Downloading Google Cloud SDK**
```bash
curl -O https://dl.google.com/dl/cloudsdk/channels/rapid/downloads/google-cloud-sdk-353.0.0-linux-x86_64.tar.gz
```

**Extract it**
```bash
tar -xf google-cloud-sdk-353.0.0-linux-x86_64.tar.gz
```

**Install the SDK**
```bash
./google-cloud-sdk/install.sh
```

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

**Initialize the SDK**
```bash
./google-cloud-skd/bin/gcloud init
```

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

**Find the bucket**
```bash
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:
```bash
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:
```bash
echo "export PASS=~/.pass" >> ~/.bashrc && source ~/.bashrc
```

Make file “**safe**”:
```bash
chmod 400 ~/.pass
```

With these `.pass` file I can encrypt and zip a folder with:
```bash
tar czf - . | openssl enc -e -aes-256-cbc -pbkdf2 -iter 100000 -salt -out archive.enc -pass env:PASS
```

To decrypt I have to use:
```bash
    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:
```bash
#!/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`:
```bash
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](https://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: https://www.paulsblog.dev/how-to-backup-your-files-to-aws-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.

This article was originally published on my blog at [https://www.paulsblog.dev/encrypt-database-backup-and-save-on-google-cloud-platform/](https://www.paulsblog.dev/encrypt-database-backup-and-save-on-google-cloud-platform/)

Feel free to connect with me on [my personal blog](https://www.paulsblog.dev), [Medium](https://medium.knulst.de), [LinkedIn](https://www.linkedin.com/in/paulknulst/), [Twitter](https://twitter.com/paulknulst), and [GitHub](https://github.com/paulknulst).

* * *

Did you find this article valuable? Want to support the author? (... and support development of current and future tutorials!). You can sponsor me on [Buy Me a Coffee](https://buymeacoffee.com/paulknulst) or [Ko-Fi](https://ko-fi.com/paulknulst). Furthermore, you can become a free or paid member by [signing up to my website](https://www.paulsblog.dev/#/portal). See the [contribute page](https://www.paulsblog.dev/contribute/) for all (free or paid) ways to say thank you!

* * * 

Photo by [panumas nikhomkhai](https://www.pexels.com/photo/the-server-room-tells-the-computer-server-that-is-working-continuously-convey-the-power-and-progress-12012981/) / [Pexels](https://www.pexels.com)