mysqldump备份脚本

mysqldump备份脚本,具有发送备份状态给dba的功能
#!/bin/sh
#set -x
#-----------------------------------------------------------
# Usage: This script is used to backup the mysql database
#        which using for the mail of the 
#        ChinaMobile's Online Application Store Development
# Name: mysqldump_backup.sh
# Autor: xianyezhao
# modify:
# Create_date: 25/09/2014
# deploy date:20140925
#  sript location:/home/mysql/scripts/mysqlbackup/scripts
#chmod mysqldump_backup.sh to 711
# crontab info 0 1 * * * /home/mysql/scripts/mysqlbackup/scripts/mysqldump_backup.sh 
#-----------------------------------------------------------
#first ,we should create workdir
#export WORKPATH=/home/mysql/scripts/mysqlbackup
#mkdir  -p ${WORKPATH}
#mkdir  -p ${WORKPATH}/scripts
#mkdir  -p ${WORKPATH}/new_bk_dir
#mkdir  -p ${WORKPATH}/old_bk_dir
#mkdir  -p ${WORKPATH}/log
#mkdir  -p ${WORKPATH}/maillog


set -x
# configure the environment variables
. ~/.bash_profile
export TMPDIR=/tmp


# configure the work directory
GZIP_BIN=/usr/bin/gzip
MYSQL_DIR=/usr/bin
#WORKPATH mesns the workpath
WORKPATH=/home/mysql/scripts/mysqlbackup
WORKDATE=`date +%Y%m%d`
# BASEDIR means the filesystem mounted
BASEDIR="/boot" 
SPACE_RATE=85        #space rate 85
SPACE_ROOM=10485760  #10GB
BACKUP_FILE_NAME="mysqldump_backup"
MYSQLDUMP="/mysql/product/bin/mysqldump"
MYSQL="/mysql/product/bin/mysql"
BACKUP_USER="root"
BACKUP_PASSWD="******"
BACKUP_HOST="localhost"
BACKUP_PORT="3306"
ALL_DATABASES="$($MYSQL -u$BACKUP_USER -h$BACKUP_HOST  -p$BACKUP_PASSWD -Bse"select distinct table_schema from information_schema.tables where table_schema not in ('information_schema','performance_schema')")"
MAIL_LIST="xianyezhao@richinfo.cn xiejiawei@richinfo.cn"
MAIL_BIN="/home/crond/bsmtp"
MAIL_IP="******"


#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 Mysqldump 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 Mysqldump backup"  ${MAIL_LIST}  < ${WORKPATH}/maillog/mail_dba_${WORKDATE}.log
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: To backup the database
# delete old backup data,keep 4 copys 
cd $WORKPATH/old_bk_dir
keepday=`ls -l|grep mysqldump_backup|wc -l`
if [ $keepday -gt 2 ]
then
 rm -fr `ls -lt|grep mysqldump_backup|tail -n 1|awk '{print $9}'`
fi
# move the old backup data to the old backup storage directory
cd ${WORKPATH}/new_bk_dir
mv ${BACKUP_FILE_NAME}*.gz ${WORKPATH}/old_bk_dir


# start backup all databases
${MYSQLDUMP} -u${BACKUP_USER} -p${BACKUP_PASSWD}  -h${BACKUP_HOST} -P${BACKUP_PORT}  --databases ${ALL_DATABASES}> ${BACKUP_FILE_NAME}_${WORKDATE}.sql




# compress the backup data
${GZIP_BIN} -c ${BACKUP_FILE_NAME}_${WORKDATE}.sql > ${BACKUP_FILE_NAME}_${WORKDATE}.gz


# delete the uncompress backup data
rm -f ${BACKUP_FILE_NAME}_${WORKDATE}.sql




# 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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29114615/viewspace-1408353/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29114615/viewspace-1408353/