#!/bin/sh

# Base configuration: datestamp e.g. YYYYMMDD

DATE=$(date +"%Y%m%d")

# Backup directory (manually create it first)

BACKUP_DIR="/volume1/Backup/MySQL"

# MySQL credentials

MYSQL_USER="root"
MYSQL_PASSWORD="yourpasswordhere"

# MySQL commands
# for Asustor mysqldump is in an "unusual" place (not in /usr/local/bin)

MYSQL=/usr/local/bin/mysql
MYSQLDUMP=/usr/local/AppCentral/mariadb/data/binary/bin/mysqldump

# Databases to ignore

SKIPDATABASES="Database|information_schema|performance_schema|mysql"

# Number of days to keep backups before deleting them

RETENTION=10

# ---- NE RIEN MODIFIER SOUS CETTE LIGNE ------------------------------------------
#
# Create a new directory into backup directory location for this date

mkdir -p $BACKUP_DIR/$DATE

# Retrieve a list of all databases

databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"`

# Dumb the databases in seperate names and gzip the .sql file

for db in $databases; do
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done

# Remove files older than X days

find $BACKUP_DIR/* -mtime +$RETENTION -delete