SmartNote

#!/usr/bin/env bash
#
# Daily backup mysql databases
# CLI args: none

#exit on error
set -e

LANG=C
PATH="/bin:/usr/sbin:/usr/bin:/sbin:/usr/local/bin:/usr/local/sbin"

DATE=$(date +%F)
BACKUP_DIR=/home/mysql_backups
DUMP_DIR=$BACKUP_DIR/dumps
LOG=$BACKUP_DIR/backup.log

DAYS=7
DB="all"
OPTS="--order-by-primary --add-drop-database"

#########################

print_error() { echo "$@"; write_log "[ERROR] $@"; exit 1; }
print_info()  { echo "$@"; write_log "[INFO] $@"; }
write_log()   { echo "`date -R -u` $@" >> $LOG; }

##########################

# check dir
[[ -d $DUMP_DIR ]] || mkdir -p $DUMP_DIR

# check permissions on backup dir
if PERMS=$(stat --format="%a" $BACKUP_DIR)
then
  # restore chmod
  [[ $PERMS -eq 700 ]] || chmod 700 $BACKUP_DIR
fi

# check binaries
[[ -f `which mysqldump 2>/dev/null` ]] && MYSQLDUMP=mysqldump || print_error "No mysqldump binary found in $PATH"
[[ -f `which mysql 2>/dev/null` ]] && MYSQL=mysql || print_error "No mysql binary found in $PATH"

# validate $DB and skip default dbs
[[ $DB == all ]] && DB=$(mysql -e 'show databases' | tail -n +2 | grep -v -E "^(information_schema|performance_schema)$")

# create dumps
for db in $DB
do
  # is db exist?
  if ! EXISTS=$($MYSQL -s -N -e "show databases" | tail -n +2 | grep -x $db)
  then
    print_info "No such database: $db"
    break
  fi

  DUMP_FILE=$DUMP_DIR/$DATE/$db.sql.gz
  # is dump exist?
  [[ -s $DUMP_FILE ]] || (
    # log
    print_info "Creating new: $DUMP_FILE"

    # mkdir
    mkdir -p $DUMP_DIR/$DATE

    # add --events to mysql db
    [[ $db == mysql ]] && OPTS+=" --events"

    # dump
    $MYSQLDUMP $OPTS $db | gzip -1 > $DUMP_FILE
  )
done

# remove dirs
if OUTDATED_DIRS=$(find $DUMP_DIR -maxdepth 1 -type d -mtime +$DAYS)
then
  for dir in $OUTDATED_DIRS
  do
    # log
    print_info "Deleting outdated: $dir"

    # rm
    [[ -d $dir ]] && rm -r "$dir" || print_error "Can't remove $dir"
  done
fi