Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 44.7Mb
PDF (A4) - 44.7Mb
PDF (RPM) - 40.5Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 205.8Kb
Man Pages (Zip) - 309.0Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Setting The Binary Log Format

5.4.4.2 Setting The Binary Log Format

You can select the binary logging format explicitly by starting the MySQL server with --binlog-format=type. The supported values for type are:

  • STATEMENT causes logging to be statement based.

  • ROW causes logging to be row based. This is the default.

  • MIXED causes logging to use mixed format.

The logging format also can be switched at runtime, although note that there are a number of situations in which you cannot do this, as discussed later in this section. Set the global value of the binlog_format system variable to specify the format for clients that connect subsequent to the change:

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

An individual client can control the logging format for its own statements by setting the session value of binlog_format:

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

Changing the global binlog_format value requires privileges sufficient to set global system variables. Changing the session binlog_format value requires privileges sufficient to set restricted session system variables. See Section 5.1.9.1, “System Variable Privileges”.

There are several reasons why a client might want to set binary logging on a per-session basis:

  • A session that makes many small changes to the database might want to use row-based logging.

  • A session that performs updates that match many rows in the WHERE clause might want to use statement-based logging because it will be more efficient to log a few statements than many rows.

  • Some statements require a lot of execution time on the master, but result in just a few rows being modified. It might therefore be beneficial to replicate them using row-based logging.

There are exceptions when you cannot switch the replication format at runtime:

  • The replication format cannot be changed from within a stored function or a trigger.

  • If the NDB storage engine is enabled.

  • If a session has open temporary tables, the replication format cannot be changed for the session (SET @@SESSION.binlog_format).

  • If any replication channel has open temporary tables, the replication format cannot be changed globally (SET @@GLOBAL.binlog_format or SET @@PERSIST.binlog_format).

  • If any replication channel applier thread is currently running, the replication format cannot be changed globally (SET @@GLOBAL.binlog_format or SET @@PERSIST.binlog_format).

Trying to switch the replication format in any of these cases (or attempting to set the current replication format) results in an error. You can, however, use PERSIST_ONLY (SET @@PERSIST_ONLY.binlog_format) to change the replication format at any time, because this action does not modify the runtime global system variable value, and takes effect only after a server restart.

Switching the replication format at runtime is not recommended when any temporary tables exist, because temporary tables are logged only when using statement-based replication, whereas with row-based replication and mixed replication, they are not logged.

Switching the replication format while replication is ongoing can also cause issues. Each MySQL Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. When using STATEMENT mode, the binlog_format system variable is not replicated. When using MIXED or ROW logging mode, it is replicated but is ignored by the slave.

A replication slave is not able to convert binary log entries received in ROW logging format to STATEMENT format for use in its own binary log. The slave must therefore use ROW or MIXED format if the master does. Changing the binary logging format on the master from STATEMENT to ROW or MIXED while replication is ongoing to a slave with STATEMENT format can cause replication to fail with errors such as Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.' Changing the binary logging format on the slave to STATEMENT format when the master is still using MIXED or ROW format also causes the same type of replication failure. To change the format safely, you must stop replication and ensure that the same change is made on both the master and the slave.

If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is possible to change the logging format to STATEMENT, but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts.

With the binary log format set to ROW, many changes are written to the binary log using the row-based format. Some changes, however, still use the statement-based format. Examples include all DDL (data definition language) statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE.

When row-based binary logging is used, the binlog_row_event_max_size system variable and its corresponding startup option --binlog-row-event-max-size set a soft limit on the maximum size of row events. The default value is 8192 bytes, and the value can only be changed at server startup. Where possible, rows stored in the binary log are grouped into events with a size not exceeding the value of this setting. If an event cannot be split, the maximum size can be exceeded.

The --binlog-row-event-max-size option is available for servers that are capable of row-based replication. Rows are stored into the binary log in chunks having a size in bytes not exceeding the value of this option. The value must be a multiple of 256. The default value is 8192.

Warning

When using statement-based logging for replication, it is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is nondeterministic; that is, it is left to the will of the query optimizer. In general, this is not a good practice even outside of replication. For a detailed explanation of this issue, see Section B.6.7, “Known Issues in MySQL”.

For information about logs kept by replication slaves, see Section 17.2.4, “Replication Relay and Status Logs”.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Ben Clewett on March 7, 2012
The 'Note' about replication and mixed-modes needs some explanation.

Using 5.0.56 I have completed various tests where by the slave was in STATEMENT and the master was in both ROW and STATEMENT at various times.

Here the slave always logs in STATEMENT, but the master's format is respected in the slave's logs, what ever that format is. Therefore the slave's binary log containes mixed statements.

A third MySQL slaving off the slave successfully reads the statements from the slave bin log, what ever their format.

I can't find any errors, warnings or failing replication...

I therefore believe this note is erroneous: from my own testing on this version....

  Posted by Mannoj Kumar on June 18, 2012
Hi Ben, try using non-deterministic data types on 3 types and you might see some differences for logging the data
Sign Up Login You must be logged in to post a comment.