Skip to content

Instantly share code, notes, and snippets.

@sayz
Last active October 15, 2017 12:42
Show Gist options
  • Save sayz/d230df1dcee04775b0e987bedeb6e72f to your computer and use it in GitHub Desktop.
Save sayz/d230df1dcee04775b0e987bedeb6e72f to your computer and use it in GitHub Desktop.
#!/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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment