You can select the binary logging format explicitly by starting
the MySQL server with
The supported values for
STATEMENT causes logging to be statement
ROW causes logging to be row based.
MIXED causes logging to use mixed format.
The default binary logging format depends on the version of MySQL you are using:
For MySQL 5.1.11 and earlier, and for MySQL 5.1.29 and later, statement-based logging is used by default.
For MySQL 5.1.12 through MySQL 5.1.28, mixed logging is used by default.
For all MySQL Cluster releases using version 6.1 or later of
NDBCLUSTER storage engine
(even those releases based on MySQL 5.1.29 and later), the
default binary log format is
Section 17.6, “MySQL Cluster Replication”.
The logging format also can be switched at runtime. To specify
the format globally for all clients, set the global value of the
binlog_format system variable:
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
SET SESSION binlog_format = 'STATEMENT';mysql>
SET SESSION binlog_format = 'ROW';mysql>
SET SESSION binlog_format = 'MIXED';
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
ROW logging mode, it is replicated but is
ignored by the slave.) Changing the binary logging format on
the master while replication is ongoing, or without also
changing it on the slave can thus cause unexpected results, or
even cause replication to fail altogether.
In addition to switching the logging format manually, a slave
server may switch the format automatically.
This happens when the server is running in either
and encounters an event in the binary log that is written in
ROW logging format. In that case, the slave
switches to row-based replication temporarily for that event,
and switches back to the previous format afterward.
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:
From within a stored function or a trigger
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.
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.
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
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 1024.
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.5.8, “Known Issues in MySQL”.