How to query mysql's binlog log based on date

This article introduces the relevant knowledge of "how to query the binlog log of mysql according to the date". In the operation of the actual case, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

A script to query mysql's binlog log based on date

[binlog@m-mysql-binlogbak ~] $cat mysqllog.sh

#! / bin/bash

# script is used to query mysql's binlog log based on date

# the author is czxin788@qq.com

Read-p "Please enter the start date of the mysql log you want to query (format must be: 170728):" starttime

Read-p "Please enter the due date for querying the mysql log (format must be: 170728):" endtime

Read-p "Please enter what you want to query:" content

Echo "-"

Echo "is inquiring for you, it may take a long time, please wait patiently."

Echo "start time: `date +% Y-%m-%d_%H:% M`"

# get the file name of binlog

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

# use the for loop to determine the starting binlog log file name to be queried based on the start date

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} `

#

# use the for loop to determine the name of the binlog log file to be queried according to the due date

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`

# output the binlog file name list to be queried

# 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 "end time: `date +% Y-%m-%d_%H:% M`"

Echo "has been completed, the content you want to query has been put in / home/binlog/$resultlog, please execute the sz / home/binlog/$resultlog command to download to your notebook to check!"

[binlog@m-mysql-binlogbak ~] $sh mysqllog.sh

You can use it.

This is the end of the content of "how to query the binlog log of mysql by date". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!