Binary Log Formats

You are viewing an old version of this article. View the current version here.

Formats

There are three formats for binary logging - statement-based, row-based and mixed. Regardless of the format, logs are always stored in binary, not plain text, format, and so are not viewable with a regular editor. However, MariaDB includes mysqlbinlog, a commandline tool for plain text processing of binary logs.

The format is determined by the value of the binlog_format system variable.

Statement-Based

The default format until MariaDB 10.2.3, statement-based logging logs all SQL statements that make changes to the data or structure of a table. Enable with --binlog-format=STATEMENT.

In certain cases, a statement may not be deterministic, and therefore not safe for replication. If MariaDB determines this it will issue the warning Statement may not be safe to log in statement format. See the Mixed section below for a list of cases where this applies, and the log_warnings system variable for details of logging these warnings.

Row-Based

Records events affecting individual table rows. Enable with --binlog-format=ROW.

Mixed

The default format from MariaDB 10.2.4. A combination of statement and row-based logging. In mixed logging, statement-based logging is used by default, but when MariaDB determines a statement may not be safe for statement-based replication, it will use the row-based format instead. Enable with --binlog-format=MIXED.

Row-based instead of statement-based logging will be used in the following situations:

  • INSERT-DELAYED statements.
  • When a table with an AUTO_INCREMENT column is updated and a trigger or stored function is used.
  • The LOAD_FILE() function is used.
  • The ROW_COUNT() or FOUND_ROWS() functions are used.
  • The USER() or CURRENT_USER() functions are used.
  • The UUID() function is used
  • One of the tables in the statement is a log table in the mysql database.
  • A statement refers to a system variable (there are some exceptions if used with session scope only).
  • A user-defined function is used.
  • If row-based logging is used for a statement, and the session executing the statement has any temporary tables, row-based logging is used for the remaining statements until the temporary table is dropped. This is because temporary tables can't use row-based logging, so if it is used due to one of the above conditions, all subsequent statements using that table are unsafe. The server deals with this situation by treating all statements in the session as unsafe for statement-based logging until the temporary table is dropped.

A storage engine can also set or limit the logging format, which helps reduce errors with replicating between masters and slaves with different storage engines.

Changing the Binary Log Format

The default binary log format is statement-based. It can be set at runtime with --binlog-format=format, and can also be changed by setting the global (with the SUPER privilege) or session value of the binlog_format server variable.

Be careful changing the binary log format when using replication. The binary log format can only be set by the server for itself. Changing the binary log format on a master has no effect on the slave's binary log format. This can cause replication to give inconsistent results or to fail. Note also that changing the global value does not change the session values of any currently running threads, and with parallel replication, even after the slaves are stopped with STOP SLAVE and restarted, the worker threads keep running until the slave_parallel_threads value is changed. A workaround then is to set global slave_parallel_threads=0, which will make the threads exit immediately.

Starting from MariaDB 10.0.22 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.

If you are running MySQL or an older MariaDB, you should be aware of that if you are running the slave in binlog_format=STATEMENT mode, the slave will stop if the master is used with binlog_format set to anything else than STATEMENT.

Although you should rarely need to change the binary logging format, you may want to in the following cases:

  • If one, or a few, statements update many rows, statement-logging will be more efficient. Since this situation is common, statement-based logging is the default.
  • Many statements that ultimately only have a small impact on the table rows may also be more efficiently logged with row-based logging.
  • A statement may take a long time to execute, but ultimately only have a small impact on the table rows, so replication with row-based logging would be more efficient.

Examples

SET GLOBAL binlog_format=ROW;

SET SESSION binlog_format=MIXED;

SET binlog_format=STATEMENT;

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.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.