#!/bin/bash

#@@@@@@@@@@@@@@@@@@@@@@@@@@@@#
# PLEASE UPDATE CONFIG BELOW #
#@@@@@@@@@@@@@@@@@@@@@@@@@@@@#

# local file backup directory
path='/home/hary/backup/db'
# mysql config
dbuser="root"
dbpass=""
dbhost="localhost"
dbport="3306"
# ftp/sftp config
ftptype="sftp" #ftp
ftppath="/home/hary/backup/db"
ftpuser="hary"
ftppassword=""
ftpserver="192.168.88.24"
ftpport="4637"
# db backup
dbnames="asbanda_ib mobilesgo"

# backup file lifetime before delete
lifetime=7

#@@@@@@@@@@@@@@@@@@@@@@@@@@@@#
# PLEASE UPDATE CONFIG ABOVE #
#@@@@@@@@@@@@@@@@@@@@@@@@@@@@#

sql='.sql'
archive='.gz'
tgl=`/bin/date +%Y%m%d`
jam=`/bin/date +%H%M%S`
tgljam=${tgl}_${jam}
for db in ${dbnames}; do
     filepath=${path}/${db}
     echo "Checking file lifetime ${filepath}"
     #find ${filepath} -maxdepth 1 -type d -mtime +7 -exec rm -rf {} \;
     find ${filepath} -maxdepth 1 -type f -mtime +${lifetime} -exec rm -rf {} \;
     filenamesql=${db}_${tgljam}${sql}
     filenamezip=${filenamesql}${archive}
     if [ ! -d ${filepath} ]; then /bin/mkdir -p ${filepath}; fi
     cd ${filepath}
     echo "Create database backup ${filenamezip}"
     mysqldump --host=${dbhost} --port=${dbport} --user=${dbuser} --password=${dbpass} --routines --single-transaction --lock-tables=false --quick ${db} | gzip -c > ${filepath}/${filenamezip}
     #touch ${filenamezip}
     echo "Upload database backup ${filenamezip}"
     if [ "${ftptype}" == "ftp" ]
          then
          lftp -u ${ftpuser},${ftppassword} -p ${ftpport} -e "set ftp:passive-mode on; mkdir -p ${ftppath}/${db}/${tgl}; cd ${ftppath}/${db}/${tgl}; mput ${filepath}/${filenamezip}; quit" ${ftpserver}
     elif [ "${ftptype}" == "sftp" ]
          then
          lftp sftp://${ftpuser}:${ftppassword}@${ftpserver} -p ${ftpport}  -e "mkdir -p ${ftppath}/${db}/${tgl}; cd ${ftppath}/${db}/${tgl}; put ${filepath}/${filenamezip}; bye"
     else
          echo "unknown ftp type ${ftptype}"
     fi
done