#!/bin/bash
BACKUPUSER="backup_user"
BACKUPPASS="PASSWORD"
BACKUPPATH="/home/backup/mysql"
MYDATE="$(date +%Y%m%d)"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
MYCNF="/etc/mysql/my.cnf"
ZIP="/bin/bzip2"
ZIPOPS="-9f"
IGNOREDDBB="Database|mysql|information_schema"
 
DBLIST=""
CHMOD="440"
CHOWN="root:admins"
 
BINLOGINDEX="/home/mysql/log/mysql-bin.index"
 
list_databases()
{
        DBLIST="`echo "show databases ;" | $MYSQL -u "$BACKUPUSER" --password="$BACKUPPASS" | egrep -v "$IGNOREDDBB"`"
         return 1
}
 
dump_databases()
{
        for i in $DBLIST
        do
                $MYSQLDUMP --master-data=2 -u "$BACKUPUSER" --password="$BACKUPPASS" $i > $BACKUPPATH/$i-$MYDATE.sql
                $ZIP $ZIPOPS $BACKUPPATH/$i-$MYDATE.sql
        done
        return 1
}
 
dump_grants()
{
        mysql -p$BACKUPPASS --batch --skip-column-names --execute="SELECT DISTINCT CONCAT('SHOW GRANTS FOR ',user,'@\'',host,'\';') AS query FROM user" mysql | mysql -p$BACKUPPASS --batch --skip-column-names mysql | perl -p -e '$_ =~ s/$/;/; END { print "FLUSH PRIVILEGES;n" }' > $BACKUPPATH/grants-$MYDATE.sql
        $ZIP $ZIPOPS $BACKUPPATH/grants-$MYDATE.sql
}
 
 
binlog_backup()
{
        local let LINES=$(cat $BINLOGINDEX | wc -l)
        let LINES--
        tar cjfv $BACKUPPATH/MYSQL_BINLOGS-$MYDATE.tar.bz2 $(head -$LINES $BINLOGINDEX | xargs)
}
 
 
 
purge_binlogs()
{
        local LOGBIN="$(cat $MYCNF | grep -v ^# | grep log_bin | awk -F= '{print $2}')"
        local BINLOGNAME="$(basename $LOGBIN | awk -F. '{print $1}')"
        local BINLOGPATH="$(dirname $LOGBIN)"
        local let MINAGE="$(cat $MYCNF | grep -v ^# | grep expire | awk -F\= '{print $2}')"
        let MINAGE=$((${MINAGE}+2))
        local LASTBINLOG="$(find $BINLOGPATH -mtime +$MINAGE -name "*$BINLOGNAME*" | tail -1)"
        if [[ "$LASTBINLOG" ]]
        then
                local LASTBINLOG="$(basename $LASTBINLOG)"
                echo "PURGE BINARY LOGS TO "$LASTBINLOG";" | $MYSQL -u "$BACKUPUSER" --password="$BACKUPPASS"
        fi
}
 
list_databases
dump_databases
dump_grants
purge_binlogs
 
find $BACKUPPATH -type f -exec chmod $CHMOD {} ;
find $BACKUPPATH -type f -exec chown $CHOWN {} ;
 
exit 0