Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 44.6Mb
PDF (A4) - 44.7Mb
PDF (RPM) - 40.4Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 205.6Kb
Man Pages (Zip) - 308.9Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Advantages and Disadvantages of Statement-Based and Row-Based Replication

17.2.1.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication

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.

Advantages of statement-based replication
  • Proven technology.

  • 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.

Disadvantages of statement-based replication
Advantages of row-based replication
  • All changes can be replicated. This is the safest form of replication.

    Note

    Statements that update the information in the mysql database—such as GRANT, REVOKE and the manipulation of triggers, stored routines (including stored procedures), and views—are all replicated to slaves using statement-based replication.

    For statements such as CREATE TABLE ... SELECT, a CREATE statement is generated from the table definition and replicated using statement-based format, while the row insertions are replicated using row-based format.

  • Fewer row locks are required on the master, which thus achieves higher concurrency, for the following types of statements:

  • Fewer row locks are required on the slave for any INSERT, UPDATE, or DELETE statement.

Disadvantages of row-based replication
  • RBR can generate more data that must be logged. To replicate a DML statement (such as an UPDATE or DELETE statement), 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 making and restoring a 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. Use binlog_row_image=minimal to reduce the disadvantage considerably.

  • 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 see on the slave what statements were received from the master and executed. However, you can see what data was changed using mysqlbinlog with the options --base64-output=DECODE-ROWS and --verbose.

    Alternatively, use the binlog_rows_query_log_events variable, which if enabled adds a Rows_query event with the statement to mysqlbinlog output when the -vv option is used.

  • For tables using the MyISAM 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 inserts on MyISAM tables are not supported when using row-based replication.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Ben Clewett on March 7, 2012
Memory tables have caused me a problem with ROW based replication.

As the table is not populated on server start-up, my slave may have an empty table, where the master is populated.

When I 'START SLAVE' I get:

Could not execute Update_rows event on table db.foo; Can't find record in 'foo', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql_bin_log.000022, end_log_pos 576268563

This is not the case with STATEMENT based replication, which will work.

In my case I solved this by using 'replicate_ignore_table' for any memory tables.

(Although a more elegant solution to keep this table functional would be preferable, if known?)

  Posted by Peter Burns on March 28, 2014
Another disadvantage of row-based replication:

If you have 2 or more servers replicating in a loop then some statements which are safe for SBR can cause RBR to fail, in particular statements which increment or decrement values. E.g.

UPDATE counter_table SET counter = counter + 1;

In SBR, 2 servers can both execute this query many times and ultimately stay in sync (even if replication halts for a while). But in RBR, if both servers execute this query at about the same time (or while replication is halted) they can both log the same change (e.g. from counter = 55 to counter = 56), and this change will fail when it replicates to the other server.

  Posted by Ben Lin on April 15, 2014
@Peter: Sounds like you are using both "servers" as masters, and replicate from each other. That is not a very good (common) practice. Usually there is only one master, and all the write operations are performed in this master. The other servers are slave for reading.
Sign Up Login You must be logged in to post a comment.