#! /bin/bash

#35  02 * * *  mysql /data/mysqldata/scripts/mysqldump_per_db.sh  >> /data/mysql/backup/mysql_back1.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

mysqlHost=127.0.0.1

HOST_PORT=3306

logicuser=****

logicpass=***

logicalBackupDir=/data/mysqldata/backup/logical

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

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

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 "

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

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

echo >> ${logfile}

#list="mysql aquarius"

 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

    # -------------------------- 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

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