Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.