db_backup.sh

#!/usr/bin/env bash

set -o errexit
exec &>> /var/log/maria_db_backup.log

# Options
BACKUP_PATH="/opt/db_backups"
DATE=$(/bin/date +"%d-%b-%Y")

logger ()
{
    echo "$(/bin/date +"%d-%b-%Y %H:%M:%S") $1"
}

logger "Starting mariadb backup on ${BACKUP_PATH}"

# Database credentials
USER="root"
PASSWD="$(/usr/bin/openssl enc -base64 -d <<< YnVyYWRhIHBhcm9sYSB5b2sga2FuZMSxcmTEsW0gOi0pCg==)" # simply hide password from the script
DB_NAME="employees"

# Create backup directory if it does not exist
[ -d ${BACKUP_PATH} ] || mkdir -p ${BACKUP_PATH}

logger "Getting tables from DB"
# Parse tables from db that defined above
TABLES=$(/usr/bin/mysql --user=${USER} --password=${PASSWD} --execute="show tables in employees" | \
/usr/bin/awk "{print $2}" | /bin/grep --invert-match --ignore-case "tables_in"| /usr/bin/tr '\n' ' ')


logger "Dump tables into SQL file"
for TABLE in ${TABLES}
do
  /usr/bin/mysqldump --user=${USER} --password=${PASSWD} ${DB_NAME} ${TABLE} > "${BACKUP_PATH}"/"${DB_NAME}"-"${TABLE}"-"${DATE}".sql
done

logger "Compressing SQL files"

/bin/tar -czvf "${BACKUP_PATH}"/backup-"${DB_NAME}"-"${DATE}".tar.gz "${BACKUP_PATH}"/*-"${DATE}".sql >/dev/null 2>&1 && /bin/rm --recursive --force "${BACKUP_PATH}"/*-"${DATE}".sql

logger "${BACKUP_PATH}/backup-${DB_NAME}-${DATE}.tar.gz created"
logger "Finished mariadb backup"