mysql-backup/backup-mysql.sh

#!/usr/bin/env bash

USER="root"
PASSWORD="secretpass"
GZIP_ENABLED="1"
OUTPUT_DIR="./output"
HOSTNAME="127.0.0.1"

USE_PASSWORD="0"
USE_HOSTNAME="0"

# automatically export all variables
set -a 
source .env
set +a

if [ "${DEBUG}" -gt "0" ]
then
	set -x
fi

PASSWORD_OPTIONS="-p${PASSWORD}"
HOST_OPTIONS="-h ${HOSTNAME}"

if [ "${USE_HOSTNAME}" -eq "0" ]
then
	HOST_OPTIONS=""
fi

if [ "${USE_PASSWORD}" -eq "0" ]
then
	PASSWORD_OPTIONS=""
fi

databases=$(mysql ${HOST_OPTIONS} -u ${USER} ${PASSWORD_OPTIONS} --batch -s -r  -e 'show databases')

for db in ${databases}; do
    if [[ "${db}" != "information_schema" ]] && [[ "${db}" != "performance_schema" ]] && [[ "${db}" != "mysql" ]] && [[ "${db}" != _* ]] && [[ "${db}" != "sys" ]]
    then
	DUMP_DIR="${OUTPUT_DIR}/${db}"
	DUMP_FILENAME="${DUMP_DIR}/$(date +%Y%m%d_%H:%M:%S).${db}.sql"
	mkdir -vp "${DUMP_DIR}"

        echo "Dumping database: ${db} into: ${DUMP_FILENAME}"
	if [ "${GZIP_ENABLED}" -gt "0" ]
	then
	        mysqldump ${HOST_OPTIONS} -u ${USER} ${PASSWORD_OPTIONS} --default-character-set=utf8mb4 --routines=true --triggers=true --databases ${db} | gzip > "${DUMP_FILENAME}.gz"
 	else
	        mysqldump ${HOST_OPTIONS} -u ${USER} ${PASSWORD_OPTIONS} --default-character-set=utf8mb4 --routines=true --triggers=true --databases ${db} > "${DUMP_FILENAME}"
	fi
    fi
done

set +x