mysql-backup-gz-clean.sh

#!/bin/bash
# Backup Mysql database staring with "__apps", compress the dump.
# Olds backup are removed if they are older than X days
# LCO 2016-06-05
# Script based on http://www.cyberciti.biz/faq/ubuntu-linux-mysql-nas-ftp-backup-script/

MUSER="root"
MPASS="PASSWORD-GOES-HERE"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
BAK="/var/backups/mysql"
GZIP="$(which gzip)"
NOW=$(date +"%Y%m%d")
 
[ ! -d "$BAK" ] && mkdir -p "$BAK"
QUERY="show databases LIKE 'apps__%';"
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse "$QUERY")"

for db in $DBS
do
 FILE=$BAK/$db.$NOW-$(date +"%H%M%S").sql.gz
 $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done


# delete files older than 7 days
find $BAK -atime +7 -name '*.gz' -exec rm {} \;