MySQL獲取binlog的開始時(shí)間和結(jié)束時(shí)間(最新方法)

更新時(shí)間：2023年05月08日 09:19:20 作者：瀟湘隱者

這篇文章主要介紹了MySQL如何獲取binlog的開始時(shí)間和結(jié)束時(shí)間,本文通過實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

MySQL數(shù)據(jù)庫恢復(fù)到指定時(shí)間點(diǎn)時(shí)，我們必須通過MySQL全備+MySQL增量備份（可選）+MySQL的二進(jìn)制日志（binlog）進(jìn)行重放來恢復(fù)到指定時(shí)間點(diǎn)，實(shí)際的生產(chǎn)環(huán)境中，可能一段時(shí)間內(nèi)生成了多個(gè)二進(jìn)制日志文件（binlog）, MySQL本身不會(huì)存儲(chǔ)二進(jìn)制日志文件（binlog）的開始時(shí)間和結(jié)束時(shí)間，如果要還原到某個(gè)時(shí)間點(diǎn)，我們需要知道還原后重放哪些二進(jìn)制日志文件。那么就必須獲取二進(jìn)制日志（binlog)的開始時(shí)間和結(jié)束時(shí)間。那么我們?nèi)绾潍@取MySQL二進(jìn)制日志文件（binlog）的開始時(shí)間和結(jié)束時(shí)間呢？下面簡(jiǎn)單總結(jié)幾個(gè)方法,以供參考。

1：通過xtrabackup_info文件獲取

Xtrabckup還原全備或增量備份時(shí)，會(huì)生成一個(gè)xtrabackup_info文件，如下所示：

$?more?xtrabackup_info?
uuid?=?3bd8a0f7-ea2f-11ed-9896-00505697b437
name?=?
tool_name?=?xtrabackup
tool_command?=?--defaults-file=/data/conf/my.cnf?--login-path=****?--backup?--target-dir=/db_backup/mysql_backup/db_backup/backup_cycle_2023_04_30/full_backup_2023_05_04_11_53_25
tool_version?=?8.0.31-24
ibbackup_version?=?8.0.31-24
server_version?=?8.0.31
start_time?=?2023-05-04?11:53:26
end_time?=?2023-05-04?11:53:30
lock_time?=?0
binlog_pos?=?filename?'mysql_binlog.000042',?position?'289',?GTID?of?the?last?change?'d01ecb4f-c944-11ed-9896-00505697b437:1-111:100
0108-1953894,d01edb91-c944-11ed-9896-00505697b437:1-5,f8ef839e-c942-11ed-9bd2-00505697b437:1-13'
innodb_from_lsn?=?0
innodb_to_lsn?=?824007891
partial?=?N
incremental?=?N
format?=?file
compressed?=?N
encrypted?=?N

如上所示，你可以看到一行關(guān)于binlog_pos的信息：binlog文件名為mysql_binlog.000042，起始位置為289，我們應(yīng)用MySQL二進(jìn)制日志（binlog）可以從這個(gè)二進(jìn)制日志開始：

mysqlbinlog?--start-position="289"?./mysql_binlog.000042??>?restore.sql

這個(gè)方法有局限性，只能被動(dòng)獲取我們需要重放二進(jìn)制日志的文件名和開始位置。這里僅供參考。

2:通過mysqlbinlog解析獲取

如下所示，我們想知道m(xù)ysql_binlog.000042什么時(shí)候開始生成，什么時(shí)候開始結(jié)束的，其實(shí)MySQL二進(jìn)制日志里面包含有這樣的信息，如下所示：

$?mysqlbinlog?mysql_binlog.000042??|?head?-10
#?The?proper?term?is?pseudo_replica_mode,?but?we?use?this?compatibility?alias
#?to?make?the?statement?usable?on?server?versions?8.0.24?and?older.
/*!50530?SET?@@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003?SET?@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER?/*!*/;
#?at?4
#230504?11:53:30?server?id?1??end_log_pos?126???Start:?binlog?v?4,?server?v?8.0.31?created?230504?11:53:30
BINLOG?'
OixTZA8BAAAAegAAAH4AAAAAAAQAOC4wLjMxAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA

我們可以通過獲取關(guān)鍵字Start: binlog獲取這一行的信息，從而獲取MySQL二進(jìn)制日志（binlog）的開始時(shí)間，如下所示：

$?mysqlbinlog??mysql_binlog.000042?|grep?"Start:?binlog"?
#230504?11:53:30?server?id?1??end_log_pos?126???Start:?binlog?v?4,?server?v?8.0.31?created?230504?11:53:30
$?mysqlbinlog??mysql_binlog.000042?|grep?"Start:?binlog"?|?awk?-F?"server?id"?'{print?$1}'
#230504?11:53:30

如上所示，生成mysql_binlog.000042的時(shí)間點(diǎn)為230504 11:53:30，這里年份使用了縮寫模式，即2023縮寫為23，230504代表的是2023-05-04.

如果要獲取二進(jìn)制日志（binlog）的結(jié)束時(shí)間，這個(gè)還要看二進(jìn)制日志是正常循環(huán)結(jié)束還是實(shí)例關(guān)閉結(jié)束，它們會(huì)對(duì)應(yīng)不同的信息

STOP_EVENT
A STOP_EVENT has not payload or post-header
ROTATE_EVENT
The rotate event is added to the binlog as last event to tell the reader what binlog to request next.

如下所示：

$?mysqlbinlog?mysql_binlog.000042??|?tail?-10
#?at?533
#230504?12:13:35?server?id?3??end_log_pos?560???Xid?=?50948183
COMMIT/*!*/;
#?at?560
#230504?12:14:03?server?id?1??end_log_pos?606???Rotate?to?mysql_binlog.000043??pos:?4
SET?@@SESSION.GTID_NEXT=?'AUTOMATIC'?/*?added?by?mysqlbinlog?*/?/*!*/;
DELIMITER?;
#?End?of?log?file
/*!50003?SET?COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530?SET?@@SESSION.PSEUDO_SLAVE_MODE=0*/;

$?mysqlbinlog??mysql_binlog.000042??|grep?Rotate
#230504?12:14:03?server?id?1??end_log_pos?606???Rotate?to?mysql_binlog.000043??pos:?4
$?mysqlbinlog??mysql_binlog.000042??|grep?Rotate?|?awk?-F?"server?id"?'{print?$1}'
#230504?12:14:03

如果MySQL關(guān)閉后，對(duì)應(yīng)的MySQL二進(jìn)制日志的格式如下所示：

$?mysqlbinlog?mysql_binlog.000045?|?tail?-10
#?at?126
#230504?17:02:23?server?id?10??end_log_pos?157?CRC32?0x2d378ba5?????????Previous-GTIDs
#?[empty]
#?at?157
#230504?17:08:11?server?id?10??end_log_pos?180?CRC32?0xb6b08f2c?????????Stop
SET?@@SESSION.GTID_NEXT=?'AUTOMATIC'?/*?added?by?mysqlbinlog?*/?/*!*/;
DELIMITER?;
#?End?of?log?file
/*!50003?SET?COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530?SET?@@SESSION.PSEUDO_SLAVE_MODE=0*/;

$??mysqlbinlog??mysql_binlog.000045?|?grep?Stop?|?awk?-F?"server?id"?'{print?$1}'

方法3：stat命令查看創(chuàng)建時(shí)間

在Linux平臺(tái)，我們可以通過stat命令查看文件的創(chuàng)建時(shí)間，如下所示

$?stat?mysql_binlog.000042
??File:?mysql_binlog.000042
??Size:?606?????????????Blocks:?8??????????IO?Block:?4096???regular?file
Device:?fd08h/64776d????Inode:?100663444???Links:?1
Access:?(0640/-rw-r-----)??Uid:?(??801/???mysql)???Gid:?(??800/???mysql)
Access:?2023-05-04?13:36:54.872910222?+0800
Modify:?2023-05-04?12:14:18.712310369?+0800
Change:?2023-05-04?12:14:18.712310369?+0800
?Birth:?2023-05-04?11:53:30.640989646?+0800

如上所示，我們可以通過Birth字段信息，知道m(xù)ysql_binlog.000042是2023-05-04 11:53:30創(chuàng)建的。但是我們沒法獲取二進(jìn)制日志的結(jié)束時(shí)間，不過由于MySQL的二進(jìn)制日志文件是有數(shù)字序列的，所以我們可以通過下一個(gè)二進(jìn)制日志文件（binlog）的開始時(shí)間來判斷上一個(gè)二進(jìn)制日志文件（binlog）的結(jié)束時(shí)間。

$?stat?mysql_binlog.000043
??File:?mysql_binlog.000043
??Size:?335?????????????Blocks:?8??????????IO?Block:?4096???regular?file
Device:?fd08h/64776d????Inode:?100663430???Links:?1
Access:?(0640/-rw-r-----)??Uid:?(??801/???mysql)???Gid:?(??800/???mysql)
Access:?2023-05-04?13:36:58.524978710?+0800
Modify:?2023-05-04?16:55:07.161545830?+0800
Change:?2023-05-04?16:55:07.161545830?+0800
?Birth:?2023-05-04?12:14:18.713310387?+0800

到此這篇關(guān)于MySQL如何獲取binlog的開始時(shí)間和結(jié)束時(shí)間的文章就介紹到這了,更多相關(guān)mysql binlog開始時(shí)間和結(jié)束時(shí)間內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: