mysql emits BINLOG rows even though binlog_­format=­STATEMENT

Question

We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default)

According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in our binlogs, as the first statement in each binlog.

Why is that?

Answer 1

Whenever mysql writes to binary logs, it has to encode all the operations.

There exists a base64 constant at the head of every binary log : 0xfe 0x62 0x69 0x6e. This is referred to the binlog magic number.

This value is registered in log_event.h in the mysql source code.

Other events are also encoded in log_event.h:

enum Log_event_type
{
  UNKNOWN_EVENT= 0, START_EVENT_V3= 1, QUERY_EVENT= 2, STOP_EVENT= 3,
  ROTATE_EVENT= 4, INTVAR_EVENT= 5, LOAD_EVENT= 6, SLAVE_EVENT= 7,
  CREATE_FILE_EVENT= 8, APPEND_BLOCK_EVENT= 9, EXEC_LOAD_EVENT= 10,
  DELETE_FILE_EVENT= 11, NEW_LOAD_EVENT= 12, RAND_EVENT= 13,
  USER_VAR_EVENT= 14, FORMAT_DESCRIPTION_EVENT= 15, XID_EVENT= 16,
  BEGIN_LOAD_QUERY_EVENT= 17, EXECUTE_LOAD_QUERY_EVENT= 18,
  TABLE_MAP_EVENT = 19, PRE_GA_WRITE_ROWS_EVENT = 20,
  PRE_GA_UPDATE_ROWS_EVENT = 21, PRE_GA_DELETE_ROWS_EVENT = 22,
  WRITE_ROWS_EVENT = 23, UPDATE_ROWS_EVENT = 24,
  DELETE_ROWS_EVENT = 25, INCIDENT_EVENT= 26,
  HEARTBEAT_LOG_EVENT= 27,
  ENUM_END_EVENT /* end marker */
};

Whenever mysqlbinlog dumps the contents of a binary logs, it has to see these event numbers, especially the binlog magic number. If this value is missing, that quickly reveals one of the following:

• the file is not a binary log
• the binary log is horrifically corrupt

If mysqlbinlog dumps the expression BINLOG, the file it is reading can be trusted to be a binary log because it encoutered the binlog magic number when parsing the file.

For more information on this: please see MySQL Internals on Binary Logs