mysql 全备+binlog 自动恢复shell脚本
来源:互联网 发布:贪吃蛇算法 编辑:程序博客网 时间:2024/06/05 05:10
mysql 全备+binlog自动恢复shell脚本
注意:
1、数据库全备的命名规则 bak_dbname_xxx_sql
2、BAKUPDIR存放所有的数据全备的文件的目录
3、BINLOGBAKDIR用与存放所有用到的binlog的目录
该脚本主要用于数据库故障时,进行及时的整库加日志的增量恢复,以求数据损失降低的最小值。
该脚本的主要思想:
1. 首先进行全库导入;
2. 通过查找二进制日志的pos来进行日志的恢复;
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 $DATABASEsucessfully">> ${RECOVERY_LOG}
31.else
32.echo "recovery $DATABASEfail">> ${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 $DATABASEbinlog sucessfully">> ${RECOVERY_LOG}
52.else
53.echo "recover $DATABASEbinlog 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
附脚本源代码:
full_db_log_imp.sh
# !/bin/bash
BASEDIR=/data/bak/test
BAKUPDIR=$BASEDIR/20121024
BINLOGBAKDIR=$BASEDIR/bin-log-bk
LOGDIR=$BASEDIR/log
RECOVERY_LOG=$LOGDIR/recovery.log
MYSQL=/usr/local/mysql/bin/mysql
MYSQLBINLOG=/usr/local/mysql/bin/mysqlbinlog
USER=root
PASS=root@centos
MYSQLIP=localhost
cd $BAKUPDIR
find $BAKUPDIR -name '*.gz'
gunzip $BAKUPDIR *.gz
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 "CHANGEMASTER TO MASTER_LOG_FILE"|awk -F "'" '{print $2}'`
BINLOGPOS=`head -n 50 $i |grep "CHANGEMASTER 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 $BASEDIR
#find ./ -type f -name "*bin*"|xargs -i basename {} > /tmp/binlog.tmp
find ./ -type f -name"*mysql-bin*" >/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
#exit 0
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 $DATABASE |$MYSQL -h$MYSQLIP -u$USER -p$PASS-f >> ${RECOVERY_LOG} 2>&1;then
echo $BINLOGPOS $RECBINLOGS
echo "recover $DATABASE binlogsucessfully" >> ${RECOVERY_LOG}
else
echo "recover $DATABASE binlogfail" >> ${RECOVERY_LOG}
exit;
fi
echo "#`date +'%Y-%m-%d %H:%M:%S'`:endrecover $DATABASE binlog" >> ${RECOVERY_LOG}
done
脚本执行后,恢复到的数据为:
- mysql 全备+binlog 自动恢复shell脚本
- Shell简单全备mysql脚本案例
- mysql备份恢复shell脚本
- mysql binlog恢复数据库
- MYSQL数据BINLOG恢复
- mysql binlog恢复数据
- mysql 基于binlog恢复
- 教你自动恢复MySQL数据库的日志文件(binlog)
- MySQL基于binlog文件的异地备份策略脚本--shell
- MySQL XtraBackup自动恢复脚本
- MySQL binlog分析脚本
- MySQL单机本地使用XtraBackup备份工具进行备份和恢复(全备+增备+binlog)_(可用)
- MySQL 实现每日全量备份,定时增量备份,自动恢复脚本
- mysql 自动 安装 shell脚本
- Shell脚本参数自动补全
- MySQL--binlog日志恢复数据
- mysql通过binlog恢复数据
- mysql 基于binlog进行恢复
- 感悟 谦卑
- C/C++中作用域详解
- 内存分配管理---堆 栈的区别与说明
- UML类图关系(泛化 、继承、实现、依赖、关联、聚合、组合)
- ShopEX更换域名后批量修改商品详细描述中的图片地址
- mysql 全备+binlog 自动恢复shell脚本
- C++的类型转换:static_cast、dynamic_cast、reinterpret_cast和const_cast
- Windows 8 Metro开发疑难杂症——导航
- Hadoop分布式环境下的数据抽样
- 图像处理里面调色板(Palette)是什么意思?
- Mdl内存实现之原理1
- c++ stl library 学习(3)
- spring ioc原理 (很经典不错的一篇关于spring的文章)
- 教你怎么教你怎么在word主动创立备份文件