0

I have a mysql server and while working on it I accidentlly truncated the wrong table. I do have a mysql dump wich I immediately used to restore my database structure, but I still miss some entries in only one table.

SHOW BINARY LOGS shows me 64 logs.

How do I know wich log to restore and what is the procedure of restoring with mysqlbinlog? Using the last log is not changing anything ...

EDIT: I already checked http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html but didn't accomplish anything

Would it be enough to just manually search all logs for the needed insert statements?

Thank you Soda

1

You can restore the database from binary logs only if they were enables from the very beginning and if there are all of them starting from .00001.

Then to restore the database you need to replay all binlogs on a fresh MySQL instance:

for log in mysql-bin.*
do
    mysqlbinlog $log | mysql -h <destination host>
done

If you truncated a table you probably need to restore just it. Then restore the database on some other host and move the table to the original server.

| improve this answer | |

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.