In direct reference to the accepted answer in this question
MySQL doesn't log to the binary log file when you use direct database.table syntax in your update queries (eg. UPDATE Foo.Bar SET …).
Is the above true? It doesn't say this explicitly in the link the answerer provides. And is the only way to mitigate this is to ensure that your application doesn't prefix the schema name to the table refs in queries?
The reason I am asking is because we recently restored from a recent backup after an accidental drop database. I was also hoping to complete the data by restoring that day's activity from the binlogs. When I extracted the queries from the binlogs I couldn't find a single query before drop database appeared in the log. – I am also certain that I extracted the correct timestamps.
Best Answer
That is true in the context of
binlog-do-db(and equivalent) usingSTATEMENTbased replication. You should never usebinlog-do-dbunless you know what you are doing (or you will lose data because of replication filters). If you want to do replication filtering, you should usereplicate-wild-do-table(and otherwildoptions) on the replica. It is easy to filter out events, but impossible to put them back if they were never logged. ROW sometimes can also help saner filtering behaviour.If you are not using replication filtering, all write commands that are not server specific are sent to the master's binlog, if enabled correctly and not truncated/expired.
You need to provide more information to understand the issue you suffered (mysql version, topology used and options).