#!/bin/bash

###update on 20150604 ,add delete AUTO_INCREMENT 

###update on 20151008,add set-gtid-purged=OFF to delete @@global.gtid_purged statements in the bak file

###201705281958 增加备份一些选项，如:--singel-transaction 增加备份一致性，--master-data 在备份中打印binlog postion,有助于结合binlog日志恢复

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/webdata/opt/local/percona/bin

export PATH

HOST='localhost'

USER=root

PASS='xxxx'

PORT=3306

SOCK=/webdata/opt/local/percona/misc/mysql.sock

#OPS='--single-transaction --master-data=2 --routines --flush-privileges --skip-add-drop-table --flush-logs'

OPS='--single-transaction --master-data=2 --routines --flush-privileges --skip-add-drop-table  -R -E'

DB_BAK_DATE=`date +%Y%m%d`

DISK_UTILS=`df -hP|awk '/webdata/ {gsub(/[^0-9]+/,"",$5);print $5}'`

            if [ $DISK_UTILS -gt 93 ];then

                 find /webdata/db3306/ -type d -name "201*" -mtime +60 -exec rm -rf {} \;

           fi

DB_FILE=`mysql -h $HOST -u $USER  -p$PASS -P $PORT -S $SOCK -e 'show databases' 2>/dev/null|awk '/mysql|eddb|fddb|gddb|iddb|mddb|oddb|wetool/NR>1'|xargs`

mkdir -p /webdata/db3306/$DB_BAK_DATE

BAK_FILE_DIR=/webdata/db3306/$DB_BAK_DATE

echo "start db backup @`date +%Y-%m-%d" "%T`" >>$(dirname $BAK_FILE_DIR)/backup_log

               #for sql_file in $DB_FILE;do

                           #mysqldump -h $HOST -u $USER  -p$PASS --default-character-set=utf8 --set-gtid-purged=OFF $sql_file --ignore-table=$sql_file.exchange_ticket_bak 2>/dev/null|sed 's/AUTO_INCREMENT=[0-9]*\s//g'|gzip >$BAK_FILE_DIR/$sql_file.gz

     mysqldump -h $HOST -u $USER  -p$PASS -P $PORT -S $SOCK --default-character-set=utf8 $OPS -B $DB_FILE  2>/dev/null|sed 's/AUTO_INCREMENT=[0-9]*\s//g'|gzip >$BAK_FILE_DIR/all.gz

              #done

#scp -P 16868 -rp $BAK_FILE_DIR gitlab.huigujia.cn:/dbback/bkcq

echo "finsh db backup @`date +%Y-%m-%d" "%T`" >>$(dirname $BAK_FILE_DIR)/backup_log