sql-backup.sh

#!/usr/bin/env bash
set -o nounset                              # Treat unset variables as an error

#########################
###                   ###
###     SETTINGS      ###
###                   ###
#########################

export PATH=/bin:/usr/bin:/usr/local/bin
configFile=/opt/sql-backup/config.json
configTemplate=/opt/sql-backup/config_template.json

if [[ ! -f $configFile ]]; then
  echo 'WARN: No configuration file found. Generate file from template.'
  if [[ ! -f $configTemplate ]]; then
    echo 'ERROR: No configuration template found. Installation might be corrupted. Please run update.sh or' \
         'reclone repository.'
    exit 1
  else
    cp $configTemplate $configFile
  fi
fi

echo 'INFO: Load config file.'
sqlHost=$(cat $configFile | jq -r '.[0].sqlHost')
sqlPort=$(cat $configFile | jq -r '.[0].sqlPort')
sqlUser=$(cat $configFile | jq -r '.[0].sqlUser')
sqlPass=$(cat $configFile | jq -r '.[0].sqlPass')
dbNames=$(cat $configFile | jq -r '.[0].dbNames')
if [ $dbNames == 'all' ]; then
  if [ -z $sqlPass ]; then
    dbNames=($(mysql -h$sqlHost -P$sqlPort -u$sqlUser -e "SHOW DATABASES;" -s --skip-column-names))
  else
    dbNames=($(mysql -h$sqlHost -P$sqlPort -u$sqlUser -p$sqlPass -e "SHOW DATABASES;" -s --skip-column-names))
  fi
fi
backupRoot=$(cat $configFile | jq -r '.[0].backupRoot')
backupRetainDays=$(cat $configFile | jq -r '.[0].backupRetainDays')

today=$(date +"%y%m%d")

#########################
###                   ###
###  START OF SCRIPT  ###
###                   ###
#########################

# check for backup root directory
if [[ -d "$backupRoot" ]];
then
  echo "INFO: Backup root directory found. Proceeding."
else
  echo "WARN: Backup root directory does not exist. Try to create it."
  { # try
    mkdir $backupRoot
  } || {
    echo "ERROR: Could not create Backup root directory. Stop script."
    exit 1
  }
fi

for dbName in "${dbNames[@]}";
do

  if [ $dbName == 'information_schema' ] || \
     [ $dbName == 'performance_schema' ] || \
     [ $dbName == 'mysql' ] || \
     [ $dbName == 'phpmyadmin' ]; then
    continue
  fi

  backupPath=$backupRoot$dbName
  mkdir -p ${backupPath}

  if [ -z $sqlPass ];
  then
    mysqldump -h ${sqlHost} \
      -P${sqlPort} \
      -u${sqlUser} \
      ${dbName} > ${backupPath}/${dbName}_${today}.sql
  else
    mysqldump -h ${sqlHost} \
      -P${sqlPort} \
      -u${sqlUser} \
      -p${sqlPass} \
      ${dbName} > ${backupPath}/${dbName}_${today}.sql
  fi

  if [ $? -eq 0 ]; then

    echo "INFO: Database backup for ${dbName} successfully completed."
    delDate=$(date +"%y%m%d" --date="${backupRetainDays} days ago")
    delFile=${dbName}-${delDate}.sql

    for f in $backupPath/*; do
      if [[ "$f" < "$delFile" ]]; then
        echo "delete $f"
        rm -f $f
      else
        echo "keep $f"
      fi
    done
  else
    echo "ERROR: Backup failed for ${dbName}. Skip deletion process for older Backups."
    continue
  fi
done