Statement vs Row-Based Binary Logging in MySQL

MySQL offers two primary formats for binary logging: Statement-Based Replication (SBR) and Row-Based Replication (RBR). This article examines the key operational differences between these two logging formats, comparing how they record data changes, their impact on database performance, network and storage overhead, and replication reliability to help you determine the best configuration for your environment.

1. How Data Changes are Recorded

The fundamental difference between the two formats lies in what gets written to the binary log:

• Statement-Based Logging (SBR): The source database writes the literal SQL statements (e.g., INSERT, UPDATE, DELETE) to the binary log. The replica then executes these exact SQL statements to apply the changes.
• Row-Based Logging (RBR): The source database writes the actual changes made to individual table rows. The binary log contains the “before” and “after” state of the modified rows, which the replica applies directly without re-running the original SQL query.

2. Replication Safety and Determinism

A major operational difference is how both formats handle non-deterministic queries:

• SBR and Non-Deterministic Queries: SBR can cause data inconsistency between the source and replica when queries use non-deterministic functions like NOW(), UUID(), RAND(), or LIMIT clauses without an ORDER BY. Because these functions can return different results when executed at different times, the replica’s data may drift from the source.
• RBR and Data Integrity: RBR is completely safe from non-deterministic issues. Since the source executes the query and logs only the final resulting row changes, the replica receives the exact data payload, guaranteeing absolute consistency between the source and replicas.

3. Disk Space and Network Bandwidth

The volume of data generated by the binary logs differs drastically depending on the workload:

• SBR Efficiency: SBR is highly efficient in terms of storage and network usage. A statement like UPDATE users SET status = 'active' might modify millions of rows, but in SBR, only the single-line SQL statement is written to the log and sent over the network.
• RBR Overhead: RBR can generate massive binary logs. Under RBR, the same UPDATE statement modifying millions of rows will write millions of individual row change events to the log. This can lead to heavy disk I/O, rapid disk space consumption, and network replication lag.

4. CPU and Lock Contention

System resource utilization on both the source and replica scales differently under each format:

• SBR Execution on Replicas: Replicas must parse, compile, and execute every SQL statement received. This requires significant CPU processing. Furthermore, executing complex queries on the replica can result in table or row locks, potentially blocking read queries on the replica.
• RBR Execution on Replicas: Replicas do not need to parse or optimize SQL statements; they simply apply the raw row modifications directly. This results in much lower CPU utilization on the replica. It also requires less restrictive locking, reducing lock contention on read-heavy replicas.

5. DDL and Metadata Changes

Data Definition Language (DDL) statements, such as ALTER TABLE, CREATE TABLE, or DROP TABLE, are always logged as statements, regardless of the binary logging format. Even when using RBR, MySQL automatically falls back to statement-based logging for DDL operations, as row-by-row logging does not apply to schema structures.

Summary of Key Differences