#!/bin/bash

#  Sets the script to exit when any error occurs
set -e

lbackup_dir="/tmp"
rbackup_dir="/home/${BKP_USER}/backup/"

# define backup filename using date-time based
file_name="${MYSQL_DBNAME}-$(date +%d.%m.%Y-%H.%M)"

# Uses mysqldump to backup the database from MySQL server and redirecting
# the output to backup file.
# Chain the compression and removal of the original backup file in a single
# command successful sequence of statements after the mysqldump
echo "dumping database data and compressing..."
mysqldump --host=${MYSQL_HOST} --user=${MYSQL_USER} --password=${MYSQL_PASSWD} ${MYSQL_DBNAME} > ${lbackup_dir}/${file_name}.sql \
  && tar -czf ${lbackup_dir}/${file_name}.tar.gz ${lbackup_dir}/${file_name}.sql \
  && rm -f ${lbackup_dir}/${file_name}.sql
echo "dump and compression.... done"

# Waits 10 seconds do send over FTP after compression is finished
sleep 10

# Sends the compressed backup to FTP server through SFTP
# SFTP is slower, but has been more reliable in poor networks
# Uses rsa key file for SSH authentication
echo "sending backup over SFTP"
sftp -i ${HOME}/.ssh/id_rsa ${BKP_USER}@${BKP_HOST}:${rbackup_dir} <<SCRIPT
put ${lbackup_dir}/${file_name}.tar.gz ${file_name}.tar.gz
SCRIPT
echo "upload to SFTP.... done"