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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply