binlogformat

Binlogformat, often referred to by the system variable binlog_format, is a MySQL and MariaDB setting that determines how binary log events are written for replication and point-in-time recovery. The binary log records data-changing statements and events, and the chosen format affects what gets logged and how.

The three common formats are STATEMENT, ROW, and MIXED. STATEMENT logs the actual SQL statements that modify data. This keeps log size smaller and can be efficient, but it relies on the statements producing deterministic results on all replicas; non-deterministic functions, depending on server state, or certain statements can lead to replication discrepancies. ROW logs the exact data changes by recording the before and after state of each row affected. This is more robust for consistency across replicas and easier to reason about in complex environments, but it generates larger logs. MIXED switches between statement and row logging, using statements where safe and falling back to row logging for statements deemed unsafe to replicate deterministically.

Choosing a binlog format affects replication behavior, PITR reliability, and compatibility with GTID-based setups. Row-based formats generally provide stronger replica consistency and are preferred when statements may be non-deterministic or when using features like triggers or stored procedures. However, they can increase log volume and I/O overhead. Statement-based logging can be faster and lighter but requires careful attention to statement determinism and potential replication drift.

Configuration is done via the binlog_format variable. It can be set at runtime (for example, SET GLOBAL binlog_format = 'ROW') or persisted in the server configuration file (such as my.cnf) with binlog_format=ROW. Changing the format affects subsequent statements and requires consideration of the existing binary logs and replication topology to avoid inconsistencies.