MySQL如何快速獲取binlog的開始時(shí)間和結(jié)束時(shí)間
2024-05-11 11:18 瀟湘隱者 閱讀(1661) 評論(0) 收藏 舉報(bào)之前寫過一篇文章MySQL如何獲取binlog的開始時(shí)間和結(jié)束時(shí)間[1],文章里面介紹了如何獲取MySQL數(shù)據(jù)庫二進(jìn)制日志（binlog）的開始時(shí)間與結(jié)束時(shí)間的一些方法。實(shí)際應(yīng)用當(dāng)中，我們可能還會(huì)遇到效率/性能方面的問題。最近對這個(gè)問題做了一些研究，這里就介紹一下如何快速獲取MySQL二進(jìn)制日志(binlog)的開始時(shí)間和結(jié)束時(shí)間。
我們下來看看當(dāng)MySQL二進(jìn)制日志（binlog）的Size很大的時(shí)候，獲取起開始時(shí)間和結(jié)束時(shí)間，如下測試所示
$ du -sh mysql_binlog.000105
1.1G mysql_binlog.000105
$ time mysqlbinlog mysql_binlog.000105 |grep "Start: binlog" | awk -F "server id" '{print $1}'
#240425 9:20:26
real 0m34.136s
user 0m25.941s
sys 0m11.985s
從上面實(shí)驗(yàn)可以看出，在MySQL二進(jìn)制日志（binlog）變大的情況下，這種方法需要34秒，非常低效和耗時(shí)，那么我們怎么提升性能呢？ 我們改寫一下腳本，如下所示
$ time mysqlbinlog mysql_binlog.000105 | head -10 | grep "Start: binlog" | awk -F "server id" '{print $1}'
#240425 9:20:26
real 0m0.010s
user 0m0.006s
sys 0m0.005s
如上所示，這樣改進(jìn)腳本后，性能效率已經(jīng)提升到0.01秒，已經(jīng)相當(dāng)?shù)母咝Я恕Ｄ敲传@取結(jié)束時(shí)間能否也可以這樣提升呢？ 很遺憾的是由于MySQL二進(jìn)制日志（binlog）的結(jié)束時(shí)間/滾動(dòng)時(shí)間(Rotate Time)位于文件的末尾，由于管道的一些基本特性，獲取MySQL二進(jìn)制日志(binlog)的結(jié)束時(shí)間無法通過上面方法來優(yōu)化，這里不打算介紹Linux管道相關(guān)概念，所以我們只需知道這么一個(gè)事實(shí)。
如果你對Linux管道的一些原理不是很清楚，那么就用實(shí)驗(yàn)測試驗(yàn)證一下，如下所示：
$ time mysqlbinlog mysql_binlog.000105 |grep Rotate | awk -F "server id" '{print $1}'
#240426 11:11:37
real 0m34.223s
user 0m27.202s
sys 0m11.551s
$ time mysqlbinlog mysql_binlog.000105 | tail -10 | grep Rotate | awk -F "server id" '{print $1}'
#240426 11:11:37
real 0m33.917s
user 0m25.528s
sys 0m11.395s
那么怎么來優(yōu)化獲取MySQL二進(jìn)制日志（binlog）的結(jié)束時(shí)間呢？經(jīng)過一番觀察與實(shí)驗(yàn)，我發(fā)現(xiàn)一個(gè)MySQL二進(jìn)制日志（binlog）的結(jié)束時(shí)間，就是下一個(gè)二進(jìn)制日志（binlog）的開始時(shí)間。如下實(shí)驗(yàn)所示
[mysql@dbtest04 bin_logs]$ ls -lrt
total 28
-rw-r----- 1 mysql mysql 207 May 9 15:25 mysql_binlog.000055
-rw-r----- 1 mysql mysql 207 May 9 15:27 mysql_binlog.000056
-rw-r----- 1 mysql mysql 207 May 10 11:02 mysql_binlog.000057
-rw-r----- 1 mysql mysql 207 May 10 11:34 mysql_binlog.000058
-rw-r----- 1 mysql mysql 207 May 10 11:38 mysql_binlog.000059
-rw-r----- 1 mysql mysql 157 May 10 11:38 mysql_binlog.000060
-rw-r----- 1 mysql mysql 246 May 10 11:38 mysql_binlog.index
[mysql@dbtest04 bin_logs]$ mysqlbinlog mysql_binlog.000055 | head -10 | grep "Start: binlog" | awk -F "server id" '{print $1}'
#240509 14:48:10
[mysql@dbtest04 bin_logs]$ mysqlbinlog mysql_binlog.000055 |grep Rotate | awk -F "server id" '{print $1}'
#240509 15:25:57
[mysql@dbtest04 bin_logs]$ mysqlbinlog mysql_binlog.000056 | head -10 | grep "Start: binlog" | awk -F "server id" '{print $1}'
#240509 15:25:57
[mysql@dbtest04 bin_logs]$ mysqlbinlog mysql_binlog.000056 |grep Rotate | awk -F "server id" '{print $1}'
#240509 15:27:37
[mysql@dbtest04 bin_logs]$ mysqlbinlog mysql_binlog.000057 | head -10 | grep "Start: binlog" | awk -F "server id" '{print $1}'
#240509 15:27:37
[mysql@dbtest04 bin_logs]$ mysqlbinlog mysql_binlog.000057 |grep Rotate | awk -F "server id" '{print $1}'
#240510 11:02:00
[mysql@dbtest04 bin_logs]$
如果全部符合這個(gè)規(guī)律的話，那么我們直接用下一個(gè)binlog的開始時(shí)間作為上一個(gè)binlog的結(jié)束時(shí)間即可，于是我寫了一個(gè)腳本find_binlog_start_end_time.sh:
#!/bin/bash
#########################################################################################
# #
# This script is used for get the binlog start time and end time #
# #
#########################################################################################
# #
# ScriptName : find_binlog_start_end_time.sh #
# Author : Kerry #
# CreateDate : 2024-05-10 #
# Email : kerry2008code@qq.com #
#***************************************************************************************#
# 參數(shù)配置 #
#---------------------------------------------------------------------------------------#
# 腳本參數(shù) binlog文件存放的路徑 #
#---------------------------------------------------------------------------------------#
# MYSQLBINLOG mysqlbinlog的位置，以防沒有設(shè)置環(huán)境變量 #
# BINLOG_BASENAME binlog的前綴名 #
#---------------------------------------------------------------------------------------#
# 注意事項(xiàng)： #
# 1：如果維護(hù)的MySQL數(shù)據(jù)庫都規(guī)范化安裝、配置的化，下面很多參數(shù)都不需要修改 #
#***************************************************************************************#
# Version Modified Date Description #
#***************************************************************************************#
# V.1.0 2024-05-10 創(chuàng)建此腳本 #
#########################################################################################
# mysqlbinlog的路徑，一般無需設(shè)置，以防沒有設(shè)置環(huán)境變量時(shí)
MYSQLBINLOG="/opt/mysql/mysql8.0/bin/mysqlbinlog"
BINLOG_BASENAME="mysql_binlog"
if [ $# = 0 ]
then
echo "find_binlog_start_end_time.sh Usage:"
echo "for eg: find_binlog_start_end_time.sh /data/mysql/binlogs"
exit
fi
BINLOG_FILE_PATH=$1
if [ ! -d $BINLOG_FILE_PATH ];then
echo "the folder $BINLOG_FILE_PATH does not exist, please check it!"
exit 1
fi
index=1
start_time=""
end_time=""
last_binlog_name=""
BINLOG_FILE_NUM=`ls -lrt $BINLOG_FILE_PATH | grep $BINLOG_BASENAME |grep -v $BINLOG_BASENAME.index | wc -l`
if [ $BINLOG_FILE_NUM -lt 1 ];then
echo "pelase check the binlog or the parameter of this script"
exit 1;
fi
cd $BINLOG_FILE_PATH
for binlog_file in `ls -rt | grep $BINLOG_BASENAME |grep -v $BINLOG_BASENAME.index`;
do
if [ $index -eq 1 ];then
start_time=`$MYSQLBINLOG $binlog_file | head -10 | grep "Start: binlog" | awk -F "server id" '{print $1}'`
last_binlog_name=$binlog_file
else
end_time=`$MYSQLBINLOG $binlog_file | head -10 | grep "Start: binlog" | awk -F "server id" '{print $1}'`
echo "file name:$last_binlog_name" , "start time:$start_time", "end time:$end_time"
if [ $index -eq $BINLOG_FILE_NUM ];then
last_end_time=`$MYSQLBINLOG $binlog_file |tail -10 | egrep "Rotate|Stop" | awk -F "server id" '{print $1}'`
echo "file name:$binlog_file" , "start time:$end_time", "end time:$last_end_time"
else
start_time=$end_time
last_binlog_name=$binlog_file
fi
fi
let index++
done
如下測試所示
[mysql@dbtest04 kerry]$ sh find_binlog_start_end_time.sh /data/mysql/bin_logs/
file name:mysql_binlog.000055 , start time:#240509 14:48:10 , end time:#240509 15:25:57
file name:mysql_binlog.000056 , start time:#240509 15:25:57 , end time:#240509 15:27:37
file name:mysql_binlog.000057 , start time:#240509 15:27:37 , end time:#240510 11:02:00
file name:mysql_binlog.000058 , start time:#240510 11:02:00 , end time:#240510 11:34:01
file name:mysql_binlog.000059 , start time:#240510 11:34:01 , end time:#240510 11:38:24
file name:mysql_binlog.000060 , start time:#240510 11:38:24 , end time:#240510 16:45:34
file name:mysql_binlog.000061 , start time:#240510 16:45:34 , end time:
另外，還有一種比較高效的方法是解析二進(jìn)制日志的頭部信息（此篇文章統(tǒng)統(tǒng)指binlog v 4），因?yàn)閎inlog的頭部由固定的4個(gè)字節(jié)組成，而頭部信息的FORMAT_DESCRIPTION_EVENT部分包含了binlog的開始時(shí)間，我在搜索/學(xué)習(xí)相關(guān)資料時(shí)，結(jié)果發(fā)現(xiàn)有人已經(jīng)總結(jié)過這方面的內(nèi)容，而且已經(jīng)有相關(guān)Python腳本或shell腳本了，這里就重復(fù)造輪子了，Python腳本來自MySQL 查詢binlog生成時(shí)間[2]我們來看看實(shí)驗(yàn)結(jié)果，如下所示
$ python3 check_bintime.py /data/mysql/bin_logs/mysql_binlog.index
{'file_name': 'mysql_binlog.000055', 'binlog_size': '207.0 B', 'start_time': '2024-05-09 14:48:10', 'end_time': '2024-05-09 15:25:57'}
{'file_name': 'mysql_binlog.000056', 'binlog_size': '207.0 B', 'start_time': '2024-05-09 15:25:57', 'end_time': '2024-05-09 15:27:37'}
{'file_name': 'mysql_binlog.000057', 'binlog_size': '207.0 B', 'start_time': '2024-05-09 15:27:37', 'end_time': '2024-05-10 11:02:00'}
{'file_name': 'mysql_binlog.000058', 'binlog_size': '207.0 B', 'start_time': '2024-05-10 11:02:00', 'end_time': '2024-05-10 11:34:01'}
{'file_name': 'mysql_binlog.000059', 'binlog_size': '207.0 B', 'start_time': '2024-05-10 11:34:01', 'end_time': '2024-05-10 11:38:24'}
{'file_name': 'mysql_binlog.000060', 'binlog_size': '180.0 B', 'start_time': '2024-05-10 11:38:24', 'end_time': '2024-05-10 16:45:34'}
{'file_name': 'mysql_binlog.000061', 'binlog_size': '157.0 B', 'start_time': '2024-05-10 16:45:34', 'end_time': 'now'}
shell腳本來自一種快速取得binlog開始時(shí)間的方法[3]:
[mysql@dbtest04 kerry]$ function getBinlogStartTime()
> {
> theFile="$1"
> #取出文件頭做分析
> binlogHead=`hexdump ${theFile} | head -1`
> #binlog文件校驗(yàn)
> binlogCrc=`echo $binlogHead | awk '{print $1$2$3}'`
> if [ "${binlogCrc}" != '000000062fe6e69' ]; then
> echo '${theFile} is damage.'
> exit 1
> fi
> #計(jì)算binlog的開始時(shí)間
> binlogBeginTimeInt=`echo $binlogHead | awk '{print $5$4}' | gawk ' { printf strtonum("0x" $0)}' `
> binlogBeginTime=`date -d "1970-01-01 UTC $binlogBeginTimeInt seconds" "+%F %T"`
> echo $binlogBeginTime
> }
[mysql@dbtest04 kerry]$ getBinlogStartTime /data/mysql/bin_logs/mysql_binlog.000055
2024-05-09 14:48:10
不過你看其腳本也會(huì)發(fā)現(xiàn)，解析MySQL的binlog的頭部文件，比較容易獲取binlog的開始時(shí)間，而獲取binlog的結(jié)束時(shí)間/輪轉(zhuǎn)時(shí)間就比較麻煩了。所以python腳本中獲取binlog結(jié)束時(shí)間的思路跟我的思路也是一樣的。
總結(jié)：
這里介紹了兩種快速獲取binlog的開始時(shí)間和結(jié)束時(shí)間的兩種方法，這兩種方法都非常高效，至于我寫的腳本find_binlog_start_end_time.sh目前還比較粗糙，后面有時(shí)間再完善補(bǔ)充。
參考資料
1: http://www.rzrgm.cn/kerrycode/p/17377899.html
[2]2: https://blog.csdn.net/qq_42768234/article/details/126970988
[3]3: https://blog.csdn.net/shaochenshuo/article/details/120549377
浙公網(wǎng)安備 33010602011771號