Retrieve Mysql Lost Data

Right, well have you ever been in a position where you have lost information on websites through incorrect sql? Well, chances are that not everything is lost, especially with mysql.

Mysql records all sql statements run on mysql a compressed binary log. Everytime mysql restarts, a new log is created. In fedora, these logs are located in /var/lib/mysql/. For example, you will see files named mysql-bin.000001. However, to make use of them, first you must extract them into a readbale forma using the mysqlbinlog tool.

mysqlbinlog takes a binary log file and then does something with it. For example, to view the file, simply use the following example;

mysqlbinlog mysql-bin.000001

To write the log to a singel file, use the following command;

mysqlbinlog mysql-bin.000001 > all_sql_statements.sql

Then you can simply read the fil and take the statements you need from it.