MySQL backup script with deletion of old files

README.md

To backup all databases in one file.

./mysql_backup.sh

To back up a particular database to a directory named the database.

./mysql_backup.sh DB_NAME

To backup every database in seperated directories.

./mysql_backup.sh combined

mysql_backup.sh

#!/bin/bash

STORE_FOLDER="/opt/mysql_backup"

MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USER="root"
MYSQL_PASSWORD="" # SET YOUR PASSWORD
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"

CURRENT=$(date +"%Y-%m-%d %H:%M:%S")
CURRENT_DAY=$(date +"%Y-%m-%d")
CURRENT_FILENAME="${CURRENT//[ ]/_}"
CURRENT_FILENAME="${CURRENT_FILENAME//[:]/-}"

HOUR_RETENTION="24"
DAY_RETENTION="7"
WEEK_RETENTION="5"
MONTH_RETENTION="12"

function do_backups() {
  backup_db=$1

  # RUN DUMP
  if [ "$backup_db" == "all" ]; then
    BACKUP_PATH=$STORE_FOLDER/all
    [[ ! -d "$BACKUP_PATH" ]] && mkdir -p "$BACKUP_PATH/hourly"
    echo "   Creating $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz"
    $MYSQLDUMP -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASSWORD --all-databases | gzip -9 > $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz
  else
    BACKUP_PATH=$STORE_FOLDER/$backup_db
    [[ ! -d "$BACKUP_PATH" ]] && mkdir -p "$BACKUP_PATH/hourly"
    echo "   Creating $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz"
    $MYSQLDUMP -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASSWORD $backup_db | gzip -9 > $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz
  fi

  [[ ! -d "$BACKUP_PATH/daily" ]] && mkdir -p "$BACKUP_PATH/daily"
  [[ ! -d "$BACKUP_PATH/weekly" ]] && mkdir -p "$BACKUP_PATH/weekly"
  [[ ! -d "$BACKUP_PATH/monthly" ]] && mkdir -p "$BACKUP_PATH/monthly"

  # COPY DAILY
  if [ `date +%k` -eq 23 ]; then
    if [ ! -f "$BACKUP_PATH/daily/$CURRENT_FILENAME.sql.gz" ]; then
      cp $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz $BACKUP_PATH/daily/$CURRENT_DAY.sql.gz
    fi
  fi

  # COPY WEEKLY
  if [ `date +%u` -eq 7 ]; then
    if [ ! -f "$BACKUP_PATH/weekly/$CURRENT_FILENAME.sql.gz" ]; then
      cp $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz $BACKUP_PATH/weekly/$CURRENT_DAY.sql.gz
    fi
  fi

  # COPY MONTHLY
  if [ `date +%d` -eq 25 ]; then
    if [ ! -f "$BACKUP_PATH/monthly/$CURRENT_FILENAME.sql.gz" ]; then
      cp $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz $BACKUP_PATH/monthly/$CURRENT_DAY.sql.gz
    fi
  fi

  # DELETE OLD BACKUPS
  find $BACKUP_PATH/hourly/ -type f -not -newermt "`date +"%Y-%m-%d %H:%M:%S" --date $HOUR_RETENTION' hours ago'`" -delete
  find $BACKUP_PATH/daily/ -type f -not -newermt "`date +"%Y-%m-%d %H:%M:%S" --date $DAY_RETENTION' days ago'`" -delete
  find $BACKUP_PATH/weekly/ -type f -not -newermt "`date +"%Y-%m-%d %H:%M:%S" --date $WEEK_RETENTION' weeks ago'`" -delete
  find $BACKUP_PATH/monthly/ -type f -not -newermt "`date +"%Y-%m-%d %H:%M:%S" --date $MONTH_RETENTION' months ago'`" -delete
}

echo "*** MySQL Backup"
echo
echo "To be deleted:"
echo "   Deleting hourly backups older than "`date +"%Y-%m-%d %H:%M:%S" --date $HOUR_RETENTION' hours ago'`
echo "   Deleting daily backups older than "`date +"%Y-%m-%d %H:%M:%S" --date $DAY_RETENTION' days ago'`
echo "   Deleting weekly backups older than "`date +"%Y-%m-%d %H:%M:%S" --date $WEEK_RETENTION' weeks ago'`
echo "   Deleting monthly backups older than "`date +"%Y-%m-%d %H:%M:%S" --date $MONTH_RETENTION' months ago'`
echo

# RUN BACKUP
echo "Starting MySQL backup..."

if [ -z "$1" ]; then
  do_backups all

  else
    if [ "$1" == "combined" ]; then
      databases=($($MYSQL -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASSWORD -Bse "show databases" | grep -i -v "_schema" | grep -i -v "sys" | grep -i -v "mysql"))

      for db in "${databases[@]}"; do
        echo "Starting $db MySQL backup..."
        do_backups $db
      done

      else do_backups $1
    fi
fi