You can select the binary logging format explicitly by starting
the MySQL server with
The supported values for
STATEMENTcauses logging to be statement based.
ROWcauses logging to be row based.
MIXEDcauses logging to use mixed format.
Setting the binary logging format does not activate binary
logging for the server. The setting only takes effect when
binary logging is enabled on the server, which is the case when
log_bin system variable is
ON. In MySQL 5.7, binary logging is
not enabled by default, and you enable it using the
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
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
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
requires privileges sufficient to set restricted session system
variables. See Section 126.96.36.199, “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
WHEREclause might want to use statement-based logging because it is more efficient to log a few statements than many rows.
Some statements require a lot of execution time on the source, 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:
From within a stored function or a trigger.
NDBstorage engine is enabled.
If the session is currently in row-based replication mode and has open temporary tables.
Trying to switch the format in any of these cases results in an error.
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 they are not logged. With mixed
replication, temporary tables are usually logged; exceptions
happen with user-defined functions (UDFs) and with the
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 source server does not cause a replica
to change its logging format to match. When using
STATEMENT mode, the
binlog_format system variable
is not replicated. When using
ROW logging mode, it is replicated but is
ignored by the replica.
A replica is not able to convert binary log entries received in
ROW logging format to
STATEMENT format for use in its own binary
log. The replica must therefore use
MIXED format if the source does. Changing the
binary logging format on the source from
MIXED while replication is ongoing to a
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 replica to
format when the source is still using
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
source and the replica.
If you are using
InnoDB tables and
the transaction isolation level is
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
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
ALTER TABLE, or
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.
When using statement-based logging for replication, it is possible for the data on the source and replica 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.3.7, “Known Issues in MySQL”.