MySQL数据库恢复到指定时间点时,我们必须通过MySQL全备+MySQL增量备份（可选）+MySQL的二进制日志（binlog）进行重放来恢复到指定时间点,实际的生产环境中,可能一段时间内生成了多个二进制日志文件（binlog）, MySQL本身不会存储二进制日志文件（binlog）的开始时间和结束时间,如果要还原到某个时间点,我们需要知道还原后重放哪些二进制日志文件.那么就必须获取二进制日志（binlog)的开始时间和结束时间.那么我们如何获取MySQL二进制日志文件（binlog）的开始时间和结束时间呢?下面简单总结几个方法,以供参考.
Xtrabckup还原全备或增量备份时,会生成一个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?=?⑧0.31-24
ibbackup_version?=?⑧0.31-24
server_version?=?⑧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
mysqlbinlog?--start-position="289"?./mysql_binlog.000042?>?restore.sql
这个方法有局限性,只能被动获取我们需要重放二进制日志的文件名和开始位置.这里仅供参考.
$?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?⑧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?⑧0.31?created?230504?11:53:30
BINLOG?'
OixTZA8BAAAAegAAAH4AAAAAAAQAOC4wLjMxAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
我们可以通过获取关键字Start: binlog获取这一行的信息,从而获取MySQL二进制日志（binlog）的开始时间,如下所示：
$?mysqlbinlog?mysql_binlog.000042?|grep?"Start:?binlog"?
#230504?11:53:30?server?id?1?end_log_pos?126?Start:?binlog?v?4,?server?v?⑧0.31?created?230504?11:53:30
$?mysqlbinlog?mysql_binlog.000042?|grep?"Start:?binlog"?|?awk?-F?"server?id"?'{print?$1}'
#230504?11:53:30
如果要获取二进制日志（binlog）的结束时间,这个还要看二进制日志是正常循环结束还是实例关闭结束,它们会对应不同的信息
STOP_EVENTA?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关闭后,对应的MySQL二进制日志的格式如下所示：
$?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}'
在Linux平台,我们可以通过stat命令查看文件的创建时间,如下所示
$?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:5④872910222?+0800
Modify:?2023-05-04?12:14:1⑧712310369?+0800
Change:?2023-05-04?12:14:1⑧712310369?+0800
?Birth:?2023-05-04?11:53:30.640989646?+0800
$?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:5⑧524978710?+0800
Modify:?2023-05-04?16:55:0⑦161545830?+0800
Change:?2023-05-04?16:55:0⑦161545830?+0800
?Birth:?2023-05-04?12:14:1⑧713310387?+0800
以上就是土嘎嘎小编为大家整理的MySQL如何获取binlog的开始时间和结束时间相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！