Statement, Row, and Mixed Logging Semantics with Determinism Risks

Learning outcomes

Replication transports database changes through the binary log, but MySQL can encode those changes in different ways. Statement-based logging records the SQL statement; row-based logging records affected row images; mixed mode chooses between them. These are not interchangeable text formats—the choice affects determinism, log volume, security exposure, replica execution work, and which filtering rules apply.

Start with the current server, not an old tutorial

SELECT @@version AS server_version,       @@global.log_bin AS log_bin,       @@global.binlog_format AS global_binlog_format,       @@session.binlog_format AS session_binlog_format,       @@global.binlog_row_image AS binlog_row_image;SHOW BINARY LOG STATUS;

On MySQL 8.4, binary logging is enabled by default unless explicitly disabled, and the default binlog_format is ROW. More importantly, Oracle documents binlog_format as deprecated in 8.4 and states that support for non-row formats is subject to removal. That means a good course can explain STATEMENT and MIXED for existing estates while choosing ROW for new replication labs.

Why nondeterminism breaks statement replay

If the source executes UUID(), RAND(), or an update whose affected row is not deterministically chosen, replaying the same SQL later on another server can produce a different result. MySQL classifies many such statements as unsafe for statement-based logging. With MIXED logging, MySQL can switch unsafe statements to row events. With pure STATEMENT logging, it warns.

SET @saved_format = @@SESSION.binlog_format;SET SESSION binlog_format = 'STATEMENT';CREATE TABLE IF NOT EXISTS servicehub_repl_lab.format_probe (  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  token CHAR(36) NOT NULL) ENGINE=InnoDB;INSERT INTO servicehub_repl_lab.format_probe(token) VALUES (UUID());SHOW WARNINGS;SET SESSION binlog_format = @saved_format;

The useful evidence is the warning that the statement is unsafe for statement format. Do not memorize every function; the engineering question is whether the SQL depends on state or nondeterminism that a later replica execution might observe differently.

Row logging records effects, not a second SQL evaluation

SET SESSION binlog_format='ROW';INSERT INTO servicehub_repl_lab.work_orders(site_code,status,summary,priority)VALUES ('BAKU-03','OPEN','Inspect standby generator',2);UPDATE servicehub_repl_lab.work_ordersSET status='IN_PROGRESS'WHERE site_code='BAKU-03' AND summary='Inspect standby generator';SHOW BINARY LOG STATUS;

Record the current binary-log file, then use mysqlbinlog on the source container. The verbose decoder renders row-event information for human inspection.

# First discover the file name with SHOW BINARY LOG STATUS.# Example inside the source container:docker exec servicehub-mysql-source   mysqlbinlog --base64-output=DECODE-ROWS -vv   /var/lib/mysql/mysql-bin.000001 | less# PowerShell can omit '| less' or pipe to Select-String/Get-Content workflows.

mysqlbinlog output is diagnostic evidence, not a stable application API. Row images can also contain sensitive values, so binary-log access is a security boundary.

MIXED is a behavior, not “half of each transaction”

In MIXED mode, statement logging is normally used, but MySQL switches statements it considers unsafe to row-based logging. The decision is per statement/event class, not a promise that every transaction contains a predictable ratio of statement and row events. That makes MIXED relevant when maintaining older systems, but it is a poor excuse to avoid understanding the statements that create nondeterminism.

DDL is still statement-oriented

Row-based logging applies to DML effects. DDL such as CREATE TABLE and ALTER TABLE is logged as statements even when binlog_format=ROW. This matters for replication filtering, schema compatibility, and operational changes. A replica whose schema has drifted can fail while applying a perfectly valid source DDL even though ordinary row DML had been applying successfully.

Tempting tuning change that solves the wrong problem

Instead, begin with ROW for current MySQL 8.4, inspect binlog_row_image and actual log growth, optimize transaction shape and retention, and verify whether the workload has a genuine replication bottleneck. Do not trade correctness for a guessed storage saving.

Statement safety is about context, not only function names

MySQL's safe/unsafe classification exists because statement replay happens later, in another session, on another server. The source and replica can have different locks, row order, server variables, user identity, files, or external function results. A statement such as UPDATE ... LIMIT 1 without a deterministic ordering does not define which qualifying row must be changed; replaying the text can legally choose a different row. Functions such as UUID() are even clearer: evaluating them twice intentionally produces different values.

With pure STATEMENT logging, MySQL emits warnings for many unsafe statements, and operators who ignore warnings can accumulate subtle divergence. MIXED can change those unsafe operations to row events. ROW avoids re-evaluating the source's DML decision by describing the row changes that actually occurred. This is a correctness advantage, not simply a performance preference.

Not every nondeterministic-looking function is treated the same way; for example, MySQL has special replication semantics for several time functions. The practical rule is to rely on documented behavior and current warnings rather than creating a homemade list from memory.

SET @saved_format = @@SESSION.binlog_format;SET SESSION binlog_format='STATEMENT';UPDATE servicehub_repl_lab.work_ordersSET priority=priorityWHERE status='OPEN'LIMIT 1;SHOW WARNINGS;SET SESSION binlog_format=@saved_format;

Row images are operational data and a security boundary

Row-based binary logs can contain before/after column values needed to reproduce changes. That makes them powerful for replication and recovery, but it also means binary-log access can expose customer identifiers, operational notes, or other sensitive fields even when an operator never runs a normal SELECT against the table. Protect binary-log files, backups, log shipping paths, and mysqlbinlog output accordingly.

The binlog_row_image setting controls how much row information MySQL writes for row events under applicable conditions. Do not change it merely to reduce file size. The correct setting depends on replication, recovery, schema compatibility, and tooling assumptions. First measure log volume, know which consumers depend on the row images, and test restore/replication behavior in the same version family.

A decoded mysqlbinlog -vv stream is human diagnostic output. It is valuable for seeing transaction boundaries and affected values, but production automation should use supported interfaces and metadata rather than fragile parsing of presentation text.

Changing format is a topology change, not a session trick

The lesson changes the session format only on a disposable source to make behavior observable. In a real topology, format changes can be restricted while replication is active and can make source/replica configurations incompatible. MySQL documentation specifically warns about transitions where a source begins producing ROW/MIXED events while a replica is configured in ways that cannot accept them.

Before a planned change, inventory every channel, downstream replica, binlog consumer, CDC connector, backup/PITR workflow, and recovery tool. Stop or stage the change according to current documentation, verify a canary transaction end to end, and record the old setting for rollback. Because binlog_format itself is deprecated in MySQL 8.4, new architecture should reduce dependence on switching rather than create a new operational knob.

Performance claims also require workload evidence. Statement events may be smaller for some mass updates, while row events can avoid expensive statement re-execution and source-side locking patterns in other cases. Measure binary-log bytes, network throughput, applier rate, CPU/I/O, transaction latency, and recovery requirements together.

Production judgment

For new MySQL 8.4 replication, ROW is the conservative baseline and future-facing choice. Learn STATEMENT and MIXED so you can diagnose legacy estates and understand warnings, not because every topology should expose a tuning knob to application sessions. Version upgrades deserve special attention because MySQL's release direction can remove or further restrict deprecated modes.

Authoritative references

• MySQL 8.4 — Setting the Binary Log Format
• MySQL 8.4 — Binary Logging Options and Variables
• MySQL 8.4 — Advantages and Disadvantages of SBR/RBR
• MySQL 8.4 — Safe and Unsafe Statements
• MySQL 8.4 — mysqlbinlog