根據日期來查詢mysql的binlog日誌的指令碼

#!/bin/bash

#本指令碼是用來根據日期來查詢mysql的binlog日誌

#the author is  czxin788@qq.com

read -p "請輸入您要查詢mysql日誌的起始日期(格式必須是:170728):" starttime

read -p "請輸入您要查詢mysql日誌的截止日期(格式必須是:170728):" endtime

read -p "請輸入您要查詢的內容:" content

echo "-------------------"

echo "正在為您查詢，時間可能會比較長，請您耐心等候....."

echo "開始時間:`date +%Y-%m-%d_%H:%M`"

#獲取binlog的檔名

binname=`ls /opt/mysqlbinlog/ |xargs`

#用for 迴圈根據開始日期確定要查詢的起始binlog日誌檔名

for i in `ls /opt/mysqlbinlog/`

do

 mysqlbinlog --base64-output=decode-rows -v -v -v /opt/mysqlbinlog/$i |head -50 |grep `expr $starttime - 1` > /dev/null 2>&1

 if [ $? -eq 0 ];then

  echo $i > /tmp/startbin.txt

  break

 fi

done

startbinlog=`cat /tmp/startbin.txt`

fistname=`echo $startbinlog ${binname##*$startbinlog}`

#######

#用for 迴圈根據截止日期確定要查詢的截止binlog日誌檔名

for j in `ls /opt/mysqlbinlog/`

do

 mysqlbinlog --base64-output=decode-rows -v -v -v /opt/mysqlbinlog/$j |head -50 |grep `expr $endtime + 1` > /dev/null 2>&1

 if [ $? -eq 0 ];then

  echo $j > /tmp/endbin.txt

  break

 fi

done

endbinlog=`cat /tmp/endbin.txt`

lastname=`echo ${fistname%%$endbinlog*} $endbinlog`

#輸出最終要查詢的binlog檔名list

#echo the result name:$lastname

cd /opt/mysqlbinlog/

resultlog=${content}_`date +%m%d%H%M%S`.log

mysqlbinlog --base64-output=decode-rows -v -v -v $lastname |grep -C 200 -i $content > /home/binlog/$resultlog

echo "-------------------"

echo "結束時間:`date +%Y-%m-%d_%H:%M`"

echo "已完成，您要查詢的內容已經放在/home/binlog/$resultlog裡面，請執行sz /home/binlog/$resultlog命令下載到自己筆記本上檢視!"

[binlog@m-mysql-binlogbak ~]$ sh  mysqllog.sh
就可以使用了