Overview of the Binary Log

Introduction to the purpose and structure of the binary log, explaining how it records data changes (DML) and structure changes (DDL) for replication and recovery.

The binary log contains a record of all changes to the databases, both data and structure, as well as how long each statement took to execute. It consists of a set of binary log files and an index.

This means that statements such as CREATE, ALTER, INSERT, UPDATE and DELETE are logged, whereas statements that have no effect on the data, such as SELECT and SHOW, are not be logged. If you want to log these, too (at a cost in performance), use the general query log.

If a statement may potentially have an effect, but doesn't, such as an UPDATE or DELETE that returns no rows, it is still logged (this applies to the default statement-based logging, not to row-based logging - see Binary Log Formats).

The purpose of the binary log is:

• To enable replication, where data is sent from one or more primary servers to one or more replica servers based on the contents of the binary log.

• To assist in backup operations.

A MariaDB server with the binary log enabled runs slightly more slowly.

It is important to protect the binary log, as it may contain sensitive information, including passwords.

Binary logs are stored in a binary, not plain text, format, and so are not readable with a regular editor. However, MariaDB includes mariadb-binlog, a command-line tool for plain-text processing of binary logs.

From MariaDB 12.3, InnoDB-based binary logs can be used. (This is configurable, and not the default.)

If configured, binary logs are stored in InnoDB tablespaces, rather than binary files. This removes the need of protecting binary logs separately, since they'll "inherit" the same protection as other MariaDB database tables. Also, any other information about log files doesn't apply – for example, you cannot specify a storage location for binary logs stored in an InnoDB tablespace.

InnoDB-based binary logs are enabled by setting binlog_storage_engine=innodb in the server configuration. See InnoDB-Based Binary Log for more information.

_{This page is licensed: CC BY-SA / Gnu FDL}