Supported Binary Log Formats

There are three supported formats for binary log events:

• Statement-Based Logging
• Row-Based Logging
• Mixed Logging

Regardless of the format, binary log events are always stored in a binary format, rather than in plain text. MariaDB includes the mysqlbinlog utility that can be used to output binary log events in a human-readable format.

You may want to set the binary log format in the following cases:

• If you execute single statements that update many rows, then statement-based logging will be more efficient than row-based logging for the slave to download.
• If you execute many statements that don't affect any rows, then row-based logging will be more efficient than statement-based logging for the slave to download.
• If you execute statements that take a long time to complete, but they ultimately only insert, update, or delete a few rows in the table, then row-based logging will be more efficient than statement-based logging for the slave to apply.

The storage engine API also allows storage engines to set or limit the logging format, which helps reduce errors with replicating between masters and slaves with different storage engines.

Statement-Based Logging

When statement-based logging is enabled, statements are logged to the binary log exactly as they were executed.

This mode can be enabled by setting the binlog_format system variable to STATEMENT.

In certain cases, a statement may not be deterministic, and therefore not safe for replication. If MariaDB determines that an unsafe statement has been executed, then it will issue a warning. For example:

[Warning] Unsafe statement written to the binary log using statement format since 
  BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This 
  is unsafe because the set of rows included cannot be predicted.

See Unsafe Statements for Statement-based Replication for more information.

If you need to execute non-deterministic statements, then it is safer to use row-based logging or mixed logging.

Row-Based Logging

When row-based logging is enabled, DML statements are not logged to the binary log. Instead, each insert, update, or delete performed by the statement for each row is logged to the binary log separately. DDL statements are still logged to the binary log.

This mode can be enabled by setting the binlog_format system variable to ROW.

Mixed Logging

When mixed logging is enabled, the server uses a combination of statement-based logging and row-based logging. Statement-based logging is used by default, but when the server determines a statement may not be safe for statement-based logging, it will use row-based logging instead. See Unsafe Statements for Statement-based Replication: Unsafe Statements for a list of unsafe statements.

This mode can be enabled by setting the binlog_format system variable to MIXED.

Configuring the Binary Log Format

The format for binary log events can be configured by setting the binlog_format system variable. If you have the SUPER privilege, then you can change it dynamically with SET GLOBAL. For example:

SET GLOBAL binlog_format='ROW';

You can also change it dynamically for just a specific session with SET SESSION. For example:

SET SESSION binlog_format='ROW';

It can also be set in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
binlog_format=ROW

STOP SLAVE;
SET GLOBAL slave_parallel_threads=0;
SET GLOBAL binlog_format='ROW';
SET GLOBAL slave_parallel_threads=4;
START SLAVE

Effect of the Binary Log Format on Slaves

In MariaDB 10.0.22 and later, a slave will apply any events it gets from the master, regardless of the binary log format. The binlog_format system variable only applies to normal (not replicated) updates.

The mysql Database

Statements that affect the mysql database can be logged in a different way to that expected.

If the mysql database is edited directly, logging is performed as expected according to the binlog_format. Statements that directly edit the mysql database include INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA INFILE, SELECT, and TRUNCATE TABLE.

If the mysql database is edited indirectly, statement logging is used regardless of binlog_format setting. Statements editing the mysql database indirectly include GRANT, REVOKE, SET PASSWORD, RENAME USER, ALTER, DROP and CREATE (except for the situation described below).

CREATE TABLE ... SELECT can use a combination of logging formats. The CREATE TABLE portion of the statement is logged using statement-based logging, while the SELECT portion is logged according to the value of binlog_format.