Xtrabackup automatic full backup script
Xtrabackup automatic full backup script, with sending mail and master-slave judgment function.
# # 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 +% Flying% Hmurf% Mmury% 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
