backup-mysql

#! /bin/bash
#
# NAME
# backup-mysql - create a tar archive with databases stoRED in .sql.gz separated file.
#
# SYNOPSIS
#	./backup-mysql /path/to/.config /path/to/backup/destination
#
# DESCRIPTION
#	this script dump all databases into singular database.sql.gz file and create an archive .tar.xz into current script directory.
#
# INSTALLATION
# - rename .config.example to .config
# - edit .config file with your data
# - sudo chown root:root /path/to/backup-mysql
# - sudo chown root:root /path/to/.config
# - sudo chmod 600 /path/to/.config
#	- sudo chmod +x /path/to/backup-mysql
#
# AUTHOR:
#	backup-mysql.sh is written by Alfio Salanitri <www.alfiosalanitri.it> and are licensed under the MIT License.
#
#
#############################################################
# Icons	and color
# https://www.techpaste.com/2012/11/print-coloRED-text-background-shell-script-linux/
# https://apps.timwhitlock.info/emoji/tables/unicode
#############################################################
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NOCOLOR='\033[0m'
ICON_OK='\xE2\x9C\x94'
ICON_KO='\xe2\x9c\x97'
ICON_WAIT='\xE2\x8C\x9B'

#############################################################
# VARIABLES
#############################################################
if [ ! -f "$1" ]; then
  printf "Sorry but the config file is required. \n"
  exit 1
fi
if [ ! -d "$2" ]; then
  printf "Sorry but the backup destination directory is required. \n"
  exit 1
fi
TIMESTAMP=$(date +"%d%m%Y-%H%M")
BACKUP_TMP_DIR="/tmp/backup-mysql"
MYSQL_USER=$(awk -F'=' '/^DATABASE_USER=/ { print $2}' $1)
MYSQL_PASSWORD=$(awk -F'=' '/^DATABASE_PASSWORD=/ { print $2}' $1)
EXCLUDED_DATABASES=$(awk -F'=' '/^EXCLUDED_DATABASES=/ { print $2}' $1)
DELETION_DAYS_NUMBER=$(awk -F'=' '/^DELETE_BACKUPS_OLDER_THAN_DAYS=/ { print $2}' $1)
BACKUP_DESTINATION=${2%/}
BACKUP_FILENAME_PREFIX="mysqldump-databases"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
if [ "" == "$MYSQL_USER" ]; then
  printf "[${RED}${ICON_KO}${NOCOLOR}] Save the database user into config file\n"
  exit 1
fi
if [ "" == "$MYSQL_PASSWORD" ]; then
  printf "[${RED}${ICON_KO}${NOCOLOR}] Save the database password into config file\n"
  exit 1
fi
# Check packages
if ! command -v $MYSQL &>/dev/null; then
  printf "${RED}${ICON_KO}${NOCOLOR} Sorry, but ${GREEN}mysql${NOCOLOR} is required.\n"
  exit 1
fi
if ! command -v $MYSQLDUMP &>/dev/null; then
  printf "${RED}${ICON_KO}${NOCOLOR} Sorry, but ${GREEN}mysqldump${NOCOLOR} is required.\n"
  exit 1
fi

START_TIME=$(date +%s)

# create the backup tmp dir
mkdir -p $BACKUP_TMP_DIR

printf "[${YELLOW}${ICON_WAIT}${NOCOLOR}] Reading all databases...\n\n"
DATABASES=$($MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($EXCLUDED_DATABASES)")

for DB in $DATABASES; do
  printf "[${YELLOW}${ICON_WAIT}${NOCOLOR}] Saving database: ${GREEN}${DB}${NOCOLOR}...\n"
  $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $DB | gzip >"$BACKUP_TMP_DIR/$DB.sql.gz"
  DB_SIZE=$(stat -c %s "$BACKUP_TMP_DIR/$DB.sql.gz" | numfmt --to=iec)
  printf "[${GREEN}${ICON_OK}${NOCOLOR}] Database: ${GREEN}${DB} ($DB_SIZE)${NOCOLOR} saved!\n"
  echo "--------------------------"
done

printf "\n[${YELLOW}${ICON_WAIT}${NOCOLOR}] Compressing directory...\n"
tar cJfP $BACKUP_DESTINATION/$BACKUP_FILENAME_PREFIX-$TIMESTAMP.tar.xz $BACKUP_TMP_DIR
printf "[${GREEN}${ICON_OK}${NOCOLOR}] Archive stored into $BACKUP_DESTINATION directory.\n"
rm -r $BACKUP_TMP_DIR
printf "[${GREEN}${ICON_OK}${NOCOLOR}] Temporary directory deleted.\n\n"

#delete files older then days number
if [ $DELETION_DAYS_NUMBER -gt 0 ]; then
  printf "[${YELLOW}${ICON_WAIT}${NOCOLOR}] Deleting backup files older then $DELETION_DAYS_NUMBER day/s...\n"
  find $BACKUP_DESTINATION -mtime +$DELETION_DAYS_NUMBER -type f -name "$BACKUP_FILENAME_PREFIX*" -delete
  printf "[${GREEN}${ICON_OK}${NOCOLOR}] Old backup files deleted with success.\n\n"
fi
END_TIME=$(date +%s)
ELAPSED=$(( end_time - start_time ))
printf "Backup date: $(date)\n"
printf "Backup time elapsed: $ELAPSED seconds.\n"
exit 0