#!/bin/bash

# A shell script to backup a MySQL database locally.
# Optionally uses SpiderOak to sync backups remotely.
#
# OPTIONAL SETUP
# 1) Optional setup in order for user passwordless mysqldumps
#
# $ vim ~/.my.conf
#
#    [mysqldump]
#    user=<DB_USER>
#    password=<DB_PASSWORD>
#
# $ chmod 0600 ~/.my.conf
#
# 2) Optional cron setup to run backup script daily at midnight
#
# $ crontab -e
# 0 0 * * * sudo -u root sh -c "<SCRIPT_PATH>/mysql-backup.sh" >/dev/null 2>&1

# Database config
db_name="<DB_NAME>"
db_host="<DB_HOST>"
db_user="<DB_USER>"

# Path to backup/sync directory
path="/root/SpiderOak Hive/"

cd "${path}"

# Set the filename date prefix
now=`date +"%Y-%m-%d"`

# Remove backup files older than 30 days
find "${path}" -type f -name '*.sql' -mtime +30 -exec rm -f {} \;

# Exclude certain tables to save on filesize
exclude=(
<TABLE NAME>
<TABLE NAME>
<TABLE NAME>
)

# Build the table ignore variable
ignore=''
for tbl in "${exclude[@]}"
do :
   ignore+=" --ignore-table=${db_name}.${tbl}"
done

# Backup database
mysqldump -u ${db_user} -h ${db_host} ${ignore} ${db_name} > "${now}-${db_name}.sql"

# Securely sync to SpiderOak
SpiderOakONE --batchmode

ls -la