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.
That is true in the context of
binlog-do-db (and equivalent) using
STATEMENT based replication. You should never use
binlog-do-db unless you know what you are doing (or you will lose data because of replication filters). If you want to do replication filtering, you should use
replicate-wild-do-table (and other
wild options) 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).
USE Zap; UPDATE Foo.Bar SET...
Decides whether to replicate based on
Foo. And it looks a
binlog_do/ignore_db to decide.