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