mysql 全备+binlog 自动恢复shell脚本

xyaxlz

1、数据库全备的命名规则 bak_dbname_xxx_sql
2、BAKUPDIR 存放所有的数据全备的文件的目录
3、BINLOGBAKDIR 用与存放所有用到的binlog的目录

1. #!/bin/bash
   
2. BASEDIR=/data1/xxx/xxx/backuptest
   
3. BAKUPDIR=$BASEDIR/tmpbak
   
4. #BAKUPDIR=$BASEDIR/2012-03-27
   
5. BINLOGBAKDIR=$BASEDIR/binlog
   
6. LOGDIR=$BASEDIR/log
   
7. RECOVERY_LOG=$LOGDIR/recovery.log
   
8. MYSQL=/usr/local/mysql/bin/mysql
   
9. MYSQLBINLOG=/usr/local/mysql/bin/mysqlbinlog
   
10. USER=root
    
11. PASS=123
    
12. MYSQLIP=localhost
    
13. 
    
14. mkdir -p $LOGDIR
    
15. BAKUPSQL=`find $BAKUPDIR -type f -name "*sql" |xargs -i basename {}`
    
16. 
    
17. for i in $BAKUPSQL
    
18. do
    
19.         cd $BAKUPDIR
    
20. 
    
21. #########CHECK BINLOG POSITION############
    
22.         BINLOG=`head -n 50 $i |grep "CHANGE MASTER TO MASTER_LOG_FILE"|awk -F "'" '{print $2}'`
    
23.         BINLOGPOS=`head -n 50 $i |grep "CHANGE MASTER TO MASTER_LOG_FILE"|awk -F "=" '{print $3}'|sed 's/;//'`
    
24.         DATABASE=`echo $i|awk -F"_" '{print $2}'`
    
25. # echo $i $DATABASE $BINLOG $BINLOGPOS
    
26. 
    
27. ####BEGINE FULLBAKUP RECOVERY#############
    
28.         echo "#`date +'%Y-%m-%d %H:%M:%S'`: start recover $DATABASE " >> ${RECOVERY_LOG}
    
29.         if $MYSQL -h$MYSQLIP -u$USER -p$PASS < $i >> ${RECOVERY_LOG} 2>&1;then
    
30.                 echo "recovery $DATABASE sucessfully" >> ${RECOVERY_LOG}
    
31.         else
    
32.                 echo "recovery $DATABASE fail" >> ${RECOVERY_LOG}
    
33.                 exit;
    
34.         fi
    
35.         echo "#`date +'%Y-%m-%d %H:%M:%S'`: end recover $DATABASE" >> ${RECOVERY_LOG}
    
36. 
    
37. 
    
38. #####BEGINE BINLOGBAKUP RECOVERY##########
    
39. 
    
40.         cd $BINLOGBAKDIR
    
41.         find . -type f -name "*bin*" |xargs -i basename {} > /tmp/binlog.tmp
    
42.         sort -n /tmp/binlog.tmp > $LOGDIR/binlog.tmp2
    
43.         sed '/md5/d' $LOGDIR/binlog.tmp2 > $LOGDIR/binlog.tmp3
    
44.         sed -n "/$BINLOG/,\$p" $LOGDIR/binlog.tmp3 > $LOGDIR/binlog${DATABASE}.log
    
45.         RECBINLOGS=`awk '{printf "%s"," "$1}' $LOGDIR/binlog${DATABASE}.log `
    
46.         #echo $DATABASE $BINLOGPOS $RECBINLOGS
    
47. 
    
48.         echo "#`date +'%Y-%m-%d %H:%M:%S'`: start recover $DATABASE binlog " >> ${RECOVERY_LOG}
    
49.         echo " $MYSQLBINLOG --no-defaults --disable-log-bin --start-position=$BINLOGPOS $RECBINLOGS -d $DATABASE |$MYSQL -h$MYSQLIP -u$USER -p$PASS -f " >> ${RECOVERY_LOG}
    
50.         if $MYSQLBINLOG --no-defaults --disable-log-bin --start-position=$BINLOGPOS $RECBINLOGS -d $DATABASE |$MYSQL -h$MYSQLIP -u$USER -p$PASS -f >> ${RECOVERY_LOG} 2>&1;then
    
51.                 echo "recover $DATABASE binlog sucessfully" >> ${RECOVERY_LOG}
    
52.         else
    
53.                 echo "recover $DATABASE binlog fail" >> ${RECOVERY_LOG}
    
54.                 exit;
    
55.         fi
    
56.         echo "#`date +'%Y-%m-%d %H:%M:%S'`:end recover $DATABASE binlog" >> ${RECOVERY_LOG}
    
57. 
    
58. 
    
59. done

复制代码