目錄

• 1：通過(guò)xtrabackup_info文件獲取
• 2:通過(guò)mysqlbinlog解析獲取
• 方法3：stat命令查看創(chuàng)建時(shí)間

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

1：通過(guò)xtrabackup_info文件獲取

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

$?more?xtrabackup_info?uuid?=?3bd8a0f7-ea2f-11ed-9896-00505697b437name?=?tool_name?=?xtrabackuptool_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_25tool_version?=?8.0.31-24ibbackup_version?=?8.0.31-24server_version?=?8.0.31start_time?=?2023-05-04?11:53:26end_time?=?2023-05-04?11:53:30lock_time?=?0binlog_pos?=?filename?"mysql_binlog.000042",?position?"289",?GTID?of?the?last?change?"d01ecb4f-c944-11ed-9896-00505697b437:1-111:1000108-1953894,d01edb91-c944-11ed-9896-00505697b437:1-5,f8ef839e-c942-11ed-9bd2-00505697b437:1-13"innodb_from_lsn?=?0innodb_to_lsn?=?824007891partial?=?Nincremental?=?Nformat?=?filecompressed?=?Nencrypted?=?N

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

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

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

2:通過(guò)mysqlbinlog解析獲取

如下所示，我們想知道m(xù)ysql_binlog.000042什么時(shí)候開(kāi)始生成，什么時(shí)候開(kāi)始結(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:30BINLOG?"OixTZA8BAAAAegAAAH4AAAAAAAQAOC4wLjMxAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA

我們可以通過(guò)獲取關(guān)鍵字Start: binlog獲取這一行的信息，從而獲取MySQL二進(jìn)制日志（binlog）的開(kāi)始時(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，這里年份使用了縮寫(xiě)模式，即2023縮寫(xiě)為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?=?50948183COMMIT/*!*/;#?at?560#230504?12:14:03?server?id?1??end_log_pos?606???Rotate?to?mysql_binlog.000043??pos:?4SET?@@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?????????StopSET?@@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)，我們可以通過(guò)stat命令查看文件的創(chuàng)建時(shí)間，如下所示

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

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

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

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