mysql binlog 备份恢复_mysql全备份与binlog 自动恢复的shell脚本

#!/bin/bash

BASEDIR=/data1/xxx/xxx/backuptest

BAKUPDIR=$BASEDIR/tmpbak

#BAKUPDIR=$BASEDIR/2012-03-27

BINLOGBAKDIR=$BASEDIR/binlog

LOGDIR=$BASEDIR/log

RECOVERY_LOG=$LOGDIR/recovery.log

MYSQL=/usr/local/mysql/bin/mysql

MYSQLBINLOG=/usr/local/mysql/bin/mysqlbinlog

USER=root

PASS=123

MYSQLIP=localhost

mkdir -p $LOGDIR

BAKUPSQL=`find $BAKUPDIR -type f -name "*sql" |xargs -i basename {}`

for i in $BAKUPSQL

do

cd $BAKUPDIR

#---CHECK BINLOG POSITION#

BINLOG=`head -n 50 $i |grep "CHANGE MASTER TO MASTER_LOG_FILE"|awk -F "'" '{print $2}'`

BINLOGPOS=`head -n 50 $i |grep "CHANGE MASTER TO MASTER_LOG_FILE"|awk -F "=" '{print $3}'|sed 's/;//'`

DATABASE=`echo $i|awk -F"_" '{print $2}'`

# echo $i $DATABASE $BINLOG $BINLOGPOS

#---BEGINE FULLBAKUP RECOVERY#

echo "#`date +'%Y-%m-%d %H:%M:%S'`: start recover $DATABASE " >> ${RECOVERY_LOG}

if $MYSQL -h$MYSQLIP -u$USER -p$PASS < $i >> ${RECOVERY_LOG} 2>&1;then

echo "recovery $DATABASE sucessfully" >> ${RECOVERY_LOG}

else

echo "recovery $DATABASE fail" >> ${RECOVERY_LOG}

exit;

fi

echo "#`date +'%Y-%m-%d %H:%M:%S'`: end recover $DATABASE" >> ${RECOVERY_LOG}

#BEGINE BINLOGBAKUP RECOVERY#

cd $BINLOGBAKDIR

find . -type f -name "*bin*" |xargs -i basename {} > /tmp/binlog.tmp

sort -n /tmp/binlog.tmp > $LOGDIR/binlog.tmp2

sed '/md5/d' $LOGDIR/binlog.tmp2 > $LOGDIR/binlog.tmp3

sed -n "/$BINLOG/,\$p" $LOGDIR/binlog.tmp3 > $LOGDIR/binlog${DATABASE}.log

RECBINLOGS=`awk '{printf "%s"," "$1}' $LOGDIR/binlog${DATABASE}.log `

#echo $DATABASE $BINLOGPOS $RECBINLOGS

echo "#`date +'%Y-%m-%d %H:%M:%S'`: start recover $DATABASE binlog " >> ${RECOVERY_LOG}

echo " $MYSQLBINLOG --no-defaults --disable-log-bin --start-position=$BINLOGPOS $RECBINLOGS -d $DATABASE |$MYSQL -h$MYSQLIP -u$USER -p$PASS -f " >> ${RECOVERY_LOG}

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

echo "recover $DATABASE binlog sucessfully" >> ${RECOVERY_LOG}

else

echo "recover $DATABASE binlog fail" >> ${RECOVERY_LOG}

exit;

fi

echo "#`date +'%Y-%m-%d %H:%M:%S'`:end recover $DATABASE binlog" >> ${RECOVERY_LOG}

done

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值