xtrabackup自动全备份脚本

2022-07-20 12:36:10

字体：大中小

来源：转载

供稿：网友

　　xtrabackup自动全备份脚本,具有发送邮件和主从判断功能.
　　##set environment##
　　. ~/.bash_profile
　　BASEDIR="/data_bak"
　　SPACE_RATE=95        #space rate 95
　　SPACE_ROOM=10485760 #10GB
　　WORKPATH=/data_bak/mysqlbackup/percona
　　DATETIME=`date '+%Y%m%d%H%M'`
　　INNOBACKUPEX="/usr/bin/innobackupex-1.5.1"
　　MYSQL="/home/mysqlapp/product/bin/mysql"
　　BACKUP_USER="root"
　　BACKUP_PASSWD="******"
　　BACKUP_HOST="localhost"
　　BACKUP_PORT="3306"
　　DEFAULTS_FILE="/home/mysqlapp/config/my.cnf"
　　SOCKET="/home/mysqlapp/dbdata/mysqld.sock"
　　DATE_VAR=`date +%F_%H-%M-%S`

　　MAIL_LIST="xianyezhao@richinfo.cn"
　　MAIL_BIN="/home/crond/bsmtp"
　　MAIL_IP="*****"

　　#sent mail:0 error backup; 1 success backup
　　my_sentmail()
　　{
　　if [ $1 == 1 ]; then
　　   echo " " | ${MAIL_BIN} -f `hostname`@139.com -h smtp.api.localdomain -s "${MAIL_IP}:Succes Innobackupex backup" ${MAIL_LIST} < ${WORKPATH}/maillog/mail_dba_${WORKDATE}.log
　　else
　　   echo " " | ${MAIL_BIN} -f `hostname`@139.com -h smtp.api.localdomain -s "${MAIL_IP}:Error Innobackupex backup" ${MAIL_LIST} < ${WORKPATH}/maillog/mail_dba_${WORKDATE}.log
　　fi
　　}

　　# Step 1: if slave status is ok,then backup the databases,else send error information and exit
　　$MYSQL -u$BACKUP_USER -h$BACKUP_HOST -p$BACKUP_PASSWD -Bse"show slave status /G">${WORKPATH}/slave_status.txt
　　SLAVE_IO_RUNNING_STATUS=`cat ${WORKPATH}/slave_status.txt|grep Slave_IO_Running|cut -d: -f2|sed s/[[:space:]]//g`
　　SLAVE_SQL_RUNNING_STATUS=`cat ${WORKPATH}/slave_status.txt|grep Slave_SQL_Running|cut -d: -f2|sed s/[[:space:]]//g`

　　if [ ${SLAVE_IO_RUNNING_STATUS} != Yes ]; then
　　echo "SLAVE_IO_RUNNING_STATUS is not Yes">${WORKPATH}/maillog/mail_dba_${WORKDATE}.log
　　my_sentmail 0
　　exit 0
　　fi

　　if [ ${SLAVE_SQL_RUNNING_STATUS} != Yes ]; then
　　echo "SLAVE_SQL_RUNNING_STATUS is not Yes">${WORKPATH}/maillog/mail_dba_${WORKDATE}.log
　　my_sentmail 0
　　exit 0
　　fi

　　# Step 2: To check the backup work directory room
　　# if nfs then $1 and $4
　　SPACE_ROOM_CK=`df -k | grep "$BASEDIR" | awk '{print $2}'`
　　SPACE_USED_RATE=`df -k | grep "$BASEDIR" | awk '{print $5}' |awk -F% '{print $1}'`

　　if [ ${SPACE_USED_RATE} -lt ${SPACE_RATE} ] && [ ${SPACE_ROOM_CK} -gt ${SPACE_ROOM} ]
　　then
　　    echo "There have enough room for backup,let goto backup our database now" >> ${WORKPATH}/log/${BACKUP_FILE_NAME}_$WORKDATE.log
　　else
　　    echo "There have not enough room for our backup work,sadly to heard that" >> ${WORKPATH}/log/${BACKUP_FILE_NAME}_$WORKDATE.log
　　    echo -e "The backup task fail cause for there have not enough space room for backup on directory /nDatabase's IP is ${BACKUP_HOST}" > ${WORKPATH}/maillog/mail_dba_${WORKDATE}.log
　　    my_sentmail 0
　　    exit 0
　　fi

　　##Step 3:rm dmp file before 2 copys
　　cd $WORKPATH/$1
　　keepday=`ls -l|grep MYSQLBACKUP|wc -l`
　　if [ $keepday -gt 1 ]
　　then
　　 rm -fr `ls -lt|grep MYSQLBACKUP|tail -n 1|awk '{print $9}'`
　　fi

　　##Step 4:make dir
　　cd $WORKPATH/$1
　　TMPDIR="MYSQLBACKUP"`date '+%Y%m%d'`
　　if [ ! -f ${TMPDIR} ]
　　then
　　mkdir ${TMPDIR}
　　fi

　　#Step 5:to backup
　　cd $TMPDIR
　　${INNOBACKUPEX} --user=${BACKUP_USER} --password=${BACKUP_PASSWD} --defaults-file=${DEFAULTS_FILE} --socket=${SOCKET} $WORKPATH/$1/${TMPDIR}/ 2>$WORKPATH/log/$1_${DATE_VAR}.log

　　# echo The success info to the send mail information file
　　echo `date '+%Y%m%d%H%M'` > ${WORKPATH}/maillog/mail_dba_${WORKDATE}.log
　　echo "Today backup success. " >> ${WORKPATH}/maillog/mail_dba_${WORKDATE}.log
　　echo `hostname`" for databases:"${ALL_DATABASES}>> ${WORKPATH}/maillog/mail_dba_${WORKDATE}.log
　　echo "Database's IP is ${BACKUP_HOST}">> ${WORKPATH}/maillog/mail_dba_${WORKDATE}.log

　　echo `date '+%Y%m%d%H%M'` >> ${WORKPATH}/log/${BACKUP_FILE_NAME}_$WORKDATE.log
　　echo "Today backup success! " >> ${WORKPATH}/log/${BACKUP_FILE_NAME}_$WORKDATE.log
　　echo `hostname`" for databases:"${ALL_DATABASES} >> ${WORKPATH}/log/${BACKUP_FILE_NAME}_$WORKDATE.log
　　echo "Database's IP is ${BACKUP_HOST}">> ${WORKPATH}/log/${BACKUP_FILE_NAME}_$WORKDATE.log
　　my_sentmail 1
　　exit 0

（编辑：错新网）

上一篇：30分钟进门MyBatis

下一篇：为什么说sap软件对ic设计企业重要呢？