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 gzip
##mysql database has no root password. This script uses 'root' MySQL user without password as no 'root' password is set.
##This is not good practice. User with more restrictive permission should be used.

#set database user
DBUSER=root

#set backup path
BACKUPPATH=/backups/magento/mysql/

#set date string for file name
DATESTR=`date +%A_%d-%m-%Y_%H-%M-%S`

#set file extension
EXT=.sql.gz

#Get datanase list from MySQL server
echo "Geting list of databases from MySQL server"
databases=(  `echo "SHOW DATABASES;" | mysql -u root` )

#set databses to be excluded
excludedDatabases=( Database performance_schema information_schema )

#set start date
STARTDATE=$(date)

echo "########################################### Start Database Backup ##############################################"

echo $STARTDATE": Starting database backups, trying to backup all databases"

#checks if the datavase is in excluded list. database name is passed as first param
#EXAMPLE
#  isExCludedDb=$(isExDb <database_name>)
function isExDb()
{
    local isXdb="false"
    for exdb in "${excludedDatabases[@]}"
    do
        if [ "$1" == "${exdb}" ]; then
            isXdb="true"
            break
        fi
    done

    echo "$isXdb"
}

#Loop through all databases
for db in "${databases[@]}"
do
    #check if the database in excluded database list
    if [ $(isExDb "$db") == "true" ]; then

        #do not backup if the database is in excluded list
        echo $(date)': Excluded database: '$db

    else
        #back up database
        echo $(date)': Backing up database: '$db

        #generate backup file path
        newbackuppath=$BACKUPPATH$db"_"$DATESTR$EXT

        #backup database
        mysqldump -u root --opt --routines --triggers --quick  $db | gzip > $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
            echo 'Failed to backup, database: '$db
            #check and delete file if exist
            if [ -f $newbackuppath ]; then
                rm -f $newbackuppath
                echo $(date)': Removed failed backup file'$newbackuppath

                #create simple errorlog file to indicate database backup failed
                errLogPath=$newbackuppath".error.log"
                echo $(date)': Failed to create database backup, something went wrong in mysqldump' > $errLogPath
            fi

        else

            #show success message, if backup succeeded.
            echo $(date)': Backup successfull, database: '$db' path: '$newbackuppath

        fi
    fi
done

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

#TODO update database list before deploy

Thanks! Great script!

nice work !

Great script, Works nice, except for 2 of my servers request the root password, how can i avoid that?
Another questiion, how can i do to put the date on the filenames in spanish?

@samuelmf The easiest way to do this is to use a client section of the ~/.my.cnf file, and add the credentials inside that file.

[client] 
user=root
password=somepassword

how can i do to put the date on the filenames in spanish?

Try:

SET lc_time_names = 'es_ES';