142 lines
5.7 KiB
Bash
Executable File
142 lines
5.7 KiB
Bash
Executable File
#!/bin/bash
|
|
## author : Dryusdan
|
|
## date : 15/02/2020
|
|
## description : A MySQL dumper
|
|
## usage : ./mysqlbackup.sh /BASE/BACKUP/FOLDER RETENTION USERNAME PASSWORD HOST HEALTCHECK_UUID
|
|
|
|
## Bash strict mode ####################################
|
|
set -o errexit # abort on nonzero exitstatus
|
|
set -o pipefail # don't hide errors within pipes
|
|
|
|
#set -o nounset # abort on unbound variable
|
|
|
|
## Logs ################################################
|
|
readonly SCRIPTNAME="$(basename "$0")"
|
|
info() { echo -e "[INFO] $* " | logger --tag "${SCRIPTNAME}" ; }
|
|
warning() { echo -e "[WARNING] $* " | logger --tag "${SCRIPTNAME}" ; }
|
|
error() { echo -e "[ERROR] $* " | logger --tag "${SCRIPTNAME}" ; }
|
|
fatal() { echo -e "[FATAL] $* " | logger --tag "${SCRIPTNAME}" ; exit 1 ; }
|
|
########################################################
|
|
|
|
## Define variables ###################################
|
|
info "Define variables"
|
|
DATE=$(date '+%s')
|
|
|
|
info "Source ${HOME}/.env"
|
|
source "${HOME}/.env"
|
|
info "Sourced"
|
|
|
|
function cleanup()
|
|
{
|
|
sudo /usr/bin/umount ${HOME}/cifs/
|
|
}
|
|
|
|
trap cleanup EXIT
|
|
|
|
sudo /usr/bin/mount -t cifs -o seal,user="${CIFSUSER}",pass="${CIFSPASSWD}",uid=$(id -u),gid=$(id -g),forceuid,forcegid, //"${CIFSUSER}.your-storagebox.de"/"${CIFSUSER}" ${HOME}/cifs/
|
|
|
|
if [ $# -eq 0 ]
|
|
then
|
|
fatal "No arguments supplied. Usage : ./mysqlbackup.sh /BASE/BACKUP/FOLDER RETENTION USERNAME PASSWORD HOST PORT"
|
|
fi
|
|
|
|
if [ -z "${1}" ];
|
|
then
|
|
fatal "Folder is not defined. Usage : ./mysqlbackup.sh /BASE/BACKUP/FOLDER RETENTION USERNAME PASSWORD HOST PORT"
|
|
else
|
|
FOLDER="${1}/${DATE}"
|
|
FOLDER_WITHOUT_DATE="${1}/"
|
|
fi
|
|
|
|
if [ -z "${2}" ];
|
|
then
|
|
fatal "Retention is not defined. Usage : ./mysqlbackup.sh /BASE/BACKUP/FOLDER RETENTION USERNAME PASSWORD HOST PORT"
|
|
else
|
|
RETENTION="${2}"
|
|
fi
|
|
|
|
if [ -z "${3}" ];
|
|
then
|
|
fatal "Username is not defined. Usage : ./mysqlbackup.sh /BASE/BACKUP/FOLDER RETENTION USERNAME PASSWORD HOST PORT"
|
|
else
|
|
USERNAME=${3}
|
|
fi
|
|
|
|
if [ -z "${4}" ];
|
|
then
|
|
fatal "Password is not defined. Usage : ./mysqlbackup.sh /BASE/BACKUP/FOLDER RETENTION USERNAME PASSWORD HOST PORT"
|
|
else
|
|
PASSWORD=${4}
|
|
fi
|
|
|
|
if [ -z "${5}" ];
|
|
then
|
|
warning "Host not defined, use 127.0.0.1 by default"
|
|
HOST="127.0.0.1"
|
|
else
|
|
HOST=${5}
|
|
fi
|
|
|
|
if [ -z "${6}" ];
|
|
then
|
|
warning "Port not defined, use 3306 by default"
|
|
PORT="3306"
|
|
else
|
|
PORT=${6}
|
|
fi
|
|
|
|
if [ -z "${7}" ];
|
|
then
|
|
HC=false
|
|
else
|
|
HC=true
|
|
HC_UUID="${7}"
|
|
fi
|
|
|
|
## Run dump ######################################
|
|
info "Create backup folder"
|
|
mkdir -p ${FOLDER}/{databases,schemas,datas,extras}/
|
|
info "Dumping databases"
|
|
curl --silent --retry 3 "https://cron.dryusdan.net/ping/${HC_UUID}/start" > /dev/null
|
|
for dbname in $(/usr/bin/mysql --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} -N -e "show databases" | grep -v "information_schema" | grep -v "mysql" | grep -v "performance_schema")
|
|
do
|
|
if ! mountpoint -q -- "${FOLDER_WITHOUT_DATE}"; then
|
|
curl --silent --retry 3 "https://cron.dryusdan.net/ping/${HC_UUID}/fail" > /dev/null
|
|
fatal "Mountpoint not mounted"
|
|
fi
|
|
info "Dumping ${dbname}"
|
|
mkdir -p ${FOLDER}/{schemas,datas}/${dbname}
|
|
info "Dumping ${dbname} schema"
|
|
/usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} --no-data ${dbname} | gzip > ${FOLDER}/databases/${dbname}.sql.gz
|
|
info "Dumping events, routines, triggers of ${dbname}"
|
|
/usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} --no-data --no-create-info --routines --triggers --events ${dbname} | gzip > ${FOLDER}/extras/${dbname}.sql.gz
|
|
info "Know engine of database"
|
|
## If all tables use InnoDB engine, we use --single-transaction
|
|
## Also, if one or more table use MyISAM engine, we need lock all tables
|
|
if /usr/bin/mysql --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} -N -e "select engine from information_schema.tables where table_schema = '${dbname}'" | grep "MyISAM" -q;
|
|
then
|
|
warning "${dbname} have a table who using MyISAM engine."
|
|
info "Dumping with lock"
|
|
for tablename in $(/usr/bin/mysql --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} -N -e "show tables" ${dbname})
|
|
do
|
|
info "Dumping ${tablename}'s schema of ${dbname}"
|
|
/usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} --no-data --add-locks --add-drop-table ${dbname} ${tablename} | sed 's/ AUTO_INCREMENT=[0-9]*\b//g' | gzip > ${FOLDER}/schemas/${dbname}/${tablename}.sql.gz
|
|
info "Dumping ${tablename}'s data of ${dbname}"
|
|
/usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} --no-create-info --add-locks --extended-insert=FALSE ${dbname} ${tablename} | gzip > ${FOLDER}/datas/${dbname}/${tablename}.sql.gz
|
|
done
|
|
else
|
|
info "Dumping with single transaction"
|
|
for tablename in $(/usr/bin/mysql --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} -N -e "show tables" ${dbname})
|
|
do
|
|
info "Dumping ${tablename}'s schema of ${dbname}"
|
|
/usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} --no-data --skip-lock-tables --add-drop-table ${dbname} ${tablename} | sed 's/ AUTO_INCREMENT=[0-9]*\b//g' | gzip > ${FOLDER}/schemas/${dbname}/${tablename}.sql.gz
|
|
info "Dumping ${tablename}'s data of ${dbname}"
|
|
/usr/bin/mysqldump --user=${USERNAME} --password=${PASSWORD} --host=${HOST} --port=${PORT} --no-create-info --skip-lock-tables --single-transaction --extended-insert=FALSE ${dbname} ${tablename} | gzip > ${FOLDER}/datas/${dbname}/${tablename}.sql.gz
|
|
done
|
|
fi
|
|
done
|
|
info "Backup is done"
|
|
curl --silent --retry 3 "https://cron.dryusdan.net/ping/${HC_UUID}/" > /dev/null
|
|
info "Removing old backup"
|
|
find ${FOLDER_WITHOUT_DATE} -mtime +${RETENTION} -exec rm -rf {} \;
|
|
info "Removing done" |