Backup MySQL Database Daily, Weekly and Monthly

mysqldump-cron.sh

#!/bin/bash

# Modified from: https://grahamrpugh.com/2019/01/15/mysqldump-daily-weekly-monthly.html

# Create database user with these privileges:
# SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, TRIGGER, SHOW VIEW
# 
# CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
# GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, TRIGGER, SHOW VIEW ON *.* TO 'newuser'@'localhost';
# FLUSH PRIVILEGES;

NOW=$(date +"%F_%H%M%S")
BACKUP_DIR="/path/to/dir"
BACKUP_LOG="$BACKUP_DIR/backup.log"
MYSQL_USER="userx"
MYSQL_PSWD="passwordx"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

TODAY=$(date +"%Y-%m-%d")
DAILY_DELETE_NAME="daily-"`date +"%Y-%m-%d" --date '7 days ago'`
WEEKLY_DELETE_NAME="weekly-"`date +"%Y-%m-%d" --date '5 weeks ago'`
MONTHLY_DELETE_NAME="monthly-"`date +"%Y-%m-%d" --date '12 months ago'`

# Create backup directory if not exists
[[ ! -d "$BACKUP_DIR" ]] && mkdir -p $BACKUP_DIR

function log() {
    echo ${1:-""}
    echo $1 >> $BACKUP_LOG
}

function del() {
    if [ -f "$1.sql.gz" ]; then
    log "   Deleting $1.sql.gz"
    rm -rf $1.sql.gz
    fi
}

function do_backups() {
    db=$1
    # to calculate execution time
    ts=$(date +%s%N)
    BACKUP_PATH=$BACKUP_DIR/$db
    # Create backup dir if not exists
    [[ ! -d "$BACKUP_PATH" ]] && mkdir -p "$BACKUP_PATH"
    # log "Starting backup $db"
    # Starting backup using mysqldump
    $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PSWD --databases $db \
    --routines --skip-comments --single-transaction --quick --skip-compact  2>/dev/null | \
    grep -v "mysql: [Warning] Using a password on the command line interface can be insecure." | \
    gzip -9 > $BACKUP_PATH/daily-$TODAY.sql.gz

    # delete old backups
    del "$BACKUP_PATH/$DAILY_DELETE_NAME"
    del "$BACKUP_PATH/$WEEKLY_DELETE_NAME"
    del "$BACKUP_PATH/$MONTHLY_DELETE_NAME"

    # make weekly
    if [ `date +%u` -eq 7 ];then
        cp $BACKUP_PATH/daily-$TODAY.sql.gz $BACKUP_PATH/weekly-$TODAY.sql.gz
    fi

    # make monthly
    if [ `date +%d` -eq 25 ];then
        cp $BACKUP_PATH/daily-$TODAY.sql.gz $BACKUP_PATH/monthly-$TODAY.sql.gz
    fi
    # calculate execution time in ms
    tt=$((($(date +%s%N) - $ts)/1000000))
    log "Backup DB: $db ($tt ms)"
}

log
log "----------------------------------------------"
log "   MySQL Auto Backup - $NOW"
log "----------------------------------------------"
log "To be deleted if present:"
log "   $DAILY_DELETE_NAME"
log "   $WEEKLY_DELETE_NAME"
log "   $MONTHLY_DELETE_NAME"
log

# Skip Some databases here using Regex..
# databases=($(/usr/bin/mysql -Bse "show databases" | grep -i -v "_schema" | grep -i -v "sys" ))
skipdbs="_schema|sys"
databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PSWD -e "SHOW DATABASES;" | grep -Ev "($skipdbs)"`

# Backup all databases
for db in $databases; do
    do_backups $db
done