Download this Manual
EPUB - 0.8Mb


MySQL Internals Manual  /  ...  /  Binlog Formats

18.3.1 Binlog Formats

Status of this subsection: Complete but not reviewed 2009-10-21

The binlog is organized as a linear sequence of events. An SQL query that modifies the database will generate one or more events and append them to the binlog. There are also auxiliary event types that describe the structure of the binlog.

Queries can be logged in two ways:

  • In statement format: the SQL query is written to the binlog in text.

  • In row format: rows that changed are written to the binlog in a binary format. Each row may consist of a Before Image (BI) and/or an After Image (AI). The BI identifies the row to modify and the AI describes the row after the change. There are three types of log_events:

  • Write_rows_log_event: adds a new row to a table. Has only AI.

  • Update_rows_log_event: modifies an existing row in a table. Has both BI and AI.

  • Delete_rows_log_event: removes an existing row from a table. Has only BI.

Which of the two formats to use is configured with the @@session.binlog_format variable, which takes the values STATEMENT, ROW, or MIXED. The following principles shall hold:

(P-binlog_format-statement) @@session.binlog_format=STATEMENT:

  • We do not guarantee correct logging.

  • The client may not generate row events.

  • If the server cannot determine that a statement is correctly logged, a warning or error shall be issued:

    • If it is possible that the user (through application-specific logic) knows that the statement is correctly logged, then a warning shall be issued.

    • If it is inherently impossible for the user to determine that the statement will be correctly logged, an error shall be issued and the statement shall not execute.

(P-binlog_format-row) @@session.binlog_format=ROW:

  • We guarantee correct logging. If a statement cannot be correctly logged, then an error shall be generated and the statement shall not execute.

  • DML changes may only be logged in row format, not in statement format. If a DML change cannot be logged in row format, then an error shall be generated and the statement shall not execute.

(P-binlog_format-mixed) @@session.binlog_format=MIXED:

  • We guarantee correct logging. If a statement cannot be correctly logged, then an error shall be generated and the statement shall not execute.

  • If correct logging can be guaranteed by logging in statement format, then statement format shall be used. Otherwise, row format shall be used.

Clarification: If it cannot be determined in a practical manner that statement format leads to correct logging, then row format shall be used.


User Comments
Sign Up Login You must be logged in to post a comment.