#!/bin/bash

DBUSERNAME=root
DBPASSWORD=***
HOSTNAME=`/bin/hostname -s`
BACKUPDIR="/var/lib/backup/mysql"
KEEPDAYS=14

MESSAGE=""

  MESSAGE+="Dumping MySQL-Databases to "$BACKUPDIR"...
"
MESSAGE+="
"`date +'%Y-%m-%d %H:%M:%S'`"

"


if [ ! -d "$BACKUPDIR" ]; then
  /bin/mkdir -p "$BACKUPDIR"
fi


if [ -d "$BACKUPDIR" ] &&  [ $KEEPDAYS != "" ]; then
    OIFS=$IFS;
    NOW=`date "+%Y%m%d"`

    for x in `find $BACKUPDIR -maxdepth 2 -mtime $KEEPDAYS -type d -regex '.*/[0-9]+_[0-9]+'`; do
        FDATE=`echo "$x"|grep -oE "[0-9]{8}_[0-9]{6}$"`
        MESSAGE+="Deleting old dump "$x"...
"
        if [ "$FDATE" != "" ]; then
            /bin/rm $x/*.bz2
            /bin/rmdir "$x"
        fi

    done

    IFS=$OIFS;
fi

MESSAGE+="
"


DBLIST=`mktemp`
/usr/bin/mysqlshow -u $DBUSERNAME -p$DBPASSWORD | awk '{print $2}' | grep -v Databases | sort >$DBLIST

for DB in `cat $DBLIST`; do
    DATE=`date "+%Y%m%d_%H%M%S"`
    MESSAGE+="Dumping Database $DB...
"
    TBLLIST=`mktemp`
    /usr/bin/mysql -u $DBUSERNAME -p$DBPASSWORD -D "$DB" -e "SHOW TABLES;" | awk '{print $1}' | grep -v "Tables_in_" | sort >$TBLLIST


    for TBL in `cat $TBLLIST`; do
        DUMPDIR=$BACKUPDIR"/"$DB"/"$DATE;
        if [ ! -d "$DUMPDIR" ]; then
            /bin/mkdir -p "$DUMPDIR"
        fi
        DUMPFILE=$DUMPDIR"/"$DB"_"$TBL"_"$DATE".sql"
        DUMPBZFILE="$DUMPFILE.bz2"
        if [ -f "$DUMPBZFILE" ]; then
            rm "$DUMPBZFILE"
        fi
        /usr/bin/mysqldump --opt -u $DBUSERNAME -p$DBPASSWORD $DB -t $TBL > "$DUMPFILE";
        /bin/bzip2 "$DUMPFILE"
    done
    if [ -f "$TBLLIST" ]; then
        /bin/rm "$TBLLIST";
    fi


done

if [ -f "$DBLIST" ]; then
    rm "$DBLIST";
fi


MESSAGE+="
"`date +'%Y-%m-%d %H:%M:%S'`"

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

"

echo "$MESSAGE"
#echo "$MESSAGE"|/usr/bin/mail -a "From: backup" -s "Backup: $HOSTNAME" mail@email.de