Backup Latest MySQL Dumps Every Date of the Month to Google Drive

grive-cron.sh

# Adapted from https://petermolnar.net/database-backups-to-google-drive/
# Works for linux systems
# 
# 1. Install grive https://github.com/Grive/grive
# 
# for ubuntu/debian use: 
# $ sudo add-apt-repository ppa:nilarimogard/webupd8
# $ sudo apt-get update
# $ sudo apt-get install grive
# 
# 
# 
# 2. Create a directory for grive and authenticate grive:
# 
# $ mkdir /path/to/your/grive-folder
# $ cd /path/to/your/grive-folder
# $ grive -a
# 
# This gives you a link: visit it, authenticate it, that's all. 
# From now on, you only need these lines to sync your data:
# $ cd /path/to/your/grive-folder
# $ grive
# 
# 
# 
# 3. Make your cron script
# 
# $ nano /path/to/your/grive-cron.sh
# 
# The script below makes MySQL dumps based on the current month's date number, so you will always have 1 month daily backup. 
# - Change the TODAY var if you want a different frequency.
# - Change /path/to/your/grive-folder
# - Change /path/to/your/grive-key.txt
# 
# 
# 
# 4. make your file executable
# 
# $ chmod +x /path/to/your/grive-cron.sh
# 
# 
# 
# 5. test it
# 
# $ sh /path/to/your/grive-cron.sh
# 
# 
# 
# 6. Save the password to run the cron job:
# 
# On Debian systems, there's a nearly root MySQL user created for the system, called debian-sys-maint. It's password is in the # file /etc/mysql/debian.cnf, so we can easily use that instead of putting a cleartext password into the code. 
# Copy the password to a file that you will save to a very secure path:
# 
# $ sed -n -e 's/^password = \(.*\)/\1/p' /etc/mysql/debian.cnf | tail -n 1 > /path/to/your/grive-key.txt
# 
# 
# 
# 7. Schedule the cron job to run this file daily 
# 
# $ crontab -e
#
# Add the following line to run the file once every midnight
# 
# 00 00 * * * /path/to/your/grive-cron.sh
# 
# 
# Thats it! your cron job is installed!



#!/bin/bash

USER=`cat /etc/mysql/debian.cnf | grep -m1 user | awk '{print $3}'`
PASS=`cat /etc/mysql/debian.cnf | grep -m1 pass | awk '{print $3}'`

SYSNAME=`hostname`
GRIVEROOT="/path/to/your/grive-folder"
BACKUPROOT="$GRIVEROOT/backup/$SYSNAME/mysql"
TODAY=$(date +"%d")
BACKUPDIR="$BACKUPROOT/$TODAY"
PASSWDFILE="/path/to/your/grive-key.txt"

GRIVEENABLED=1
LOGENABLED=1

log_message () {
        if [ "$LOGENABLED" -ne 0 ]; then
                echo $1
        fi
}

DATABASES=`mysql -u $USER -p$PASS -Bse 'show databases'`

for DB in $DATABASES; do

	log_message "database $DB";

	# skip system tables
	if [ "$DB" = "information_schema" ] || [ "$DB" = "performance_schema" ] || [ "$DB" = "mysql" ]; then
		log_message "skipping system database $DB"
		continue
	fi

	# create backup dir
	if [ ! -d $BACKUPDIR ]; then
		log_message "creating backup dir $BACKUPDIR"
		mkdir -p $BACKUPDIR
	fi

	# dump and compress SQL database
	SQLFILE="$BACKUPDIR/$DB.sql.gz"
	ENCFILE="$SQLFILE.enc"
	log_message "dumping and compressing database $DB"
	mysqldump -u $USER -p$PASS $DB | gzip -9 > $SQLFILE

	# encrypt SQL dump
	log_message "encrypting $SQLFILE"
	openssl des3 -in $SQLFILE -out $ENCFILE -pass file:$PASSWDFILE
	# to decrypt, use the following:
	# $ openssl des3 -d -in $ENCFILE -out $SQLFILE -pass file:$PASSWDFILE
	# that is:
	# $ openssl des3 -d -in backup.sql.gz.enc -out backup.sql.gz -pass file:key.txt

	# delete unencrypted dump
	log_message "encryption finished; removing original dump"
	rm $SQLFILE
done


if [ "$GRIVEENABLED" -ne 0 ]; then
        log_message "   encryptions finished; starting Google Drive sync"
        cd $GRIVEROOT
        grive
fi