Bash Script to backup all MySQL databases

backup.sh

#!/bin/bash
#==============================================================================
#TITLE:            backup.sh
#DESCRIPTION:      script for automating the daily mysql backups on development computer
#AUTHOR:           tleish
#DATE:             2013-12-20
#VERSION:          0.4
#USAGE:            ./mysql_backup.sh
#CRON:
  # example cron for daily db backup @ 9:15 am
  # min  hr mday month wday command
  # 15   9  *    *     *    /Users/[your user name]/scripts/mysql_backup.sh

#RESTORE FROM BACKUP
  #$ gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

#==============================================================================
# CUSTOM SETTINGS
#==============================================================================

# directory to put the backup files
# use the value from $RESTIC_REPOSITORY in the ENV 

# Web root of all sites to backup
# BACKUP_SRC

# MySQL Parameters
# Set via the environment
# MYSQL_UNAME=root
# MYSQL_PWORD=

# Don't backup databases with these names 
# Example: starts with mysql (^mysql) or ends with _schema (_schema$)
IGNORE_DB="(^mysql|_schema$)"

# include mysql and mysqldump binaries for cron bash user
PATH=$PATH:/usr/local/mysql/bin:/usr/local/bin
RESTIC=/usr/local/bin/restic

# Number of days to keep backups
KEEP_BACKUPS_FOR=30 #days
KEEP_BACKUPS_FOR_HOURS=24 #hours

#==============================================================================
# METHODS
#==============================================================================

# YYYY-MM-DD
TIMESTAMP=$(date +%F)

function delete_old_backups()
{
  echo "Pruning backups older than $KEEP_BACKUPS_FOR days"
  local keep_policy="--keep-daily $KEEP_BACKUPS_FOR"
  if [ -n "$KEEP_BACKUPS_FOR_HOURS" ]; then
    local keep_policy+=" --keep-hourly $KEEP_BACKUPS_FOR_HOURS" 
  fi
  
  $RESTIC forget --prune $keep_policy
  $RESTIC check
}

function mysql_login() {
  local mysql_login="-u $MYSQL_UNAME" 
  if [ -n "$MYSQL_PWORD" ]; then
    local mysql_login+=" -p$MYSQL_PWORD" 
  fi
  echo $mysql_login
}

function database_list() {
  local show_databases_sql="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'"
  echo $(mysql $(mysql_login) -e "$show_databases_sql"|awk -F " " '{if (NR!=1) print $1}')
}

function echo_status(){
  printf '\r'; 
  printf ' %0.s' {0..100} 
  printf '\r'; 
  printf "$1"'\r'
}

function backup_database(){
    backup_file="$TIMESTAMP.$database.sql" 
    output+="$database => $backup_file\n"
    echo_status "...backing up $count of $total databases: $database"
    mysqldump $(mysql_login) $database | $RESTIC backup --stdin --stdin-filename $backup_file --tag mysql --tag $database
}

function backup_databases(){
  local databases=$(database_list)
  local total=$(echo $databases | wc -w | xargs)
  local output=""
  local count=1
  for database in $databases; do
    backup_database
    local count=$((count+1))
    sleep 5
  done
  echo -ne $output | column -t
}

function backup_folder(){
    output+="$folder\n"
    echo_status "...backing up folder #$count ($folder)"
    $RESTIC backup $folder --tag web --tag $(basename $folder)
}

function backup_folders(){
  local count=1
  local output=""
  local last_char=${BACKUP_SRC:${#BACKUP_SRC}-1:1}
  [[ $last_char != "/" ]] && BACKUP_SRC="$BACKUP_SRC/";
  for folder in $BACKUP_SRC; do
    if [ -d ${folder} ]; then
      backup_folder
      local count=$((count+1))
      sleep 5
     fi
  done
  echo -ne $output | column -t
}

function hr(){
  printf '=%.0s' {1..100}
  printf "\n"
}

#==============================================================================
# RUN SCRIPT
#==============================================================================
START=$(date +%s)
printf "Running backup at $(date).."
backup_databases
hr
backup_folders
hr
delete_old_backups
hr
DONE=$(($(date +%s)-START))
printf "All backed up! Finished in $DONE seconds.\n\n"