#!/bin/bash

TIME=$(date "+%Y-%m-%d_%H-%M-%S")

## Database connection details
PASSWORD=XXXXXX
HOST=XXXX
USER=XXXXX
DATABASE=XXXXX

## Folders for storaging backups 1. writes to local then moves to remote use sshfs for mouting remote direct
## makes like lifers for checking if connection just check file exis
LOCALLOCATION=/home/john/css.direct/_live/_backups/sql/
REMOTELOCATION=/mnt/xoren/storage/users/css/sql/

DB_SFILE=${LOCALLOCATION}${TIME}_S_css_data.sql
DB_DFILE=${LOCALLOCATION}${TIME}_D_css_data.sql
DB_D_BOOKING_FILE=${LOCALLOCATION}${TIME}_D_BOOKINGS_css_data.sql


EXCLUDED_TABLES=(
tlbLog
tblBooking_dups_logs
tblHols
error_500
tblBooking
)

IGNORED_TABLES_STRING=''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done

echo "Dump structure"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} --single-transaction --no-data ${DATABASE} > ${DB_SFILE}
echo "Dump content"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} ${DATABASE} tblBooking > ${DB_D_BOOKING_FILE}
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} ${DATABASE} --no-create-info ${IGNORED_TABLES_STRING} >> ${DB_DFILE}

echo 'Compress sql'
gzip  ${LOCALLOCATION}*.sql


if [ -d ${REMOTELOCATION} ]; then
	echo ""
	echo 'Moved sql'
	mv ${LOCALLOCATION}*.sql.gz ${REMOTELOCATION}

	export TERM=xterm-256color

	DAYS=60;

	find ${REMOTELOCATION}* -mtime +${DAYS} -exec rm -f {} \;
else
	echo "Error: Can't connect to the remote location"
fi

exit 1