Each binary logging format has advantages and disadvantages. For most users, the mixed replication format should provide the best combination of data integrity and performance. If, however, you want to take advantage of the features specific to the statement-based or row-based replication format when performing certain tasks, you can use the information in this section, which provides a summary of their relative advantages and disadvantages, to determine which is best for your needs.
Proven technology that has existed in MySQL since 3.23.
Less data written to log files. When updates or deletes affect many rows, this results in much less storage space required for log files. This also means that taking and restoring from backups can be accomplished more quickly.
Log files contain all statements that made any changes, so they can be used to audit the database.
Statements that are unsafe for SBR.
Not all statements which modify data (such as
REPLACE statements) can be
replicated using statement-based replication. Any
nondeterministic behavior is difficult to replicate when
using statement-based replication. Examples of such DML
(Data Modification Language) statements include the
A statement that depends on a UDF or stored program that is nondeterministic, since the value returned by such a UDF or stored program or depends on factors other than the parameters supplied to it. (Row-based replication, however, simply replicates the value returned by the UDF or stored program, so its effect on table rows and data is the same on both the master and slave.) See Section 220.127.116.11, “Replication of Invoked Features”, for more information.
Statements using any of the following functions cannot be replicated properly using statement-based replication:
However, all other functions are replicated correctly
using statement-based replication, including
NOW() and so forth.
For more information, see Section 18.104.22.168, “Replication and System Functions”.
Statements that cannot be replicated correctly using statement-based replication are logged with a warning like the one shown here:
[Warning] Statement is not safe to log in statement format.
A similar warning is also issued to the client in such
cases. The client can display it using
SELECT requires a greater number of row-level
locks than with row-based replication.
UPDATE statements that
require a table scan (because no index is used in the
WHERE clause) must lock a greater number
of rows than with row-based replication.
For complex statements, the statement must be evaluated and executed on the slave before the rows are updated or inserted. With row-based replication, the slave only has to modify the affected rows, not execute the full statement.
If there is an error in evaluation on the slave, particularly when executing complex statements, statement-based replication may slowly increase the margin of error across the affected rows over time. See Section 22.214.171.124, “Slave Errors During Replication”.
Stored functions execute with the same
NOW() value as the calling
statement. However, this is not true of stored procedures.
Deterministic UDFs must be applied on the slaves.
Table definitions must be (nearly) identical on master and slave. See Section 126.96.36.199, “Replication with Differing Table Definitions on Master and Slave”, for more information.
All changes can be replicated. This is the safest form of replication.
For MySQL versions earlier than 5.1.14, DDL (Data Definition
Language) statements such as
TABLE are replicated using statement-based
replication, while DML statements, as well as
REVOKE statements, are
replicated using row-based replication.
In MySQL 5.1.14 and later, the
database is not replicated. The
database is instead seen as a node-specific database.
Row-based replication is not supported on tables in this
database. Instead, statements that would normally update
this information—such as
REVOKE and the manipulation
of triggers, stored routines (including stored procedures),
and views—are all replicated to slaves using
For statements such as
... SELECT, a
is generated from the table definition and replicated using
statement-based format, while the row insertions are
replicated using row-based format.
The technology is the same as in most other database management systems; knowledge about other systems transfers to MySQL.
Fewer row locks are required on the master, which thus achieves higher concurrency, for the following types of statements:
RBR tends to generate more data that must be logged. To
replicate a DML statement (such as an
statement-based replication writes only the statement to the
binary log. By contrast, row-based replication writes each
changed row to the binary log. If the statement changes many
rows, row-based replication may write significantly more
data to the binary log; this is true even for statements
that are rolled back. This also means that taking and
restoring from backup can require more time. In addition,
the binary log is locked for a longer time to write the
data, which may cause concurrency problems.
Deterministic UDFs that generate large
BLOB values take longer to
replicate with row-based replication than with
statement-based replication. This is because the
BLOB column value is logged,
rather than the statement generating the data.
You cannot examine the logs to see what statements were executed, nor can you see on the slave what statements were received from the master and executed.
For tables using the
storage engine, a stronger lock is required on the slave for
INSERT statements when
applying them as row-based events to the binary log than
when applying them as statements. This means that concurrent
MyISAM tables are
not supported when using row-based replication.
Formerly, when performing a bulk operation that includes nontransactional storage engines, changes were applied as the statement executed. With row-based logging, this meant that the binary log was written while the statement was running. On the master, this does not cause problems with concurrency, because tables are locked until the bulk operation terminates. On the slave server, tables were not locked while the slave applied changes, because the slave did not know that those changes were part of a bulk operation.
In such cases, if you retrieved data from a table on the
master (for example, using
SELECT * FROM
table_name), the server waited for the bulk
operation to complete before executing the
SELECT statement, because the
table was read-locked. On the slave, the server did not wait
(because there was no lock). This meant that, until the bulk
operation on the slave completed, different results were
obtained for the same
query on the master and on the slave.
This issue was resolved in MySQL 5.1.24. (Bug #29020)