Rollback with MySQL Transaction Log

Yesterday was a Grey Monday (almost turning into Black Monday)..

It happened that one of the MySQL DB was not backed-up and to make things more thrilling, one the table on the db was compromised.
Alhamdulillah, again, Zaman came to save the day.

Here what we did to rollback the transaction using the transaction log :

1. Read the transaction log in /var/db/myql/hostname-bin.xxxxxx
   This is binary format. In order to read the file, you have to use mysqlbinlog command :
   
   mysqlbinlog mysql-bin.000001
   
   This will show every single transaction happened on that particular server.
   
   
2. Then setup new MySQL server (I strongly advise you to setup a new database since we do not want to mess the existing one.)
   
   
3. Dump the compromised db structure only (without data) then restore it in the new server.
   
   
4. Then simply run the mysqlbinlog command and extend it to restore back to the server :
   
   mysqlbinlog mysql-bin.xxxxxx --database=yourdbname | mysql -uroot -p yourdbname
   
   If you want to exclude the last transaction which normally will be the culprit, you can use --stop-datetime option. You should know when is the last point/time (before the culprit transaction) of the transaction.
   
   
   
   By reading the transaction log, you will find the timestamp provided by MySQL. For example : 1263759884
   
   Since mysqlbinlog format needs human readable format, you have to convert the timestamp to human readable format. Here is one of the online date converter :
   
   http://www.epochconverter.com/
   
   Once converted, append it into the command :
   
   
   
   mysqlbinlog mysql-bin.000024 --database=yourdbname --stop-datetime="2010-01-17 22:11:16" | mysql -uroot -p yourdbname
   
   That's it.