xtrabackup自动全备份脚本

痛定思痛。 2022-05-21 21:07 320阅读 0赞

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