MySQL Binlog Formats: Statement, Row, Mixed

1. What the binlog actually logs

The binary log, or binlog, is MySQL's central change log and the foundation of every form of replication as well as point-in-time recovery from backups. Every data change executed on an instance with log_bin enabled ends up in the binlog before it is considered complete. The binlog format determines the granularity at which these changes are recorded, and this decision has far-reaching consequences for replication safety, storage requirements, and debuggability.

MySQL offers three possible values for binlog_format: STATEMENT logs the executed SQL statement itself, ROW logs the actual changes to individual rows, and MIXED switches between both modes depending on the situation. The choice of binlog format was for years one of the most underestimated configuration decisions in MySQL operations, because STATEMENT was the default for a long time despite its known replication safety gaps.

2. STATEMENT format: SQL statements as the log

In STATEMENT-based binlog format, MySQL writes the actually executed SQL statement to the binlog, for example UPDATE products SET price = price * 1.1 WHERE category_id = 5. On the replica, the exact same statement is re-executed. The big advantage of this approach is compact size: a single SQL statement affecting a thousand rows produces just one short log entry, regardless of how many rows were actually changed.

This very compactness is also the weakness of STATEMENT format: it only works correctly if the statement produces exactly the same result on the replica as on the source. Functions like NOW(), RAND(), UUID(), or statements depending on execution order or internal state, such as LIMIT without ORDER BY in UPDATE statements, can yield different results on the source and the replica. STATEMENT binlog format relies on such non-determinism being either avoided or automatically translated by MySQL into a safe, deterministic form, which does not always succeed reliably.

-- Enable STATEMENT format explicitly (not recommended for production)
SET GLOBAL binlog_format = 'STATEMENT';

-- This statement replicates safely under STATEMENT format
UPDATE products SET price = price * 1.1 WHERE category_id = 5;

-- This statement is unsafe under STATEMENT format and only issues a warning
UPDATE products SET last_price_change = NOW() WHERE category_id = 5;
-- Warning (1592): Statement may not be safe to log in statement format.

-- mysqlbinlog on a STATEMENT-format log shows the original SQL directly
-- #260723 10:15:03 server id 1  end_log_pos 512
-- UPDATE products SET price = price * 1.1 WHERE category_id = 5

3. ROW format: row changes instead of SQL

In ROW-based binlog format, MySQL does not log the SQL statement but the actual before and after values of every affected row. An UPDATE changing a thousand rows produces a thousand individual row events, each with the exact column values before and after the change. This approach entirely eliminates the non-determinism problem: the replica applies exactly the same row changes, regardless of which function or execution order produced the result on the source.

The price for this safety is a significantly larger binlog volume for statements affecting many rows, because every row is logged individually instead of the compact SQL statement. For mass updates affecting millions of rows, ROW format can multiply binlog volume compared to STATEMENT. Nevertheless, ROW has been the recommended standard since MySQL 5.7 and is even the default value for binlog_format in MySQL 8.0, because the guarantee of correct replication almost always outweighs the extra storage cost in practice.

# my.cnf: enabling ROW-based binary logging with a full row image
[mysqld]
server-id = 1
log_bin = /var/log/mysql/binlog
binlog_format = ROW
binlog_row_image = FULL
gtid_mode = ON
enforce_gtid_consistency = ON

# Optional: compress binlog events to offset the larger ROW format size
binlog_transaction_compression = ON

4. MIXED format: automatic switching between both

MIXED binlog format attempts to combine the advantages of both worlds: by default MySQL uses STATEMENT-based logging for statements considered safely deterministic, and automatically switches to ROW-based logging as soon as the server detects a statement that could potentially exhibit non-deterministic behavior, for example a call to UUID() or a stored procedure with unsafe content.

In practice, MIXED turns out to be a compromise that rarely truly convinces: the size advantages of STATEMENT are lost for many everyday statements, because modern applications constantly use functions like NOW() for timestamps, which regularly forces MySQL to switch into ROW mode. At the same time, the complexity of having two different logging modes in the same binlog remains, complicating debugging and analysis with mysqlbinlog, because tools and administrators need to understand both formats at once.

5. Non-deterministic statements as the core problem

The real reason the binlog format becomes a critical configuration decision at all lies in non-deterministic SQL constructs. An UPDATE with LIMIT 100 without ORDER BY can affect a different subset of rows on every execution, because the internal storage order of rows can vary between source and replica, especially after different maintenance operations like OPTIMIZE TABLE. With STATEMENT format, such a statement would be re-executed on the replica and could hit different rows than on the source, a subtle and hard-to-find bug.

Triggers and stored procedures with internal logic depending on session variables or timing are also classic candidates for non-determinism. MySQL internally marks such constructs as UNSAFE and, in MIXED format, automatically writes a ROW event instead of the statement. With binlog format explicitly set to STATEMENT, the server only issues a warning but does not prevent the potentially incorrect replication, which makes STATEMENT an avoidable risk for production systems with complex application logic.

-- Example of a non-deterministic UPDATE that is unsafe under STATEMENT format
UPDATE products
SET featured = 1
LIMIT 100;  -- no ORDER BY: row order is not guaranteed to match between servers

-- MIXED format automatically falls back to ROW logging for this statement
-- STATEMENT format would replay it as-is and can hit a different set of rows

-- Check whether the server flagged a statement as unsafe
SHOW WARNINGS;
-- Level | Code | Message
-- Note  | 1592 | Statement may not be safe to log in statement format.

6. Binlog size: row vs. statement in practice

The size difference between ROW and STATEMENT binlog depends heavily on the workload pattern. For applications with many small, targeted single-row changes, for example the classic web application pattern of individual INSERT and UPDATE statements per request, the size difference between the formats is small, because only a few rows are affected per statement anyway. For batch jobs, mass imports, or large UPDATE statements changing thousands or millions of rows simultaneously, ROW format can dramatically increase binlog volume.

For such cases, MySQL offers binlog_row_image as a tuning knob: the default value FULL logs all columns before and after the change, MINIMAL logs only the actually changed columns plus the columns needed to uniquely identify the row. For tables with many columns where a typical UPDATE only changes a few, MINIMAL considerably reduces binlog volume without giving up the replication safety benefits of ROW-based logging. Additionally, since MySQL 8.0.20, binlog_transaction_compression compresses binlog events at the transaction level, further reducing storage requirements, especially for repetitive data patterns.

7. Configuring the format and tuning row image options

Changing the binlog format happens through server configuration and can be set both globally and per session, useful for targeted migrations or exceptional cases. A global switch typically requires no server restart, since binlog_format is a dynamic variable, but should be planned carefully, because already-running transactions must complete consistently in the previous format.

-- Check the current binlog format
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_row_image';

-- Change globally at runtime (new connections pick up the change)
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_row_image = 'MINIMAL';

-- Change for the current session only, useful for testing before a full rollout
SET SESSION binlog_format = 'ROW';

-- Persist the change in configuration for the next restart
-- [mysqld]
-- binlog_format = ROW
-- binlog_row_image = MINIMAL
-- binlog_row_metadata = FULL

When switching from STATEMENT or MIXED to ROW, it is important to switch all replicas simultaneously or with minimal delay, to avoid compatibility problems during the transition. In mixed environments where some replicas still run older MySQL versions, you should verify in advance that all versions fully support the desired binlog format and the associated row image options.

8. Analyzing binlogs with mysqlbinlog

The command-line tool mysqlbinlog decodes binlog files into readable or re-executable SQL form, regardless of the binlog format used. With STATEMENT format, the output directly shows the original SQL statements; with ROW format, the binary-encoded row changes are translated into a pseudo-SQL representation with the --verbose option, readable but not identical to the original statement.

# Decode a ROW-format binlog into human-readable pseudo-SQL
mysqlbinlog --verbose --base64-output=decode-rows /var/log/mysql/binlog.000042

# Extract only events for a specific database and time window
mysqlbinlog --database=shop \
  --start-datetime="2026-07-23 08:00:00" \
  --stop-datetime="2026-07-23 09:00:00" \
  /var/log/mysql/binlog.000042 | less

# Replay a binlog to recover data up to a specific point in time
mysqlbinlog --stop-position=154892 /var/log/mysql/binlog.000042 | mysql -u root -p

This analysis capability is especially valuable for point-in-time recovery after an accidental DELETE without a WHERE clause: the exact time or exact binlog position before the faulty statement can be extracted from the binlog, and recovery can proceed up to precisely that point. With ROW format, this recovery is more precise, because every individual row change is traceable, while with STATEMENT format only the original statement is visible, without information about which specific rows it actually affected.

9. Statement vs. row vs. mixed compared

Choosing the right binlog format depends on the priority between storage space, replication safety, and debugging comfort. The table below summarizes the key differences.

In practice, ROW binlog format is the right choice for the vast majority of production MySQL installations, because the guarantee of correct replication almost always justifies the extra storage cost, especially combined with binlog_row_image = MINIMAL and transaction compression to reduce size.

10. Summary

The binlog format is not a minor detail, it is a configuration decision with direct consequences for data integrity in replication. STATEMENT offers compact logs but carries real risk with non-deterministic statements. ROW entirely eliminates that risk, at the cost of more storage for mass operations. MIXED promises a compromise but rarely delivers the hoped-for balance and complicates debugging through two parallel formats in the same log.

For practically every production MySQL installation, ROW binlog format is the correct default choice, complemented by binlog_row_image = MINIMAL and transaction compression to control storage requirements. Anyone who also uses the binlog for point-in-time recovery benefits from ROW format's more precise traceability of individual row changes, an advantage that simply does not exist with STATEMENT format.

11. FAQ: MySQL Binlog Formats