Mysql – troubleshooting missing entries using log files

logsMySQL

Something somewhere (most likely the app) is causing rows to disappear.
I'd like to find if there is a query that would point me in the right direction as to what's going on.

It looks like my bin log was not turned on so I've updated my.conf to include the log

log_bin                 = ON
general_log_file        = /tmp/mysql.log
general_log             = 1

but this is only going to update it from this point forward.

Is there any other way of being able to go back to see older queries?

Best Answer

A DELETE (and other writes) will be captured by both logs (assuming you don't have binlog-do/ignore setting in my.cnf). It would be more efficient to use the binlog, but it needs mysqlbinlog to read it. The general_log is plain text, but it gets huge fast, since it is capturing everything (including reads).

I think both logs have the query time and the login name. (I hope you are not doing everything as root.)

Neither can capture anything that happened when they were turned off. So turn one or both on now, and hope that the naughty query comes along soon.

Related Question