Major changes in the replication environment and in the behavior of applications can result from using row-based logging (RBL) or row-based replication (RBR) rather than statement-based logging or replication. This section describes a number of issues known to exist when using row-based logging or replication, and discusses some best practices for taking advantage of row-based logging and replication.
For additional information, see Section 16.1.2, “Replication Formats”, and Section 16.1.2.1, “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 17.6.3, “Known Issues in MySQL Cluster Replication”.
RBL, RBR, and temporary tables. As noted in Section 16.4.1.19, “Replication and Temporary Tables”, temporary tables are not replicated when using row-based format. When mixed format is in effect, “safe” statements involving temporary tables are logged using statement-based format. For more information, see Section 16.1.2.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.
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. Because RBL writes changes for each row to the binary log, its size can increase quite rapidly. In a replication environment, 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 BINLOG
statement (see Section 13.7.6.1, “BINLOG Syntax”). This statement
displays an event in printable form, but 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 in a manner that is easily human readable. This
is helpful when binary log events were written in
row-based format if 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”.
Binary log execution errors and
slave_exec_mode.
If 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
slave, so that the master and slave are no longer
synchronized. Latency issues and use of nontransactional
tables with RBR when
slave_exec_mode is
IDEMPOTENT can cause the master and
slave to diverge even further. For more information about
slave_exec_mode, see
Section 5.1.3, “Server System Variables”.
slave_exec_mode=IDEMPOTENT
is generally useful only for circular replication or
multi-master replication with MySQL Cluster, for which
IDEMPOTENT is the default value (see
Section 17.6, “MySQL Cluster Replication”).
For other scenarios, setting
slave_exec_mode to
STRICT is normally sufficient; this is
the default value for storage engines other than
NDB.
Lack of binary log checksums.
RBL uses no checksums. 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. The CHANGE MASTER
TO statement has options to enable replication
over SSL. See also Section 13.4.2.1, “CHANGE MASTER TO Syntax”, for
general 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
id_valueUPDATE 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, you must also use
--binlog_format=STATEMENT.
In MySQL 5.5, you can do filtering based on
server ID by using the IGNORE_SERVER_IDS
option for the CHANGE MASTER
TO statement. This option works with the
statement-based and row-based logging formats.
Database-level replication options.
The effects of the
--replicate-do-db,
--replicate-ignore-db, and
--replicate-rewrite-db
options differ considerably depending on whether row-based
or statement-based logging is used. Because of this, it is
recommended to avoid database-level options and instead
use table-level options such as
--replicate-do-table and
--replicate-ignore-table.
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.