Backups MYSQL Databases¶

#!/usr/bin/env bash
# gunzip -c file.sql.gzip > dump.sql
# gpg --decrypt-files /tmp/file.gpg
#
# Create file ~/.my.cnf and add following lines in it and replace mysqluser & mysqlpass values.
#
# [client]
#   user=mysqluser
#   password=mysqlpass
#

dateTime=$(date +%Y-%m-%d-%H-%M-%S)
dumpsDir="/backups/mysql"
oldDays=30

excludedBases=${1}
excludedBaseBases="information_schema|performance_schema|mysql"

if [ -n "${excludedBases}" ]; then
    excludedBases=$(echo "${excludedBases}" | sed 's/ /|/g' | sed 's/,/|/g' | sed 's/;/|/g')
    excludedBases="${excludedBaseBases}|${excludedBases}"
else
 excludedBases="${excludedBaseBases}"
fi

databases=($(mysql -Bse "show databases;" | egrep -v "${excludedBases}"))

if [ ! -d "${dumpsDir}" ]; then
    mkdir "${dumpsDir}"
    chmod ug+rw "${dumpsDir}"
    chmod o-rwx "${dumpsDir}"
fi


for base in "${databases[@]}"
do
    cd "${dumpsDir}"
    echo "Backup Database: ${base}"

    if [ ! -d "${base}" ]; then
        mkdir "${base}"
        chmod ug+rw "${base}"
        chmod o-rwx "${base}"
    fi

    cd "${base}"

    name="${base}-$(hostname)-${dateTime}"

    mysqldump "${base}" --set-gtid-purged=OFF > "${name}.sql"
    tar -cjf "${name}.tgz" "${name}.sql" > /dev/null 2>&1
    rm "${name}.sql"

    countFiles=$(ls "./" | wc -l)

    if [ ${countFiles} -ge ${oldDays} ]; then
        echo "Delete old backups (Current count files: ${countFiles})"
        find ./ -name "*.tgz" -mdateTime +${oldDays} -type f -delete > /dev/null 2>&1
    else
        echo "No old dumps files"
    fi

done

exit 0