Bash scripts to backup all databases in a MySQL server with the option to exclude some databases.

bash_backup_all_mysql_databases.sh

#!/usr/bin/env bash
#
# This script backups selected databases in local MySQL server
#
# REQUIREMENTS
#     - mysqldump bzip2
#     - MySQL or MariaDB configured with one of theirs options : 
#           - auth_socket plugin for root access (modern default's auth method for root account)
#           - /etc/mysql/debian.cnf with a valid privileged account
#
LDIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
echoerr() { echo "$@" 1>&2; }

# set backup path
BACKUPPATH="$1"
if [ -z "$BACKUPPATH" ]
then
    BACKUPPATH="$LDIR"
fi
# set date string for file name
DATESTR=$(date +%Y-%m-%d-%Hh%Mm%S)
# set file extension
EXT=.sql.bz2

# --defaults-file=/etc/mysql/debian.cnf

# Check MySQL / MariaDB connection method
MYSQLCOOPTS=""
if mysql -u root -e "SELECT 1" > /dev/null 2>&1
then
    MYSQLCOOPTS="-u root"
elif mysql --defaults-file="/etc/mysql/debian.cnf" -e "SELECT 1" > /dev/null 2>&1
then
    MYSQLCOOPTS="--defaults-file=/etc/mysql/debian.cnf"
else
    echoerr "No valid connexion method found"
    exit 1
fi
function getdate() {
    echo $(date +%Y-%m-%d-%Hh%Mm%S)
}


# Get datanase list from MySQL server
echo "Geting list of databases from MySQL server"
databases=( $(mysql $MYSQLCOOPTS -B -N -e "SHOW DATABASES;") )

# Set databses to be excluded
excludedDatabases=( performance_schema information_schema sys )

# Set start date
STARTDATE=$(getdate)

echo "########################################### Start Database Backup ##############################################"
echo $STARTDATE": Starting database backups, trying to backup all databases"

# Checks if the database is in excluded list. database name is passed as first param
# EXAMPLE
#   if isExDb "$db"; then echo "Excluded"; fi
function isExDb()
{
    for exdb in "${excludedDatabases[@]}"
    do
        if [ "++${1}++" == "++${exdb}++" ]; then
            # 0 = success
            return 0
        fi
    done
    return 255
}

# Loop through all databases
for db in "${databases[@]}"
do
    # check if the database in excluded database list
    if isExDb "$db"
    then
        echo $(getdate)': ignoring excluded database: '$db
    else
        echo $(getdate)': Backing up database: '$db

        newbackuppath=${BACKUPPATH}/${db}"_"${DATESTR}${EXT}
        mysqldump $MYSQLCOOPTS --opt --routines --triggers --quick  $db | bzip2 --best > "$newbackuppath"

        # check if database backup succeeded or failed
        if [ "${PIPESTATUS[0]}" -ne "0" ]
        then
            # if database backup failed, delete the file created during backup attempt
            echoerr 'Failed to backup, database: '$db
            # check and delete file if exist
            if [ -f "$newbackuppath" ]
            then
                rm -f "$newbackuppath"
                echo $(getdate)': Removed failed backup file '$newbackuppath

                #create simple errorlog file to indicate database backup failed
                errLogPath=$newbackuppath".error.log"
                echoerr $(getdate)': Failed to create database backup, something went wrong in mysqldump'
            fi
        else
            #show success message, if backup succeeded.
            echo $(getdate)': Backup successfull, database: '$db' path: '$newbackuppath
        fi
    fi
done

echo $(getdate)": Database backup finished, started at: "$STARTDATE
echo "-------------------------------------------------End-------------------------------------------------------------"