#!/bin/bash
#Author:BrandynX
#Create date:2015-03-26
#Version: v1.1
#Modify:
Dpath="/data/Backup_MySQL"
Username="root"
Password="RvF9YzGhksfvwTJz"
Xmysql="/app/mysql/bin/mysql -u${Username} -p${Password} -S /tmp/mysql3306.sock"
Xmysqldump="/app/mysql/bin/mysqldump -u${Username} -p${Password} -S /tmp/mysql3306.sock"
Savedays="1"
Today=`date +%Y%m%d`
Old_backup_dir_day=`date -d "-${Savedays} day" +%Y%m%d`
REMOTE_DIR=/nfsdir/mysql
NFSDAYS="7"
NFS_BACKUP_DAY=`date -d "-${NFSDAYS} day" +%Y%m%d`

#Clear Old_backup_dir
if [ -d $Dpath/$Old_backup_dir_day ];then
        rm -rf $Dpath/$Old_backup_dir_day
    rm -f $Dpath/$Old_backup_dir_day-mysql.tar.gz
fi

#Create Backup Dir
if [ ! -d $Today  ];then
        mkdir -p $Dpath/$Today
        cp /data/mysql3306/my.cnf $Dpath/$Today
fi

#MySQL Get Database List
$Xmysql -e "show databases;" | grep -vE "(Database|_schema)" > $Dpath/db_list

#MySQL Create Backup Database Dir
while read Dbname 
do              
        if [ ! -d $Dpath/$Today/$Dbname  ];then
                mkdir -p $Dpath/$Today/$Dbname
        fi

        #MySQL Get Table Name
        $Xmysql -e "use $Dbname;show tables;" | grep -vE "(Tables_in|general_log|slow_log)" > $Dpath/table_list

        while read Tablename
        do
                #Backup Table
                  $Xmysqldump $Dbname $Tablename --set-gtid-purged=OFF > $Dpath/$Today/$Dbname/$Tablename.sql
        done < $Dpath/table_list

        #Backup Routines/triggers/Events
    $Xmysqldump $Dbname -d  --triggers --routines --events --set-gtid-purged=OFF  > $Dpath/$Today/$Dbname/triggers_events_routines_nodata.sql

done < $Dpath/db_list


#Clear Process file
rm -f $Dpath/db_list
rm -f $Dpath/table_list

#Synchronize files to nfs
#Compress the backup file
cd $Dpath 
tar czf $Today-mysql.tar.gz $Today
cp $Today-mysql.tar.gz $REMOTE_DIR


#Delete NFS backup file
rm -f  $REMOTE_DIR/$NFS_BACKUP_DAY-mysql.tar.gz