MySQL binlog missing queries

Posted on

Question :

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.

Answer :

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;

Decides whether to replicate based on Zap, not Foo. And it looks a binlog_do/ignore_db to decide.

Leave a Reply

Your email address will not be published. Required fields are marked *