Mysql Backup Script

backup_mysql.sh

#!/bin/bash

DATE=$(date +"%Y%m%d")
BACKUP_DIR="/backup/mysql"
BACKUP_ARCHIVE_DIR="/backup/mysql_archive"
LOG_DIR="/backup/backup_logs"
MYSQL_USER="backup_user"
MYSQL_PASSWORD="tr -cd 'a-zA-Z0-9' </dev/urandom | head -c '65' ; echo '@'"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
SKIPDATABASES="Database|information_schema|performance_schema|mysql|sys"
RETENTION=2
LOG_FILE="${LOG_DIR}/${DATE}_mysql_backup_timing.log"
BACKUP_READ_USER="backup_user"

log() {
  echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG_FILE"
}

format_duration() {
  local seconds=$1
  printf '%02d:%02d:%02d\n' \
    $((seconds/3600)) \
    $(((seconds%3600)/60)) \
    $((seconds%60))
}

START_TIME=$(date +%s)
log "MySql Backup started"

# Create Backup Folder (Date)
mkdir -p $BACKUP_DIR/$DATE

# Create Backups
databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"`
for db in $databases; do
  echo $db
  $MYSQLDUMP --no-tablespaces --force --routines --events -E --triggers --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-lock-tables  --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done

# Copy to archive folder
cp -r $BACKUP_DIR/$DATE $BACKUP_ARCHIVE_DIR/

# Grant Access
chgrp -R $BACKUP_READ_USER $BACKUP_DIR/$DATE
chmod -R 750 $BACKUP_DIR/$DATE

# Delete older ones
find $BACKUP_DIR/* -name '*.sql.gz' -mtime +$RETENTION -delete
find $BACKUP_DIR -type d -empty -delete

END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))
log "MySql Backup finished in $(format_duration "$DURATION")"