Understanding MySQL Binlog: Statement, Row, and Mixed Modes Explained

Statement Mode

Statement mode is based on SQL statements (statement‑based replication) and records only the modifying SQL statements such as INSERT, UPDATE, DELETE.

For example, an UPDATE T SET update_time=NOW() WHERE id=1 will be logged as the exact SQL text.

Statement format does not record transaction boundaries, making it suitable for non‑transactional operations.

Advantages: Binlog files are usually smaller because only SQL statements are stored.

Disadvantages: Accuracy is limited; functions like NOW() or UUID() may not replicate correctly.

Row Mode

Row mode records the actual data changes for each row, including before‑and‑after values for every column.

When an UPDATE affects 100 rows, the binlog will contain 100 separate row change entries.

Row mode is ideal for transactional operations because it ensures precise replay of each row change on the replica.

However, it can produce larger binlog files and increase I/O and network load.

Advantages: High accuracy; exact data changes are replicated.

Disadvantages: Larger log files, higher network and disk I/O.

Mixed Mode

Mixed mode combines Statement and Row modes, automatically choosing the appropriate format based on the SQL statement.

Simple statements are logged in Statement format, while complex changes (e.g., involving generated values or triggers) use Row format to maintain consistency.

Advantages: Balances accuracy with moderate file size.

Disadvantages: Potential for master‑slave inconsistencies.