Backup scripts

backup-mysql.sh

#!/bin/sh
#
# Usage
# ########
# 
# This script backup all your user MySQL databases to a dedicated GZIP file (in a folder)
#  while forcing a storage strategy to control disk space
# It is best used in a cron to perform hourly, daily, weekly and monthly backups
# Backups are stored in the target folder under : /folder/$PERIOD-YYMMDD-hhmm/mysql-$DB-$PERIOD-YYMMDD-hhmm.sql.gz
# Old backups are automatically deleted if too old
#
# Usage : ./backup-mysql.sh [period]
#     period can be : hourly, daily, weekly, monthly
#
# 
# Crontab
# ########
# 0       *       *       *       *       /backups/backup-mysql.sh hourly >> /var/log/backups-mysql.log
# 10      2       *       *       *       /backups/backup-mysql.sh daily >> /var/log/backups-mysql.log
# 20      2       *       *       1       /backups/backup-mysql.sh weekly >> /var/log/backups-mysql.log
# 40      2       1       *       *       /backups/backup-mysql.sh monthly >> /var/log/backups-mysql.log
#
#

if [ $# -ne 1 ]; then
        echo "Usage : $0 period"
        echo "   where period is : hourly daily weekly monthly"
        exit 1
fi

MYSQL_USER="root"
MYSQL_PASSWORD="XXXX"
PERIOD=$1
OUTPUT="/backups/mysql"

# HowKeep the backups for X days
KEEP_HOURLY=3
KEEP_DAILY=15
KEEP_WEEKLY=30
KEEP_MONTHLY=190

DATE="${PERIOD}-"$(date +"%Y%m%d-%H%M")
DIR="${OUTPUT}/${DATE}/"

## CREATE THE DIR
echo "STARTS `date`"
echo "MYSQL BACKUPS -" `hostname`
echo "Destination: ${DIR}"

mkdir -p $DIR
cd $DIR || exit 100

# EXTRACT DATABASES LIST
DATABASES=$( echo 'show databases;' | mysql -u "${MYSQL_USER}" --password="${MYSQL_PASSWORD}" -N | egrep -v '^(information_schema|performance_schema|mysql)$' )

# EXTRACT DUMPS
for DB in $DATABASES; do
        echo "   database : $DB"
        mysqldump --opt -u $MYSQL_USER --password="${MYSQL_PASSWORD}" "$DB" | gzip > "mysql-${DB}-${DATE}.sql.gz"
done
echo "ENDS `date`"
echo

## CLEANUP
cd ${OUTPUT} || exit 101

find -name 'hourly-*' -mtime +$KEEP_HOURLY   | xargs rm -rf
find -name 'daily-*' -mtime +$KEEP_DAILY     | xargs rm -rf
find -name 'weekly-*' -mtime +$KEEP_WEEKLY   | xargs rm -rf
find -name 'monthly-*' -mtime +$KEEP_MONTHLY | xargs rm -rf