MySQL uses statement-based logging (SBL), row-based logging (RBL) or mixed-format logging. The type of binary log used impacts the size and efficiency of logging.Therefore the choice between row-based replication (RBR) or statement-based replication (SBR) depends on your application and environment. This section describes known issues when using a row-based format log, and discusses some best practices using it in replication.
For additional information, see Section 17.1.2, “Replication Formats”, and Section 126.96.36.199, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.
For information about issues specific to MySQL Cluster Replication (which depends on row-based replication), see Section 18.6.3, “Known Issues in MySQL Cluster Replication”.
Row-based logging of temporary tables. As noted in Section 188.8.131.52, “Replication and Temporary Tables”, temporary tables are not replicated when using row-based format. When using mixed format logging, “safe” statements involving temporary tables are logged using statement-based format. For more information, see Section 184.108.40.206, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.
Temporary tables are not replicated when using row-based format because there is no need. In addition, because temporary tables can be read only from the thread which created them, there is seldom if ever any benefit obtained from replicating them, even when using statement-based format.
In MySQL 5.6, you can switch from
statement-based to row-based binary logging mode even when
temporary tables have been created. However, while using the
row-based format, the MySQL server cannot determine the
logging mode that was in effect when a given temporary table
was created. For this reason, the server in such cases logs
TABLE IF EXISTS statement for each temporary table
that still exists for a given client session when that
session ends. While this means that it is possible that an
DROP TEMPORARY TABLE
statement might be logged in some cases, the statement is
harmless, and does not cause an error even if the table does
not exist, due to the presence of the
In MySQL 5.6.6 and earlier, the
option caused any nontransactional DML statement involving
temporary tables to fail with an error when using row-based
logging, in spite of the fact that they are not written to
the binary log. In MySQL 5.6.7 and later, such statements
are allowed when using
binlog_format=ROW, as long
as any nontransactional tables affected by the statements
are temporary tables (Bug #14272672).
RBL and synchronization of nontransactional tables. When many rows are affected, the set of changes is split into several events; when the statement commits, all of these events are written to the binary log. When executing on the slave, a table lock is taken on all tables involved, and then the rows are applied in batch mode. (This may or may not be effective, depending on the engine used for the slave's copy of the table.)
Latency and binary log size. RBL writes changes for each row to the binary log and so its size can increase quite rapidly. This can significantly increase the time required to make changes on the slave that match those on the master. You should be aware of the potential for this delay in your applications.
Reading the binary log.
mysqlbinlog displays row-based events
in the binary log using the
statement (see Section 220.127.116.11, “BINLOG Syntax”). This statement
displays an event as a base 64-encoded string, the meaning
of which is not evident. When invoked with the
mysqlbinlog formats the contents of the
binary log to be human readable. When binary log events
were written in row-based format and you want to read or
recover from a replication or database failure you can use
this command to read contents of the binary log. For more
information, see Section 18.104.22.168, “mysqlbinlog Row Event Display”.
Binary log execution errors and
IDEMPOTENT, a failure to apply changes
from RBL because the original row cannot be found does not
trigger an error or cause replication to fail. This means
that it is possible that updates are not applied on the
slave, so that the master and slave are no longer
synchronized. Latency issues and use of nontransactional
tables with RBR when
IDEMPOTENT can cause the master and
slave to diverge even further. For more information about
Section 5.1.4, “Server System Variables”.
is generally useful only for circular replication or
multi-master replication with MySQL Cluster, for which
IDEMPOTENT is the default value.
For other scenarios, setting
STRICT is normally sufficient; this is
the default value.
Formerly, the default value when using MySQL Cluster was
slave_exec_mode=IDEMPOTENT, but this is
no longer the case in MySQL Cluster NDB 7.3.
Lack of binary log checksums.
RBL does not use checksums, so network, disk, and other
errors may not be identified when processing the binary
log. To ensure that data is transmitted without network
corruption use SSL for replication connections. The
CHANGE MASTER TO statement
has options to enable replication over SSL. See also
Section 22.214.171.124, “CHANGE MASTER TO Syntax”, for general
information about setting up MySQL with SSL.
Filtering based on server ID not supported.
In MySQL 5.6, you can filter based on server
ID by using the
option for the
TO statement. This option works with
statement-based and row-based logging formats. Another
method to filter out changes on some slaves is to use a
WHERE clause that includes the relation
DELETE statements. For
WHERE @@server_id <> 1.
However, this does not work correctly with row-based
logging. To use the
server_id system variable
for statement filtering, use statement-based logging.
Database-level replication options.
The effects of the
options differ considerably depending on whether row-based
or statement-based logging is used. Therefore, it is
recommended to avoid database-level options and instead
use table-level options such as
For more information about these options and the impact
replication format has on how they operate, see
Section 17.1.4, “Replication and Binary Logging Options and Variables”.
RBL, nontransactional tables, and stopped slaves.
When using row-based logging, if the slave server is
stopped while a slave thread is updating a
nontransactional table, the slave database can reach an
inconsistent state. For this reason, it is recommended
that you use a transactional storage engine such as
InnoDB for all tables
replicated using the row-based format. Use of
STOP SLAVE or
SQL_THREAD prior to shutting down the slave
MySQL server helps prevent issues from occurring, and is
always recommended regardless of the logging format or
storage engine you use.