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

Inhoudsopgave

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

Statements that are unsafe for SBR. Not all statements which modify data (such as INSERT DELETE, UPDATE, and REPLACE statements) can be replicated using statement-based replication. Any nondeterministic behavior is difficult to replicate when using statement-based replication. Examples of such Data Modification Language (DML) statements include the following:
- 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 17.4.1.16, “Replication of Invoked Features”, for more information.
- DELETE and UPDATE statements that use a LIMIT clause without an ORDER BY are nondeterministic. See Section 17.4.1.18, “Replication and LIMIT”.
- Locking read statements (SELECT ... FOR UPDATE and SELECT ... FOR SHARE) that use NOWAIT or SKIP LOCKED options. See Locking Read Concurrency with NOWAIT and SKIP LOCKED.
- Deterministic UDFs must be applied on the slaves.
- Statements using any of the following functions cannot be replicated properly using statement-based replication:
  - LOAD_FILE()
  - UUID(), UUID_SHORT()
  - USER()
  - FOUND_ROWS()
  - SYSDATE() (unless both the master and the slave are started with the --sysdate-is-now option)
  - GET_LOCK()
  - IS_FREE_LOCK()
  - IS_USED_LOCK()
  - MASTER_POS_WAIT()
  - RAND()
  - RELEASE_LOCK()
  - SLEEP()
  - VERSION()
  However, all other functions are replicated correctly using statement-based replication, including NOW() and so forth.
  For more information, see Section 17.4.1.14, “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 SHOW WARNINGS.
INSERT ... 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 InnoDB: An INSERT statement that uses AUTO_INCREMENT blocks other nonconflicting INSERT statements.
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 17.4.1.28, “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 17.4.1.9, “Replication with Differing Table Definitions on Master and Slave”, for more information.

Inhoudsopgave Haut

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 system schema—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:
- INSERT ... SELECT
- INSERT statements with AUTO_INCREMENT
- UPDATE or DELETE statements with WHERE clauses that do not use keys or do not change most of the examined rows.
Fewer row locks are required on the slave for any INSERT, UPDATE, or DELETE statement.

Inhoudsopgave Haut

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.

Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-replication-sbr-rbr.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut