#!/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/mysql/bin

export PATH

HOST='127.0.0.1'

USER=root

PASS='XXXXXX'

PORT=33060

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

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

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/db33060/ -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 'NR>1'`

DB_FILE="hyd2_to_hyd4  water ask mysql"

mkdir -p /webdata/db33060/$DB_BAK_DATE

BAK_FILE_DIR=/webdata/db33060/$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 --set-gtid-purged=OFF $sql_file  2>/dev/null|sed 's/AUTO_INCREMENT=[0-9]*\s//g'|gzip >$BAK_FILE_DIR/$sql_file.gz

              done

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