Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.