Using row-based logging or replication, rather than statement-based logging or replication, can result in major changes in the replication environment and in the behavior of applications. This section describes a number of issues known to exist when using row-based logging or row-based replication, and discusses some best practices for taking advantage of row-based logging (RBL) and row-based replication (RBR).
For additional information, see Section 16.1.2.1, “Comparison of Statement-Based and Row-Based Replication”, and Section 16.1.2, “Replication Formats”.
For information about issues specific to MySQL Cluster Replication (which depends on row-based replication), see Known Issues in MySQL Cluster Replication.
RBL, RBR, and temporary tables. As noted elsewhere in this chapter (see Section 16.3.1.23, “Replication and Temporary Tables”), temporary tables are not replicated when using the row-based format. However, you can use the mixed format; when mixed format is in effect, “safe” statements involving temporary tables are logged using the statement-based format. For more information, see Section 16.1.2.1, “Comparison of Statement-Based and Row-Based Replication”.
There is actually no need to replicate temporary tables when using RBR. In addition, since 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 mode.
RBL and the BLACKHOLE
storage engine.
Prior to MySQL 5.1.29,
DELETE
and
UPDATE
statements did not
work with RBL and BLACKHOLE
tables.
(Bug#38360)
RBL and synchronization of nontransactional tables.
When using row-based replication of a
MyISAM
or other nontransactional table,
changed rows are written to the transaction cache. Often,
when many rows are affected, the set of changes are 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, 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. Because RBL writes changes for each row to the binary log, the size of the binary log can grow quite rapidly. When used in a replication environment, this can significantly increase the time required for making the 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.
With the
--base64-output=DECODE-ROWS
and --verbose
options,
mysqlbinlog is able to format the
contents of the binary log in a manner that is easily
human-readable, in case you want to read or recover from a
replication or database failure using the contents of the
binary log. For more information, see
Section 4.6.7.2, “mysqlbinlog Row Event Display”. Before MySQL
5.1.28, this was not possible (Bug#31455).
Binary log execution errors and
slave_exec_mode
.
If you use slave_exec_mode=IDEMPOTENT
,
a failure to apply changes from RBL because the original
row cannot be found does not trigger an error, and does
not 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 when
using slave_exec_mode=IDEMPOTENT
and
RBR can cause the master and slave to diverge even
further. For more information about
slave_exec_mode
, see
Section 5.1.4, “Server System Variables”.
slave_exec_mode=IDEMPOTENT
is generally
useful only for circular replication or multi-master
replication with MySQL Cluster (see
MySQL Cluster Replication). For other
scenarios, the default value
(slave_exec_mode=STRICT
) is normally
sufficient.
Lack of binary log checksums. No checksums are used for RBL. This means that network, disk, and other errors may not be identified when processing the binary log. To ensure that data is transmitted without network corruption, you may want to consider using SSL, which adds another layer of checksumming, for replication connections. See Section 5.5.7, “Using SSL for Secure Connections”, for more information about setting up MySQL with SSL.
Filtering based on server ID not supported.
A common practice is to filter out changes on some slaves
by using a WHERE
clause that includes
the relation @server_id <>
clause with
server-id
UPDATE
and DELETE
statements, a simple example of such a clause being
WHERE @server_id <> 1
. However,
this does not work correctly with row-based logging. If
you must use the
server_id
system variable
for statement filtering, then you must also use
--binlog_format=STATEMENT
.
Database-level replication options.
The effects of the options
--replicate-do-db
,
--replicate-ignore-db
, and
--replicate-rewrite-db
differ considerably depending on whether row-based or
statement-based logging is in use. Because of this, it is
recommended to avoid the database-level options and use
the table-level options such as
--replicate-do-table
and
--replicate-ignore-table
instead. For more information about these options and the
impact that your choice of replication format has on how
they operate, see Section 16.1.3, “Replication and Binary Logging Options and Variables”.
User Comments
Add your own comment.