#!/bin/bash
# Author: Carlo Abiog
# Date Created: July 4, 2024
# Date Modified: June 3, 2025
# March 14, 2025 Added ajels
# March 18, 2025: Added actamanilana
# March 20, 2025: Added pjahs, tomas
# June 3, 2025: Added medicine
# New MySQL Server (dbweb26.ust.edu.ph) Backup Script

# 20150131
# DATE_DIR=$(date +%Y%m%d)

# Week Number
echo
START=$(date +%s)
echo "START TIME: "`date '+%F %r'`
DATE_DIR=$(date +%Y_%V_mysql_dbweb26)
echo
echo "[1/4] backing up database and configuration files ... Please Wait"
cd /
mkdir -p "/${DATE_DIR}"
mkdir -p "/${DATE_DIR}/etc"
mkdir -p "/${DATE_DIR}/bin"
cp -pr /etc/my.cnf "/${DATE_DIR}/etc/"
cp -pr /etc/crontab "/${DATE_DIR}/etc/"
cp -pr /etc/hosts "/${DATE_DIR}/etc/"
cp -pr /bin/backupmysql.sh "/${DATE_DIR}/bin/"

CREDENTIAL_FILE="/home/credentials.txt"
MCREDENTIALS="--defaults-extra-file=$CREDENTIAL_FILE"

MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

BAK="/${DATE_DIR}"
# check if directory does not exists then create else delete
DBS="$($MYSQL $MCREDENTIALS -Bse 'show databases' | grep -Ev "(Database|information_schema|performance_schema)")"
for db in $DBS
do
  FILE=$BAK/$db.sql.gz
$MYSQLDUMP $MCREDENTIALS $db | $GZIP -9 > $FILE
done
echo "[1/4] backing up database and configuration files ... Done"
echo
echo "[2/4] compressing backup folder ... Please Wait"
tar -czpf "${DATE_DIR}.tar.gz" "/${DATE_DIR}"
echo "deleting backup folder /${DATE_DIR} ... Please Wait"
rm -rf "/${DATE_DIR}"
echo "[2/4] compressing backup folder ... Done"
echo
echo "[3/4] encrypting backup file ... Please Wait"
gpg --batch -c --passphrase "pope francis" "/${DATE_DIR}.tar.gz"
echo "deleting backup file /${DATE_DIR}.tar.gz ... Please Wait"
rm -rf "/${DATE_DIR}.tar.gz"
echo "[3/4] encrypting backup file ... Done"
echo
echo "[4/4] transferring to backup server ... Please Wait"
## destination folder must be owned by the user connecting
scp -r "/${DATE_DIR}.tar.gz.gpg" backupadmin@10.1.16.21:/home/backupadmin/mysql_backup/
echo "deleting encrypted backup file /${DATE_DIR}.tar.gz.gpg ... Please Wait"
rm -rf "/${DATE_DIR}.tar.gz.gpg"
echo "[4/4] transferring to backup server ... Done"
echo
END=$(date +%s)
echo "END TIME: "`date '+%F %r'`
echo
HOUR=$(((END - START)/3600))
MIN=$((((END - START) - ((HOUR * 3600)))/60))
echo "BACKUP DURATION: ${HOUR} hours and ${MIN} minutes"
echo