Forked from shakhmehedi/bash_backup_all_mysql_databases.sh
Last active
February 2, 2024 13:48
-
-
Save Zeuh/9de30435d9ab3a017c17de0583d7bd58 to your computer and use it in GitHub Desktop.
Bash scripts to backup all databases in a MySQL server with the option to exclude some databases.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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-------------------------------------------------------------" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment