Restoring from backup and binary

Question

There are times when a table / database is dropped unintentionally.

I have to check the date-time of the start position from the binary when the backup was taken. I do also have to check the date-time of the position where the "drop" statement is found. I do run the mysqlbinlog statement with those parameters.

I can not use start-position and stop-position parameters because the binaries are spread across different files. Is there any better way to handle such human mistakes?

Answer 1

every time you take a backup, you should be using FLUSH TABLES WITH READ LOCK to force all of the tables in to a consistent state, followed by FLUSH LOGS to close the current binary log. then, when you apply the backup, all you have to do is replay one binary log.