MySQL如何快速獲取binlog的開始時間和結束時間

来源:https://www.cnblogs.com/kerrycode/p/18186152
-Advertisement-
Play Games

之前寫過一篇文章MySQL如何獲取binlog的開始時間和結束時間[1],文章裡面介紹瞭如何獲取MySQL資料庫二進位日誌（binlog）的開始時間與結束時間的一些方法。實際應用當中，我們可能還會遇到效率/性能方面的問題。最近對這個問題做了一些研究，這裡就介紹一下如何快速獲取MySQL二進位日誌(b ...


之前寫過一篇文章MySQL如何獲取binlog的開始時間和結束時間[1],文章裡面介紹瞭如何獲取MySQL資料庫二進位日誌（binlog）的開始時間與結束時間的一些方法。實際應用當中，我們可能還會遇到效率/性能方面的問題。最近對這個問題做了一些研究，這裡就介紹一下如何快速獲取MySQL二進位日誌(binlog)的開始時間和結束時間。

我們下來看看當MySQL二進位日誌（binlog）的Size很大的時候，獲取起開始時間和結束時間，如下測試所示

$ 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

從上面實驗可以看出，在MySQL二進位日誌（binlog）變大的情況下，這種方法需要34秒，非常低效和耗時，那麼我們怎麼提升性能呢？ 我們改寫一下腳本，如下所示

$ 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

如上所示，這樣改進腳本後，性能效率已經提升到0.01秒，已經相當的高效了。那麼獲取結束時間能否也可以這樣提升呢？ 很遺憾的是由於MySQL二進位日誌（binlog）的結束時間/滾動時間(Rotate Time)位於文件的末尾，由於管道的一些基本特性，獲取MySQL二進位日誌(binlog)的結束時間無法通過上面方法來優化，這裡不打算介紹Linux管道相關概念，所以我們只需知道這麼一個事實。

如果你對Linux管道的一些原理不是很清楚，那麼就用實驗測試驗證一下，如下所示：

$ 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

那麼怎麼來優化獲取MySQL二進位日誌（binlog）的結束時間呢？經過一番觀察與實驗，我發現一個MySQL二進位日誌（binlog）的結束時間，就是下一個二進位日誌（binlog）的開始時間。如下實驗所示

[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]$

如果全部符合這個規律的話，那麼我們直接用下一個binlog的開始時間作為上一個binlog的結束時間即可，於是我寫了一個腳本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                 :    [email protected]                                       #
#***************************************************************************************#
# 參數配置                                                                              #
#---------------------------------------------------------------------------------------#
# 腳本參數   binlog文件存放的路徑                                                       #
#---------------------------------------------------------------------------------------#
# MYSQLBINLOG        mysqlbinlog的位置，以防沒有設置環境變數                            #
# BINLOG_BASENAME    binlog的首碼名                                                     #
#---------------------------------------------------------------------------------------#
# 註意事項：                                                                            #
#   1：如果維護的MySQL資料庫都規範化安裝、配置的化，下麵很多參數都不需要修改            #
#***************************************************************************************#
# Version        Modified Date            Description                                   #
#***************************************************************************************#
# V.1.0          2024-05-10            創建此腳本                                       #
#########################################################################################

# mysqlbinlog的路徑，一般無需設置，以防沒有設置環境變數時
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:

另外，還有一種比較高效的方法是解析二進位日誌的頭部信息（此篇文章統統指binlog v 4），因為binlog的頭部由固定的4個位元組組成，而頭部信息的FORMAT_DESCRIPTION_EVENT部分包含了binlog的開始時間，我在搜索/學習相關資料時，結果發現有人已經總結過這方面的內容，而且已經有相關Python腳本或shell腳本了，這裡就重覆造輪子了，Python腳本來自MySQL 查詢binlog生成時間[2]我們來看看實驗結果，如下所示

$ 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開始時間的方法[3]:

[mysql@dbtest04 kerry]$ function getBinlogStartTime()
> {
>         theFile="$1"
>         #取出文件頭做分析
>         binlogHead=`hexdump ${theFile} | head -1`
>         #binlog文件校驗
>         binlogCrc=`echo $binlogHead | awk '{print $1$2$3}'`
>         if [ "${binlogCrc}" != '000000062fe6e69' ]; then
>                 echo '${theFile} is damage.'
>                 exit 1
>         fi
>         #計算binlog的開始時間
>         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

不過你看其腳本也會發現，解析MySQL的binlog的頭部文件，比較容易獲取binlog的開始時間，而獲取binlog的結束時間/輪轉時間就比較麻煩了。所以python腳本中獲取binlog結束時間的思路跟我的思路也是一樣的。

總結：

這裡介紹了兩種快速獲取binlog的開始時間和結束時間的兩種方法，這兩種方法都非常高效，至於我寫的腳本find_binlog_start_end_time.sh目前還比較粗糙，後面有時間再完善補充。

參考資料

[1]

1: https://www.cnblogs.com/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
掃描上面二維碼關註我 如果你真心覺得文章寫得不錯，而且對你有所幫助，那就不妨幫忙“推薦"一下，您的“推薦”和”打賞“將是我最大的寫作動力！ 本文版權歸作者所有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接.
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • sql學習：終於把sql case語句使用講明白了，一看就懂
    作者 | 幸福it民工 來源 | 今日頭條 在資料庫查詢分析中，經常遇到case條件判斷，總結出來供大家參考，下麵通過兩個實例給大家講解。 case用途介紹: CASE是多條件分支語句，相比IF…ELSE語句，CASE語句進行分支流程式控制制可以使代碼更加晰，易於理解。CASE語句也根據表達式邏輯值的真 ...
  • 詳解Redis持久化（持久化高危漏洞利用與多種對抗方案、RDB、AOF、同步手動持久化、非同步手動非阻塞持久化、備份檢測、備份修複、壓縮原理、雙備份模式、純緩存模式）
    謹防持久化+未授權訪問漏洞入侵伺服器 CVE編號找不到，CNVD有一個：CNVD-2015-07557（國家信息安全漏洞共用平臺漏洞編號）。 這是我之前寫過的文章，漏洞成因、影響範圍、POC與對抗方案有詳解： 謹防利用Redis未授權訪問漏洞入侵伺服器 RDB（Redis Database、全量保存 ...
  • MySQL Join原理分析（緩衝塊嵌套與索引嵌套迴圈）
    場景假設 A表（1000條數據）left join B表（1000條數據）。 嵌套迴圈（Nested-Loop Join） 極簡概括：顧名思義多層迴圈疊加，由於MySQL條數數量有限，所用for迴圈而不用while，在MySQL中就是多層for迴圈。 性能問題：MySQL使用這種作為join方式最簡 ...
  • MySQL 8.0 Reference Manual（讀書筆記93節--Semisynchronous Replication）
    面試遇到過的Redis問題 Redis的數據類型 怎麼利用Redis解決超賣問題 緩存雪崩、緩存擊穿、緩存穿透 1. 什麼是Redis Redis就是一個非關係型資料庫（NoSQL），我們知道關係型資料庫MySQL，在MySQL里我們對於數據的增刪改查需要SQL語句（select * from... ...
  • 【YashanDB知識庫】ycm托管資料庫時，資料庫非OM安裝無法托管
    問題現象 ps -ef | grep yas 查看無yasom和yasagent進程，且在{資料庫安裝目錄}/om/{資料庫名稱}的目錄下沒有conf、data、log等目錄，確定資料庫不是用yasboot安裝，是用腳本安裝的 問題的風險及影響 非yasboot安裝，ycm無法完成托管，無法監控 問 ...
  • SQL 優化
    1. 插入數據 1.1 insert 如果我們需要一次性往資料庫表中插入多條記錄，可以從以下三個方面進行優化。 insert into tb_test values(1,'tom'); insert into tb_test values(2,'cat'); insert into tb_test ...
  • Agile PLM資料庫表結構(Oracle)
    剛進公司，任務是接管PLM系統，但是還在給外包團隊開發，沒有代碼。無妨先看業務和資料庫，ok，業務看不懂，只能先看資料庫，資料庫沒有數據字典，這個系統沒有任何文檔產出......練手時發現資料庫類型是Oracle，面對百度不成問題，數據字典只能看前端然後去資料庫裡面一個個找著對應自己整理了，純折磨 ...
  • 構建無伺服器數倉（二）Apache DolphinScheduler 集成以及 LOB 粒度資源消費分析
    引言 在數據驅動的世界中，企業正在尋求可靠且高性能的解決方案來管理其不斷增長的數據需求。本系列博客從一個重視數據安全和合規性的 B2C 金融科技客戶的角度來討論雲上雲下混合部署的情況下如何利用亞馬遜雲科技雲原生服務、開源社區產品以及第三方工具構建無伺服器數據倉庫的解耦方法。 Apache Dolph ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類