Understanding MySQL binlog_format: Choosing Between ROW, MIXED, and STATEMENT

Overview

When you set up MySQL replication on Amazon RDS or Aurora MySQL, you need to choose a binary logging format. The binlog_format parameter determines how data changes are captured in the binary log, which directly affects replication performance, data consistency, and storage consumption.

This article breaks down the three available formats, compares their trade-offs, and provides clear guidance on when to use each one.

Deprecation Notice: MySQL deprecated the binlog_format parameter in version 8.0.34. In MySQL 8.4, the default changed to ROW. A future major release will remove this parameter entirely and support only row-based replication. For new replication setups, AWS recommends row-based logging.

How Each Format Works

The binary log (binlog) records data changes on the source database. The replication process replays these changes on the target. The format you choose affects how those changes are captured.

ROW Format

Each individual row change is recorded separately in the binlog.

Example: An UPDATE that modifies 1 million rows produces 1 million entries in the binlog — one entry per affected row.

Pros:

• Strongest data consistency between source and replica
• No ambiguity — each row change is explicit

Cons:

• Largest binlog size, especially for bulk operations
• Requires primary keys on all tables; without them, MySQL performs full table scans during replay, which causes replica lag

MIXED Format

The engine selects the most efficient recording method automatically:

• Deterministic statements are recorded as statements (smaller, faster).
• Non-deterministic statements are recorded as row changes (safer).

Pros:

• Balances performance and consistency for most workloads
• Reduces binlog size compared to ROW for deterministic operations

Cons:

• Edge cases exist where the engine misclassifies a statement, which can cause data inconsistency.
• Temporary tables are replicated. If the source restarts, temp tables are destroyed locally but may persist on the replica.

STATEMENT Format

The literal SQL statement is recorded in the binlog.

Example: A single UPDATE query appears as-is in the binlog, regardless of how many rows it affects.

Pros:

• Smallest binlog size
• Fastest replication throughput

Cons:

• Non-deterministic functions (NOW(), UUID(), RAND()) can produce different results on the replica due to execution timing.
• Highest risk of data inconsistency between source and target.

Trade-off Comparison

The following table summarizes the key differences:

Criterion	STATEMENT	MIXED	ROW
Replication speed	Fastest	Medium	Slowest
Data consistency	Lowest	Medium	Highest
Binlog size	Smallest	Medium	Largest

In general, you trade replication speed and storage efficiency for data safety as you move from STATEMENT to ROW.

When to Use Each Format

Use ROW when:

• You are running MySQL 8.0.34 or later (aligns with the deprecation path)
• You are creating a blue/green deployment for engine upgrades or parameter changes
• You need strong replication consistency (the safest option)
• Your workload includes non-deterministic functions, UDFs, or stored procedures
• You are using AWS DMS (Database Migration Service requires ROW)
• You are configuring zero-ETL integrations with Amazon Redshift (requires ROW)
• You want to avoid any edge-case replication issues

Use MIXED when:

• You are on MySQL 8.0.33 or earlier and MIXED is not yet deprecated
• Your workload contains only deterministic SQL (no UDFs, no RAND(), no UUID())
• You need to reduce replication lag in a high-throughput OLTP environment
• You accept the small risk of edge-case engine misclassification

Note: AWS documentation for Aurora MySQL and RDS for MySQL states: "We recommend mixed unless you need a specific binlog format." However, for versions 8.0.34 and later, AWS recommends ROW for new setups due to the deprecation path.

Avoid STATEMENT unless:

• You have a specific legacy requirement
• You fully understand the data consistency risks
• None of your workloads use non-deterministic functions

Best Practices for ROW Format

When using ROW format, keep these points in mind:

1. Define primary keys on all tables. Without primary keys, MySQL performs full table scans during replay on replicas. This causes significant lag during bulk operations.

2. Monitor binlog size. ROW format generates larger binlogs. Set an appropriate binary log retention period and monitor disk usage.

3. Consider session-level overrides for bulk operations. In Aurora MySQL version 3, you can temporarily change binlog_format at the session level for archival or purging jobs that generate excessive row events.

Blue/Green Deployment Guidance

If you are using a blue/green deployment to upgrade your database engine or change parameter settings:

1. Set binlog_format = ROW in the DB cluster parameter group before creating the blue/green environment.
2. Reboot the writer instance to apply the change. In Aurora MySQL, binlog_format is a static parameter that requires a reboot.
3. Monitor replication lag on the green environment.
4. Perform the switchover as soon as lag reaches zero. A prompt switchover minimizes the window for any replication complications.

Summary

For most users today, ROW is the recommended choice. It provides the highest data consistency, aligns with the MySQL deprecation roadmap, and is required by several AWS features (DMS, zero-ETL, blue/green deployments).

MIXED remains a valid option on older MySQL versions (before 8.0.34) for workloads that are fully deterministic and where replication speed is a priority.

STATEMENT should generally be avoided due to its inherent consistency risks.

References

• Configuring RDS for MySQL binary logging
• Configuring Aurora MySQL binary logging
• Creating a blue/green deployment in Amazon Aurora
• Best practices for Amazon Aurora MySQL database configuration
• Overview and best practices of multithreaded replication
• Amazon RDS for MySQL LTS version 8.4 is now generally available
• MySQL 8.0 Reference Manual — Binary Logging Formats