List

If you have a Google Apps account (a business account with more than 5 users or one provided by your school), you are given an “unlimited” amount of storage on Google Drive. In addition, there are great clients like Rclone that add features like encryption and a full rsync like feature set. This makes Google Apps a great platform to store nightly database dumps.

Installation

First, we need to install Rclone. After heading over to Rclone’s documentation on installation, all we need to do is:
wget https://rclone.org/install.sh
Open the install.sh file to make sure it is not malicious, and then run it as root. This will add rclone to your path to invoke from any directory.

Configuring Rclone

We need to add our Google Drive account as a remote endpoint. In your terminal, run rclone config. Press `n` to create a new remote. For the name, you can make it anything, but I will use gdrive. For the type, we are using Google Drive which is drive. For the application ID & Secret, you can fill in your own API keys from the Google API Console, but this is optional, just press return to skip these. Service Account Credentials is also an optional advanced field. I always chose to not use auto config, especially if I am setting up rclone over SSH. All this means is that you will have to copy and paste a link from the terminal into your web browser. 

After opening the link, you will need to login to your Google account and give rclone access. Copy and paste the access token from Google into your terminal window. Choose no on the team drive prompt. If all looks good, type y to save your config.

Configuring an Encrypted Remote

We now have our Google Drive account configured, but we should also set up encryption. To do this, run rclone config and create a new remote with the type crypt. You will see this prompt:

Remote to encrypt/decrypt.
Normally should contain a ':' and a path, eg "myremote:path/to/dir",
"myremote:bucket" or maybe "myremote:" (not recommended).
remote>

This is asking where you want your encrypted container to go. “crypt” is only a container for encryption and needs to be inside another remote (the Google Drive remote we set up earlier). I will be using gdrive:encrypted where the first part is the name of my remote earlier and the second part being the folder I want this container to be located.

I also encrypt filenames with the standard encryption and encrypt directory names. I also let rclone generate a password/salt and be sure to write down these values before finishing the process.

Setting Up the Backup

What we need now is a tool that can:

  • Login to our database
  • Quickly dump the tables without locking
  • Upload these dumps to our encrypted rclone remote
  • Delete the dump locally

I have already created a backup script for this purpose and have uploaded it to my Github. This script will not lock tables while backing up data, but keep in mind that any writes to your other tables made recently will not be reflected. If this is important to you, take a look at some HotBackup solutions for your database.

Configure the variables at the top of the script and give it a run. If your database files appear in your rclone crypt, then you have set everything up correctly.

Using Crontab to Automatically Backup Our Database

Using the same user you have configured rclone with, open up the crontab using crontab -e. Scroll to the bottom of the file, and add this rule:

00 06 * * * /bin/bash /home/me/sql/full.sh

Where /home/me/sql/full.sh is the path to the script you used earlier. This will run a full backup at 06:00 local server time every day to your rclone remote.

I hope this tutorial has been helpful, and of course be sure to check and test your backups regularly. =)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Posts

1 2 3 4
February 28th, 2018

Concluding the February Challenge

Writing every day for the month of February has been an experience, and has changed my perspective on writing tremendously. […]

February 27th, 2018

Cloudflare2DB

Cloudflare stats are very useful, but they expire and can’t be tracked over a long period of time. That’s why […]

February 26th, 2018

Nightly Database Backups to Google Drive

If you have a Google Apps account (a business account with more than 5 users or one provided by your […]

February 25th, 2018

Sublime Text

Sublime Text is a really powerful text editor and includes tons of functionality relevant to programming. I will dive into […]

February 24th, 2018

Great Resources for Student Developers

Being enrolled in a university comes with its pros and cons (a topic itself worthy of many other blog posts), […]

February 23rd, 2018

What is MVC?

MVC is a term thrown around lots of software development projects. Laravel, Zend, CodeIgniter, Ruby on Rails, Django, iOS, and […]

February 22nd, 2018

Getting Started With Phalcon PHP

To get started with Phalcon, I already have my server stack setup with Ubuntu 16.04, Percona MySQL, Nginx, and PHP […]

February 21st, 2018

What is Phalcon?

Phalcon is a modern PHP framework with the typical feature set you’d expect. Database ORM, error handling, MVC architecture, and […]

February 20th, 2018

Setting Up a PHP Development Environment

If you do PHP development, moving to a PHP IDE is extremely beneficial especially when debugging. PHPStorm is a great […]