#!/bin/bash
MasterIp=*
USER=root
PORT=3306
PASSWD=000000
DATE=`date +%Y-%m-%d`
#DATE_NOW=`date +%Y-%m-%d.%H%M`
DATE_NOW=`date +%Y-%m-%d`
OLDDATE=`date +%Y-%m-%d -d "30 days ago"`　
#全表備份
BACKUPDIR=/data/mysql_backup/fulltable
#單表tb_trcevt備份
datapath='/data/mysql_backup/tb_trcevt'
logpath='/data/mysql_backup/log'
backtable=tb_trcevt
backtable_db=bim#binlog
BINLOG=/data/binlogserver
DATA_DIR_binlog=/data/mysql/binlog
PATH=/usr/local/mysql/bin:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
export PATHfunction stop() {ps uax | grep mysqlbinlog | grep raw | awk '{print $2}' | xargs kill
}if [ ! -d ${logpath} ];thenmkdir -p ${logpath}
fiif [ ! -d ${BACKUPDIR} ];thenmkdir -p ${BACKUPDIR}
fiif [ ! -d ${datapath} ];thenmkdir -p ${datapath}
ficd ${BACKUPDIR}/
echo ”備份時間為${DATE},開始----” >> ${logpath}/dbback.log
mysqladmin -u${USER} -p${PASSWD} flush-logs
#mysqldump -u${USER} -p${PASSWD} -h${MasterIp} --flush-logs -A -R -E --triggers --master-data=2 --delete-master-logs  --single-transaction > full_${DATE_NOW}.sql
#mysqldump -u${USER} -p${PASSWD} -h${MasterIp} --flush-logs -A -R -E --triggers --master-data=2   --single-transaction > full_${DATE_NOW}.sql
mysqlpump -u${USER} -p${PASSWD} -h${MasterIp}  --single-transaction --default-character-set=utf8 --compress-output=LZ4 --default-parallelism=3  -A --routines --events --triggers > full_${DATE_NOW}.sqlif [[ $? == 0 ]];thencd $BACKUPDIR#為節約硬盤空間，將數據庫壓縮tar zcvf full_${DATE_NOW}.tar.gz full_${DATE_NOW}.sql > /dev/null 2>&1#刪除原始文件，只留壓縮后文件rm -f full_${DATE_NOW}.sqlecho "數據庫備份數據成功!!" >> ${logpath}/dbback.log
elseecho "數據庫備份數據失敗!!" >> ${logpath}/error.log
ficd ${BACKUPDIR}/
echo ”備份時間為${DATE},開始----” >> ${logpath}/tableback.logmysqldump  -t $backtable_db -h${VIP} --set-gtid-purged=OFF -u${USER} -p${PASSWD} --tables  ${backtable} >${datapath}/${backtable}.sql
mysql -u${USER} -p${PASSWD} -h${VIP} -D $backtable_db  -e 'TRUNCATE TABLE tb_trcevt'if [[ $? == 0 ]];thencd $datapath#為節約硬盤空間，將數據庫壓縮tar zcvf ${backtime}${backtable}.tar.gz ${backtable}.sql > /dev/null 2>&1#刪除原始文件，只留壓縮后文件rm -f ${datapath}/${backtable}.sqlecho "數據庫備份${backtable}表數據成功!!" >> ${logpath}/tableback.log
elseecho "數據庫備份${backtable}表數據失敗!!" >> ${logpath}/table_error.log
fi