Search code examples
mysqldatetimeloggingrecover

Recover MySQL data from queries/logs/binary/whatever

I am having a very big problem with mysql. I have a form in which the users fill a field that corresponds to a datetime attribute in the database. The problem I am having is that the form had a bad format for the datetime field, and all the records are filled with '0000-00-00 00:00:00' in the database.

Is there a way I can recover the data the user submitted? Maybe from a log/binary log?

I have checked the general log and it is empty.

Thanks in advance! I really will appreciate your help.

Solution

  • The mysqlbinlog utility can be used to convert the binary log binary format to text format.

    http://dev.mysql.com/doc/refman/5.5/en/mysqlbinlog.html

    Note that the binary logs may contain statements (those changes will be apparaent in the output from the mysqlbinlog urility. It may also contain binary changes, or they may be a mixture. What was recorded in the binary logs depends on the setting of the binlog_statement system variable. The default value depends on the version of MySQL.

    http://dev.mysql.com/doc/refman/5.5/en/binary-log-setting.html

    (This question might better be asked on dba.stackexchange.com.)

    FOLLOWUP

    The MySQL binary logs are not the InnoDB log files (ib_logfile0, ib_logfile1).

    The binary logs (on one of my installs) are written to the MySQL data directory:

    SHOW VARIABLES LIKE 'datadir'

    e.g.

    > ls -l /opt/mysql/data/mysql-bin*

-rw-rw---- 1 mysql mysql 1074553753 2014-05-28 22:39 /opt/mysql/data/mysql-bin.003907
-rw-rw---- 1 mysql mysql 1073745289 2014-05-29 01:22 /opt/mysql/data/mysql-bin.003908
-rw-rw---- 1 mysql mysql  280683262 2014-05-29 01:55 /opt/mysql/data/mysql-bin.003909
-rw-rw---- 1 mysql mysql  520382482 2014-05-29 05:55 /opt/mysql/data/mysql-bin.003910
-rw-rw---- 1 mysql mysql  705774048 2014-05-29 15:26 /opt/mysql/data/mysql-bin.003911
-rw-rw---- 1 mysql mysql        817 2014-05-29 05:55 /opt/mysql/data/mysql-bin.index

    Note that the binary logs are written only if binary logging is enabled. You can check whether binary logging is currently enable by querying the log_bin system variable:

    SHOW VARIABLES LIKE 'log_bin'

Variable_name  Value   
-------------  --------
log_bin        ON