6.3. Row-Based Replication
Replication capabilities in MySQL originally were based on
propagation of SQL statements from master to slave. This is called
statement-based replication (SBR). As of
MySQL 5.1.5, another basis for replication is available. This is
called row-based replication (RBR). Instead
of sending SQL statements to the slave, the master writes events
to its binary log that indicate how individual table rows are
effected.
For a comparison that shows the advantages and disadvantages of
SBR and RBR, see Section 6.12, “Comparison of Statement-Based Versus Row-Based Replication”.
With MySQL's classic statement-based replication, there may be
issues with replicating stored routines or triggers. You can avoid
these issues by using MySQL's row-based replication (RBR) instead.
For a detailed list of issues, see
Section 19.4, “Binary Logging of Stored Routines and Triggers”.
If you build MySQL from source, row-based replication is
unavailable unless you invoke configure with
the --with-row-based-replication option.
MySQL Server use statement-based replication by default, even if
it has been configured with support for row-based replication. To
cause row-based replication to be used, start the server with the
--binlog-format=row option to enable row-based
replication globally (for all client connections). The option also
automatically turns on
innodb_locks_unsafe_for_binlog, which is safe
when row-based replication is used.
Statement-based replication can be chosen at server startup either
by specifying --binlog-format=statement or by not
using the --binlog-format option at all.
Row-based replication causes most changes to
be written to the binary log using the row-based format. Some
changes, however, must be written to the binary log as statements:
ANALYZE TABLE
REPAIR TABLE
OPTIMIZE TABLE
The --binlog-row-event-max-size is available for
servers that are capabile 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.
