Mysql point in time restore using event position from 100 binary log files

Question

i stuck on one question! the question is that i hava a large database in production environment and i have a full, incremental backup like till 2pm and aftre 1 hour i suddenly run the "drop database" command and now i want to restore that database before "drop database" statement from binary log files. And the difficulty is that how i know that from which binary log file i have to restore my data when there are so many binlog files like 100 files?

Answer 1

You dump each binlog file and grep it until you find the file and position you're looking for.

Example: I ran 'drop schema test2' on my local instance and then checked the last binary log file. I use grep -B3 which outputs the line that matches and the 3 lines before that line.

mysqlbinlog binlog.000069 | grep -B3 'drop schema test2'
# at 43985
#220801 10:12:41 server id 1 end_log_pos 44090 CRC32 0xaf5591a1 Query thread_id=107 exec_time=0 error_code=0 Xid = 1016
SET TIMESTAMP=1659373961/*!*/;
drop schema test2

If you have 100 binary log files, you need to search them one by one. This is a tedious chore, time-consuming and boring. But this is the way to do it.