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

发布时间:2019-10-05编辑:脚本学堂
本文介绍下,用于对mysql数据进行全备份,以及根据binlog日志进行数据恢复的一段shell脚本,有需要的朋友参考下。

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

脚本内容:
 

复制代码 代码示例:

#!/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

您可能感兴趣的文章:
清理多台MySQL数据库的过期binlog日志的shell脚本
批量清除过期的binlog日志释放磁盘空间的shell脚本
mysql binlog 自动清理脚本一例
批量清除128组节点db上过期的binlog释放磁盘空间的shell脚本