#! /bin/bash

#05  01,03,12 * * *  mysql /data/mysqldata/scripts/mysql_backup_dump_xtra.sh  >> /data/mysql/backup/log/mysql_back2.log 2>&1

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

# Define the variables the script used for executing.

#mysql backup by hongquan on 2017

#day=`date +%u`

curHour=`date +%H`

curDate=`date +'%Y%m%d'`

backupDir=/data/mysqldata/backup

defaultFile=/data/mysqldata/backup

mysqlHost=127.0.0.1

port=3306

logicuser=**

logicpass=**

logicalBackupDir=/data/mysqldata/backup/logical

fullBackupDir=/data/mysqldata/backup/full

incBackupDir=/data/mysqldata/backup/inc

logfile=/data/mysqldata/backup/mysql_backup.log

sockfile=/data/mysqldata/3306/mysql.sock

full_inbkpex_day=/data/mysqldata/backup/full/full_ipx$curDate$curHour

inc_inbkpex=/data/mysqldata/backup/inc/inc_ipx$curDate$curHour

MYSQL_PATH=/usr/local/mysql/bin

MYSQL_CMD="${MYSQL_PATH}/mysql -u${logicuser} -p${logicpass} -h${mysqlHost} -S /data/mysqldata/${HOST_PORT}/mysql.sock"

MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${logicuser} -p${logicpass} -h${mysqlHost} -P${HOST_PORT}  -R -S  \

            ${sockfile} --single-transaction  --master-data=2 --default-character-set=utf8 --set-gtid-purged=OFF -q "

case ${curHour}  in

         01)

                type=logical

                ;;

         03)

                type=full

                ;;

         12)

                type=inc

                ;;

        "*")

                exit

esac

echo "NOW is : Backup type=$type" >> ${logfile}

    if [ ! -d "${backupDir}" ]; then

    mkdir -p "${backupDir}"

    echo -e "===${backupDir} does not exsits,created them!" >> ${logfile}

    fi

     if [ ! -d "${logicalBackupDir}" ]; then

    mkdir -p "${logicalBackupDir}"

    echo -e "===${logicalBackupDir} does not exsits,created them!" >> ${logfile}

    fi

     if [ ! -d "${fullBackupDir}" ]; then

    mkdir -p "${fullBackupDir}"

    echo -e "===${fullBackupDir} does not exsits,created them!" >> ${logfile}

    fi

    if [ ! -d "${incBackupDir}" ]; then

    mkdir -p "${incBackupDir}"

    echo -e "===${incBackupDir} does not exsits,created them!" >> ${logfile}

    fi

extra_lsndir=${backupDir}

ori_conf_file=/data/mysqldata/${port}/my.cnf

new_conf_file=${backupDir}/my_${port}_`date +%Y%m%d%H%M%S`.cnf

echo "backup start" `date` >> ${logfile}

echo -e "===first cp my.cnf file to backup directory ===" >> ${logfile}

/bin/cp ${ori_conf_file} ${new_conf_file}

echo >> ${logfile}

if [[ "${type}" = "logical" ]]; then

    # -------------------------- logical backup with mysqldump --------------------------

    echo -e "mysqldumpbk per db list= ${list} " >> ${logfile}

   for dbs in `${MYSQL_CMD} 2>/dev/null  -e "show databases" | sed '1d' | egrep -v "information_schema|performance_schema|sys"`

    do

      echo -e "echo $dbs is appoint ; " >> ${logfile}

      ${MYSQL_DUMP} ${dbs} 2>/dev/null | gzip >  ${logicalBackupDir}/${dbs}_${curDate}.sql.gz

      echo -e " **** database :${dbs} backup stop_time :`date +%F' '%T' '%w` **** \n">> ${logfile}

      echo -e "**** backup file size:`du -sh ${logicalBackupDir}/`**** \n" >> ${logfile}

    done

elif [[ "${type}" = "full" ]]; then

    # -------------------------- full backup with xtrabackup --------------------------

    cd ${backupDir}

    #rm -rf /data/mysql/backup/full/

    if [ ! -d "${full_inbkpex_day}" ]; then

    mkdir -p "${full_inbkpex_day}"

    echo -e "===${full_inbkpex_day} does not exsits,created them!" >> ${logfile}

    fi

    #tar -zcvf  $tar_full.tar.gz  ${full_inbkpex_day}

    cd ${full_inbkpex_day}

    /usr/bin/innobackupex  --defaults-file=${ori_conf_file} --user=${logicuser} --password=${logicpass} \

    --host=${mysqlHost}  --port=${port} --no-timestamp --extra-lsndir=${extra_lsndir}  --stream=tar  ${backupDir} \

    2>/tmp/full_ipx$curDate$curHour.log | gzip 1>${full_inbkpex_day}/`date +%Y%m%d%H%M%S`.tar.gz 

 echo -e "mysql full backup full_ipx$curDate$curHour" >> ${logfile}

elif [[ "${type}" = "inc" ]]; then

    # -------------------------- incremental backup with xtrabackup --------------------------

    if [ ! -d "${inc_inbkpex}" ]; then

    mkdir -p "${inc_inbkpex}"

    echo -e "===${inc_inbkpex} does not exsits,created them!" >> ${logfile}

    fi

    /usr/bin/innobackupex --defaults-file=${ori_conf_file} --user=${logicuser} --password=${logicpass} \

    --host=${mysqlHost}  --port=${port} --no-timestamp --extra-lsndir=${extra_lsndir} --incremental \

    --incremental-basedir=${backupDir} ${inc_inbkpex}  2>/tmp/inc_ipx$curDate$curHour.log

    echo -e "mysql inc backup inc_ipx$curDate$curHour" >> ${logfile}

else

    echo -e "wrong backup type!" >> ${logfile}

    exit 1

fi

#echo -e "----find expired backup and delete those files ----">> ${logfile}

#for tfile in $(/usr/bin/find ${backupDir}/ -mtime +7)

#do

#  if [ -d $tfile ] ; then

#    rmdir $tfile

#   elif [ -f $tfile ]; then

#   #rm -f $tfile

#    echo -e "---not backupDir found ----">>  ${logfile}

#  fi

#   echo -e "----delete file:$tfile ----">>  ${logfile}

#done

echo -e "\n=== backup dened at `date +%F' '%T' '%w`===\n" >> ${logfile}