MariaDB Binary Log Formats: ROW vs STATEMENT vs MIXED (2026)

MariaDB’s binary log formats dramatically change how changes are recorded, not just that they are recorded, and the default ROW format is often a performance trap.

Let’s see it in action. Imagine we have a simple table:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products (name, price) VALUES ('Gadget', 19.99);

Now, let’s consider how different binary log formats handle an update.

The Core Problem: Replaying Changes

The binary log (binlog) is MariaDB’s transaction log. It records all data modifications. This is crucial for:

• Replication: Slaves read the binlog from the master and apply the same changes.
• Point-in-Time Recovery: You can restore a backup and then apply binlog events up to a specific moment.
• Auditing: For compliance or debugging, you can reconstruct who changed what and when.

The format of these events is what ROW, STATEMENT, and MIXED control.

Statement-Based Replication (SBR)

With STATEMENT format, MariaDB writes the actual SQL statement that modified the data.

If binlog_format=STATEMENT, and we run:

UPDATE products SET price = price * 1.10 WHERE name = 'Gadget';

The binary log might contain an event like this:

#180725 10:30:00 server id 1  end_log_pos 123 CRC32 0x12345678  Query   thread_id=42  exec_time=0     error_code=0
SET TIMESTAMP=1532509800/1.0;
UPDATE products SET price = price * 1.10 WHERE name = 'Gadget';

Pros:

• Smaller Binlog Size: Generally, SQL statements are more compact than recording every row change, especially for updates affecting many rows.
• Easier to Read: For humans, it’s straightforward to see the SQL commands that were executed.

Cons:

• Non-Deterministic Statements: This is the killer. If a statement’s outcome depends on factors not explicitly in the statement itself, replication can diverge. Examples:
  • NOW(), UUID(), RAND(): These functions return different values each time.
  • Statements without an explicit ORDER BY clause that affect multiple rows: The order in which rows are processed might differ between master and slave, leading to different results if the statement relies on that order.
  • LIMIT without ORDER BY: Similar to the above.
• Function-Based Triggers: If a trigger executes a non-deterministic statement.

Row-Based Replication (RBR)

With ROW format, MariaDB writes the before and after images of the rows that were modified. It doesn’t record the SQL statement.

If binlog_format=ROW, and we run the same update:

UPDATE products SET price = price * 1.10 WHERE name = 'Gadget';

The binary log will contain events detailing the specific row(s) changed. It will look something like this (simplified):

#180725 10:30:00 server id 1  end_log_pos 123 CRC32 0x12345678  Table_map: `test`.`products` mapped to number 123
#180725 10:30:00 server id 1  end_log_pos 150 CRC32 0x12345678  Update_rows: table id 123 flags: STMT_END_F
### UPDATE `test`.`products`
### WHERE
###   @1=1
###   @2='Gadget'
###   @3=19.99
### BEFORE ROW
###   @1=1
###   @2='Gadget'
###   @3=19.99
### AFTER ROW
###   @1=1
###   @2='Gadget'
###   @3=21.99

Pros:

• Deterministic: This is the primary advantage. It guarantees that the exact same data changes are applied on the slave, regardless of non-deterministic functions or statement ordering.
• Handles Complex Statements Safely: Stored procedures, triggers, and complex SQL are replicated accurately.

Cons:

• Larger Binlog Size: For statements that affect many rows (e.g., UPDATE products SET price = price * 1.10; without a WHERE clause), ROW format can generate a massive amount of data, as each modified row is logged individually. This impacts disk space and network I/O for replication.
• Less Human-Readable: It’s harder to quickly scan the binlog and understand what SQL commands were run; you have to interpret the row changes.

Mixed-Based Replication (MBR)

MIXED format is a hybrid. It uses STATEMENT for statements that are safe and deterministic, and switches to ROW for statements that are not.

MariaDB tries to detect non-deterministic statements. For example, a statement using UUID() would automatically be logged in ROW format, while a simple INSERT or UPDATE without tricky functions would be logged as STATEMENT.

Pros:

• Best of Both Worlds (Theoretically): Aims to provide the space efficiency of STATEMENT while maintaining the safety of ROW for critical operations.

Cons:

• Complexity and Edge Cases: The detection logic isn’t perfect. Some complex statements might be misclassified, leading to unexpected behavior.
• Still Can Be Large: If many non-deterministic statements are executed, the binlog can still grow significantly.
• Harder to Predict: You don’t always know why a particular statement was logged as ROW vs STATEMENT without deep inspection.

The Surprising Performance Trap: ROW and Bulk Updates

While ROW is safer, the default ROW format can be a severe performance bottleneck for write-heavy workloads, especially when many rows are updated by a single statement. The overhead of writing each row’s before/after image to the binlog, and then the subsequent I/O on the replica to apply those individual row changes, can dwarf the actual SQL execution time. For a statement like UPDATE orders SET status = 'shipped' WHERE order_date < '2023-01-01';, if this affects thousands of rows, the binlog output for ROW format can be enormous.

The most surprising thing is that for simple, deterministic updates that affect many rows, STATEMENT format can actually be faster and produce a smaller binlog, provided you are absolutely certain it’s safe for your replication topology.

Changing the Format

You can change the binary log format dynamically or statically:

Dynamically (for the current session/server runtime):

SET GLOBAL binlog_format = 'ROW'; -- or 'STATEMENT' or 'MIXED'

This change takes effect immediately for new transactions.

Statically (persistent across restarts):

Edit your MariaDB configuration file (e.g., /etc/my.cnf, /etc/mysql/mariadb.conf.d/50-server.cnf) and add or modify the following line under the [mariadb] or [mysqld] section:

[mariadb]
binlog_format = ROW

Then, restart the MariaDB server.

The Next Step: Binlog Events and mysqlbinlog

Once you’ve chosen a format and are looking at your binary logs, the next step is learning to read them using the mysqlbinlog utility to diagnose replication issues or perform point-in-time recovery.