How to restore truncated MySQL table from binary logs?

Question

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

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