In this lesson, you will learn how to evaluate the differences between Statement-based and Row-based binary logging to ensure data consistency across your MySQL and MariaDB replicas.
The binary log (binlog) is the foundation of replication; it records every change made to your database. When configuring it, you must choose a format that dictates how these changes are captured and applied to your replicas.
Statement-Based Replication (SBR)
In SBR, the database logs the exact SQL statement executed (e.g., UPDATE users SET status = 'active' WHERE last_login < '2023-01-01').
- Pros: It is extremely compact, as it only records the query text.
- Cons: It can lead to consistency issues if the statement is non-deterministic. For example, if your query uses
UUID(),RAND(), or doesn't have anORDER BYclause during aLIMITupdate, the replica might end up with different data than the source.
Row-Based Replication (RBR)
In RBR, the database logs the actual changes made to individual rows. Instead of the SQL text, the binlog records the "before" and "after" images of the modified rows.
- Pros: It is completely deterministic. Since the exact data change is captured, there is no ambiguity for the replica, making it the safer choice for maintaining integrity.
- Cons: The log files can grow significantly larger, especially during bulk operations (e.g.,
DELETEorUPDATEaffecting millions of rows).
The "Mixed" Alternative
Most modern environments use MIXED mode. In this configuration, MySQL automatically uses SBR for statements that are safe and switches to RBR for non-deterministic operations. This provides a balance between the storage efficiency of SBR and the safety of RBR.
To view your current setting, you can use:
SHOW VARIABLES LIKE 'binlog_format';
If you prioritize absolute data integrity and have the storage capacity, ROW is the recommended standard. If you are dealing with massive batch operations and want to minimize log disk I/O, MIXED provides a flexible middle ground.
Key Takeaway: Choose Row-based logging when data integrity is paramount, as it eliminates the risks of non-deterministic statements that plague Statement-based logging.