导出bin log时间段脚本datarecover.sh

 

修改 _binlogdir='/data/mysql/mysql_3301/mysql-bin'

 

bin log实例格式为：mysql-bin.000002

 

导出该目录下all.sql

 

 

执行：

#./datarecover.sh

输入起始日期：2012-09-15 00:00:00

输入起始日期：2012-09-15 23:59:59

输入导出的库名：直接回车导出所有

 

 

#! /bin/bash
_binlogdir='/data/mysql/mysql_3301/mysql-bin'
_fristbinlog=`ls -r $_binlogdir* |tail -1`
_lastbinlog=`ls $_binlogdir* |tail -2|grep -v index`
_fristbinlogtime=`stat "$_fristbinlog" |grep Modify|awk -F . '{print $1}'|awk -F Modify: '{print $2}'`
_lastbinlogtime=`stat "$_lastbinlog"  |grep Modify|awk -F . '{print $1}'|awk -F Modify: '{print $2}'`
echo "================================================================"
echo " 此程序只能导出 $_fristbinlogtime - $_lastbinlogtime 的sql语句  "
echo "================================================================"
read -p "请输入开始时间 Please enter begian time (such as 2010-01-01 09:23:01) :" fristbegiantime
read -p "请输入结束时间 Please enter over  time (such as 2010-10-10 09:23:01) :" fristovertime
read -p "请输入要导出的库，不填此项为全选 ：" dbname
_fristNo=`echo $_fristbinlog|awk -F . '{print $2}'`
_lastNo=`echo $_lastbinlog|awk -F . '{print $2}'`
begiantime=`date -d "$fristbegiantime" +%s`
overtime=`date -d "$fristovertime" +%s`
echo `seq -f %06g $_fristNo $_lastNo`
for No in `seq -f %06g $_fristNo $_lastNo`
do
_alltime=`stat $_binlogdir.$No|grep Modify|awk -F . '{print $1}'|awk -F Modify: '{print $2}'`
echo $_alltime
_alltime=`date -d "$_alltime" +%s`
echo $_alltime
echo =========================
_begchazhi=$(($begiantime - $_alltime ))
_lachazhi=$(($overtime - $_alltime))
echo $_begchazhi
echo $_lachazhi
echo ===============================
if [ $_begchazhi -gt 0 ] && [ $_begchazhi -lt 3600 ];then
_begianNo=$No
echo $_begianNo
fi
if [ $_lachazhi -gt 0 ]&& [ $_lachazhi -lt 3600 ];then
_lastNo=$No
echo $_lastNo
fi
done
if [ -z $dbname  ];then
#for No in `seq -f %06g $_begianNo $_lastNo`
for No in `seq  -f %06g $_fristNo $_lastNo`
do
#echo $No
mysqlbinlog --start-datetime="$fristbegiantime" --stop-datetime="$fristovertime" $_binlogdir.$No  >> all.sql
done
else
#for No in `seq  -f %06g $_begianNo $_lastNo`
for No in `seq  -f %06g $_fristNo $_lastNo`
do
mysqlbinlog --start-datetime="$fristbegiantime" --stop-datetime="$fristovertime" --database $dbname  $_binlogdir.$No >>/$dbname.sql
done
fi