MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
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 describes some best practices using it in replication.
For additional information, see Section 16.2.1, “Replication Formats”, and Section 16.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.
For information about issues specific to NDB Cluster Replication (which depends on row-based replication), see Section 21.7.3, “Known Issues in NDB Cluster Replication”.
Row-based logging of temporary tables. As noted in Section 16.4.1.29, “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 16.2.1.1, “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.
You can switch from statement-based to row-based binary
logging format at runtime even when temporary tables have
been created. From MySQL 5.7.25, the MySQL server tracks the
logging mode that was in effect when each temporary table
was created. When a given client session ends, the server
logs a DROP TEMPORARY TABLE IF EXISTS
statement for each temporary table that still exists and was
created when statement-based binary logging was in use. If
row-based or mixed format binary logging was in use when the
table was created, the DROP TEMPORARY TABLE IF
EXISTS
statement is not logged. In previous
releases, the DROP TEMPORARY TABLE IF
EXISTS
statement was logged regardless of the
logging mode that was in effect.
Nontransactional DML statements involving temporary tables
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 replica, a table lock is taken on all tables involved, and then the rows are applied in batch mode. Depending on the engine used for the replica's copy of the table, this may or may not be effective.
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 replica that match those on the source. 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 BINLOG
statement (see Section 13.7.6.1, “BINLOG Statement”). This statement
displays an event as a base 64-encoded string, the meaning
of which is not evident. When invoked with the
--base64-output=DECODE-ROWS
and --verbose
options,
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 4.6.7.2, “mysqlbinlog Row Event Display”.
Binary log execution errors and replica execution mode.
Using
slave_exec_mode=IDEMPOTENT
is generally only useful with MySQL NDB Cluster
replication, for which IDEMPOTENT
is
the default value. (See
Section 21.7.10, “NDB Cluster Replication: Bidirectional and Circular Replication”).
When slave_exec_mode
is
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
replica, so that the source and replica are no longer
synchronized. Latency issues and use of nontransactional
tables with RBR when
slave_exec_mode
is
IDEMPOTENT
can cause the source and
replica to diverge even further. For more information
about slave_exec_mode
,
see Section 5.1.7, “Server System Variables”.
For other scenarios, setting
slave_exec_mode
to
STRICT
is normally sufficient; this is
the default value for storage engines other than
NDB
.
Filtering based on server ID not supported.
You can filter based on server ID by using the
IGNORE_SERVER_IDS
option for the
CHANGE MASTER TO
statement.
This option works with statement-based and row-based
logging formats. Another method to filter out changes on
some replicas is to use a WHERE
clause
that includes the relation @@server_id <>
clause with
id_value
UPDATE
and
DELETE
statements. For
example, 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.
RBL, nontransactional tables, and stopped replicas.
When using row-based logging, if the replica server is
stopped while a replication thread is updating a
nontransactional table, the replica 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
STOP SLAVE
SQL_THREAD
prior to shutting down the replica
server helps prevent issues from occurring, and is always
recommended regardless of the logging format or storage
engine you use.