1

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?

1 Answer 1

0

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.

1
  • thank you @Bill Karwin i get my answer.
    – Nomi Malik
    Aug 2 at 6:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.