#!/bin/bash

#######################################

BACKUP_ROOT=/backup

BCKDIR=$BACKUP_ROOT/mysql

KEEPDAYS=90

#######################################

MYCONF=/etc/mysql/debian.cnf

#exit if backup root doesn't exist
[ -d $BACKUP_ROOT ] || exit 21
#exit if backup dir is /
[ "x$BCKDIR" == "x" ] && exit 23

#make dirs
[ -d $BCKDIR ] || mkdir $BCKDIR
[ -d $BCKDIR/current ] || mkdir $BCKDIR/current
[ -d $BCKDIR/daily ] || mkdir $BCKDIR/daily
[ -d $BCKDIR/weekly ] || mkdir $BCKDIR/weekly

rm $BCKDIR/current/*


SQL="SELECT schema_name FROM information_schema.schemata WHERE"
SQL="${SQL} schema_name NOT IN ('information_schema','performance_schema')"

DBS="$(echo $SQL | mysql --defaults-file=$MYCONF -Bs )"

# turn off slow query log
mysql --defaults-file=$MYCONF -Bse 'SET GLOBAL slow_query_log = OFF'

# save databases into separate files
for db in $DBS
do

    FILE=$BCKDIR/current/$db.sql
    [ -f $FILE.gz ] && rm $FILE.gz
    [ -f $FILE ] && rm $FILE
    mysqldump --defaults-file=$MYCONF $db -Q -R --triggers --add-drop-table --add-locks --force --quick --disable-keys --lock-tables -r $FILE 2>/dev/null
    # --extended-insert
    # --add-drop-database
    # --complete-insert
    # --create-options
#    if [ -x /usr/bin/pigz ]; then
#	pigz --rsyncable $FILE
#    else
	gzip --rsyncable $FILE
#    fi

done

# turn slow query log back on
mysql --defaults-file=$MYCONF -Bse 'SET GLOBAL slow_query_log = ON'

#rotate
#daily
find $BCKDIR/daily/ -mindepth 1 -maxdepth 1 -mtime +8 -exec rm -r {} \;

#weekly
if [ "`date +%u`" == "7" ]; then
    find $BCKDIR/daily/ -mindepth 1 -maxdepth 1 -mtime +7 -exec mv {} $BCKDIR/weekly/ \;
    find $BCKDIR/weekly/ -mindepth 1 -maxdepth 1 -mtime +$KEEPDAYS -exec rm -r {} \;
fi

# snapshot

TS=`date +%Y-%m-%d`

src=$BCKDIR/current
dst=$BCKDIR/daily/$TS

[ -d $dst ] && rm -r $dst
mkdir $dst

cp -a -l $src/* $dst/

exit 0