shell/bash
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Files
master
bash/backup_mysql.sh
Bruno21 839d05bfbb 27-08-2025
2025-08-27 09:14:20 +02:00

290 lines
9.9 KiB
Bash
Executable File
Raw Permalink Blame History

#!/bin/bash
#
####################################################################
## Shell script to backup all MySql database with single User
##
## MySQL Database Backup Script
## Written By: Amol Jhod
## URL: https://www.itsupportwale.com/blog/learn-how-to-backup-up-all-mysql-databases-using-a-single-user-with-a-simple-bash-script
## Last Update: Apr 25, 2019
##
## For more scripts please visit : www.itsupportwale.com
##
#####################################################################
#####################################################################
#### Caution : This script is takes backup of all databases #########
############# on which the given user is having access. ###########
############## And Delete the backups older then BKP_DAYS ##########
#####################################################################
#####################################################################
########### You Have to Update the Below Values #####################
#####################################################################
#
#
# 04-09-2024 : backup, sending remote, notification OK
# 04-09-2024 : TO DO : test deleting local files after $BKP_DAYS
red="\033[1;31m"
greenbold="\033[1;32m"
green="\033[0;32m"
yellow="\033[0;33m"
yellowbold="\033[1;33m"
bold="\033[1m"
#bold_under="\033[1;4m"
underline="\033[4m"
reset="\033[0m"
dotenv () {
set -a
# shellcheck disable=SC1091
[ -f "$HOME/.env" ] && . "$HOME/.env" || echo -e "${red}\nNo .env file found ! Could'nt get update from Github.'.${reset}"
set +a
}
dotenv
#CREATE USER 'mysqlbackupuser'@'localhost' IDENTIFIED BY '34diK=[6]Zui';
#GRANT SELECT ON * . * TO 'mysqlbackupuser'@'localhost';
pass=$(gpg --quiet --decrypt $HOME/.my.cnf.gpg)
x=$(echo $pass | awk '{print $2}')
BKP_USER=${x:5}
y=$(echo $pass | awk '{print $3}')
BKP_PASS=${y:9}
machine=$(hostname | awk -F"." '{print $1}')
BKP_BASE_DIR=$(dirname "$0")
# Read l/p from .my.cnf
#BKP_USER="mysqlbackupuser" # Enter the username for backup
#BKP_PASS="$(cat $HOME/.password.txt)" # Enter the password of the backup user
#BKP_PASS="${PASSWORD}" # direnv (.envrc)
#
BKP_DEST="$HOME/Documents/MySQL" # Enter the Backup directory,change this if you have someother location
if [ ! -d $BKP_DEST ]; then mkdir $BKP_DEST; fi
#
## Note: Scripts will delete all backup which are older then BKP_DAYS##
#
# D:3 W:22 M:93
BKP_DAYS=1 # 3 Enter how many days backup you want,
BKP_WEEKS=21 # 3*7
BKP_MONTHS=93 # 3*31
#
########### Use This for only local server #############################
MYSQL_HOST="localhost"
#
#
########################################################################
########### Thats Enough!! NO NEED TO CHANGE THE BELOW VALUES ##########
########################################################################
#
##################### Get Backup DATE ##################################
#
#BKP_DATE="$(date +"%A_%d-%m-%Y_%H-%M-%S")";
# Pour les date en français: LC_ALL="fr_FR.UTF-8" date +"%B_%Y"
#lundi=date +%u
if [ $(date +%d) = "01" ]; then # 1er du mois => octobre_2019
BKP_DATE="$(date +"%B_%Y" | sed 'y/áàâäçéèêëîïìôöóûùúüñÂÀÄÇÉÈÊËÎÏÔÖÙÜÑ/aaaaceeeeiiiooouuuunAAACEEEEIIOOUUN/')";
elif [ $(date +%u) -eq 1 ]; then # lundi => S44_2019
BKP_DATE="$(date +"S%V_%Y")";
else
BKP_DATE="$(date +"%A_%d-%m-%Y_%H-%M-%S")"; # => Mercredi_30-10-2019_11-33-17
fi
#
########## Ignore these default databases shen taking backup ############
#
IGNORE_DB="information_schema mysql performance_schema sys"
#
########## Creating backup dir if not exist #############################
#
[ ! -d $BKP_DEST ] && mkdir -p $BKP_DEST || :
#
################# Autodetect the linux bin path #########################
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
#
curl -Is https://www.apple.com | head -1 | grep 200 1>/dev/null
if [[ $? -eq 1 ]]; then
echo -e "\n${red}No Internet connection !${reset}"
echo -e "No pushover notification !"
#exit 1
fi
send_pushover_notification() {
echo -e "Sending Pushover notification ..."
curl -s -F "token=$AIRBOOK_APP" \
-F "user=$USER_KEY" \
-F "title=$1" \
-F priority=2 \
-F html=1 \
-F retry=300 \
-F expire=86400 \
-F "message=$2" https://api.pushover.net/1/messages.json
[ $? -eq 0 ] && echo -e "${greenbold}\Pushover notification sent successfully !${reset}" || echo -e "${redbold}error sending Pushover notification !${reset}"
}
###################### Get database list ################################
#
DB_LIST="$($MYSQL -u $BKP_USER -h $MYSQL_HOST -p$BKP_PASS -Bse 'SHOW DATABASES')"
: <<'END_COMMENT'
END_COMMENT
#
for db in $DB_LIST
do
skipdb=-1
if [ "$IGNORE_DB" != "" ];
then
for i in $IGNORE_DB
do
[ "$db" == "$i" ] && skipdb=1 || :
done
fi
if [ "$skipdb" == "-1" ] ; then
BKP_FILENAME="$BKP_DEST/$db.$BKP_DATE.sql"
BKP_GZ_FILENAME="$BKP_DEST/$db.$BKP_DATE.sql.gz"
#
################ Using MYSQLDUMP for bakup and Gzip for compression ###################
#
# Dans les dossiers Backup: mkdir -p silverbook/Bases_MySQL
echo -e "Backup $db database..."
$MYSQLDUMP -u $BKP_USER -h $MYSQL_HOST -p$BKP_PASS -r$BKP_FILENAME $db
$GZIP -9 $BKP_FILENAME
f=$(basename "$BKP_GZ_FILENAME")
server1="ftp.cluster011.ovh.net:backup/$machine/Bases_MySQL/"
echo -e "Transfer $f to $server1"
scp "$BKP_GZ_FILENAME" funnymac@"$server1"
result1=$?
[ $? -ne 0 ] && send_pushover_notification "Backup MySQL Db" "Error when transfering $f on $s1 "
sleep 1
# NAS Synology: scp -O
server2="photos-nas.ovh:/volume1/Backup/$machine/Bases_MySQL/"
s2=$(echo $server2 | awk -F":" '{print $1}')
echo -e "Transfer $f to $server2"
scp -O -P42667 -p "$BKP_GZ_FILENAME" bruno@"$server2"
result2=$?
[ $? -ne 0 ] && send_pushover_notification "Backup MySQL Db" "Error when transfering $f on $s2 "
echo
fi
done
#########To delete all backup files older then BKP_DAYS #################
#
# Nettoyage serveurs locaux:
if find --version >/dev/null 2>&1 ; then
echo Using GNU date
d_duration="$BKP_DAYS"
w_duration="$BKP_WEEKS"
m_duration="$BKP_MONTHS"
else
echo Using BSD date
d_duration="$BKP_DAYS"d
w_duration="$BKP_WEEKS"d
m_duration="$BKP_MONTHS"d
echo "$d_duration"
echo "$w_duration"
echo "$m_duration"
fi
echo -e "Find daily backups..."
#find $BKP_DEST -name "*.sql.gz" -mtime +$BKP_DAYS | grep -v -E '(January|February|March|April|May|June|July|August|September|October|November|December)|(S\d{2}_\d{4})' | xargs rm -f
find $BKP_DEST -name "*.sql.gz" -mtime +"$d_duration" | grep -v -E '(janvier|fevrier|mars|avril|mai|juin|juillet|aout|septembre|octobre|novembre|decembre)|(Sd{2}_d{4})' | xargs rm -f
##gfind /Users/bruno/Documents/MySQL -mtime +93 -iregex '.*\(January\|February\|March\|April\|May\|June\|July\|September\|October\|November\|December\).*'
echo "----"
echo -e "Find weekly backups..."
find $BKP_DEST -name "*.sql.gz" -mtime +"$w_duration" | grep -E 'Sd{2}_d{4}' | xargs rm -f
##gfind /Users/bruno/Documents/MySQL -mtime +22 -regextype posix-extended -iregex '.*S[0-9]{2}_[0-9]{4}.*'
echo "----"
echo -e "Find monthly backups..."
#find $BKP_DEST -name "*.sql.gz" -mtime +$BKP_MONTHS | grep -E '(January|February|March|April|May|June|July|August|September|October|November|December)' | xargs rm -f
find $BKP_DEST -name "*.sql.gz" -mtime +"$m_duration" | grep -E '(janvier|février|mars|avril|mai|juin|juillet|août|septembre|octobre|novembre|decembre)' | xargs rm -f
#
: <<'END_COMMENT2'
# Nettoyage serveurs distants:
#916_dest="/volume1/Backup/SilverBook/Bases_MySQL"
# ssh dsm916e "find /volume1/Backup/SilverBook/Bases_MySQL -type f -name '*.sql.gz' | grep -P '(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday)' | xargs rm -f"
ssh dsm916e "set -o pipefail; find /volume1/Backup/$machine/Bases_MySQL -type f -mtime +$BKP_DAYS -name '*.sql.gz' | grep -P '(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday)' | xargs rm -f"
retCode[1]=$?
errMsg[1]="daily backup bases @ dsm916"
ssh dsm916e "set -o pipefail; find /volume1/Backup/$machine/Bases_MySQL -type f -mtime +$BKP_WEEKS -name '*.sql.gz' | grep -P 'S\d{2}_\d{4}' | xargs rm -f"
retCode[2]=$?
errMsg[2]="weekly backup bases @ dsm916"
ssh dsm916e "set -o pipefail; find /volume1/Backup/$machine/Bases_MySQL -type f -mtime +$BKP_MONTHS -name '*.sql.gz' | grep -P '(January|February|March|April|May|June|July|August|September|October|November|December)' | xargs rm -f"
retCode[3]=$?
errMsg[3]="monthly backup bases @ dsm916"
ovh_dest="/homez.528/funnymac/backup/$machine/Bases_MySQL"
# ssh ovh "find /homez.528/funnymac/backup/SilverBook/Bases_MySQL -type f -name '*.sql.gz' | grep -P '(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday)' | xargs rm -f"
ssh ovh "set -o pipefail; find $ovh_dest -type f -mtime +1 -name '*.sql.gz' | grep -P '(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday)' | xargs rm -f"
retCode[4]=$?
errMsg[4]="daily backup bases @ ovh"
ssh ovh "set -o pipefail; find $ovh_dest -type f -mtime +$BKP_WEEKS -name '*.sql.gz' | grep -P 'S\d{2}_\d{4}' | xargs rm -f"
retCode[5]=$?
errMsg[5]="weekly backup bases @ ovh"
ssh ovh "set -o pipefail; find $ovh_dest -type f -mtime +$BKP_MONTHS -name '*.sql.gz' | grep -P '(January|February|March|April|May|June|July|August|September|October|November|December)' | xargs rm -f"
retCode[6]=$?
errMsg[6]="monthly backup bases @ ovh"
# Codes retour:
# grep: 0 -> si lignes, 1 -> pas de lignes, 2 -> erreur
# find: 0 -> si ok, >0 -> si erreur
# xarg: 0 -> si ok, 123 à 127 si erreur, 1 -> autres erreurs
for (( c=1; c<=6; c++ ))
do
#if [ ${retCode[$c]} -eq 1 ]; then
#echo "erreur"
#notification "Error on deleting remote files ${errMsg[$c]}" $result
#notification "Error" ${retCode[$c]}
#fi
if [ ${retCode[$c]} -eq 0 ]; then
echo "Pas d'erreur"
#notification "Error on deleting remote files ${errMsg[$c]}" $result
notification "No Error" ${retCode[$c]}
fi
done
#
#### End of script ####
END_COMMENT2
Reference in New Issue View Git Blame Copy Permalink