# Activating the Binary Log

## Turning on Binary Logging

To enable binary logging, start the server with the [--log-bin](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#log_bin) option. Alternatively, add this to the [configuration file](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/configuring-mariadb/configuring-mariadb-with-option-files) (for instance, `my.cnf`), then restart the server:

```ini
[client-server]
log_bin
```

Optionally, you can specify a **basename** (with or without a path). If you don't, MariaDB assigns a default basename (for instance, `c525d37c-b2ff-4543-b06f-87012d142d44-bin)`, derived from the UUID or hostname of the computer the server runs on. To the basename, file extensions are added, determining the nature of the log files. See [this section](#log-file-organization) for details.

{% if  %}
{% hint style="info" %}
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](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/innodb-based-binary-log) for more information.
{% endhint %}
{% endif %}

On server start, you can set a basename (in the example, with a path) like this:

```bash
mariadbd --log-bin=/var/log/mariadb/mariadb-logs
```

The following applies:

* If you specify a binary log basename with an extension (for example `.log`), the extension is silently ignored.
* If you don't specify a path with the basename, the server logs into *`datadir` .* (*`Datadir`* is determined by the value of the [datadir](https://mariadb.com/docs/server/variables-and-modes/server-system-variables#datadir) system variable.)
* If you don't specify a basename, it is strongly recommended to use [`--log-basename`](https://mariadb.com/docs/server/starting-and-stopping-mariadb/mariadbd-options#log-basename) or to ensure that [replication](https://mariadb.com/docs/server/ha-and-performance/standard-replication) doesn't stop if the hostname of the computer changes.

## Verifying Logging is On

Once `log-bin` or `log_bin` is configured and the server has been restarted, binary logging is enabled. To verify that, issue this statement:

```sql
SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
```

## Viewing Log Files

{% if  %}
{% hint style="info" %}
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](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/innodb-based-binary-log) for more information.
{% endhint %}
{% endif %}

To view the log files created by the server, issue this statement:

```sql
SHOW BINARY LOGS;
+-------------------------------------------------+-----------+
| Log_name                                        | File_size |
+-------------------------------------------------+-----------+
| c525d37c-b2ff-4543-b06f-87012d142d44-bin.000001 |       437 |
| c525d37c-b2ff-4543-b06f-87012d142d44-bin.000002 |       433 |
+-------------------------------------------------+-----------+
```

To see which log file is currently used, issue this statement:

```sql
SHOW BINLOG STATUS \G
*************************** 1. row ***************************
            File: c525d37c-b2ff-4543-b06f-87012d142d44-bin.000002
        Position: 433
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
```

To find out where log files are stored, issue this statement:

{% code overflow="wrap" %}

```sql
SHOW VARIABLES LIKE 'log_bin_basename' \G
*************************** 1. row ***************************
Variable_name: log_bin_basename
        Value: /opt/homebrew/var/mysql/c525d37c-b2ff-4543-b06f-87012d142d44-bin
```

{% endcode %}

* `/opt/homebrew/var/mysql/` is the **storage location**.
* `c525d37c-b2ff-4543-b06f-87012d142d44-bin` is the **basename** of the binary log files.

## Log File Organization

{% if  %}
{% hint style="info" %}
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](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/innodb-based-binary-log) for more information.
{% endhint %}
{% endif %}

Knowing the **basename**, you can view the log files on the file system, too. Change directory (`cd`) to the **storage location**, and issue this command:

```bash
ls -1 c525d37c-b2ff-4543-b06f-87012d142d44-bin.*
c525d37c-b2ff-4543-b06f-87012d142d44-bin.000001
c525d37c-b2ff-4543-b06f-87012d142d44-bin.000001.idx
c525d37c-b2ff-4543-b06f-87012d142d44-bin.000002
c525d37c-b2ff-4543-b06f-87012d142d44-bin.000002.idx
c525d37c-b2ff-4543-b06f-87012d142d44-bin.index
```

* **The binary log index** is the file containing an `.index` extension. It is a plain-text file, containing a master list of the binary log files, in order.\
  By default, the name of the index file is *basename.index*. This can be overridden with the [`--log-bin-index`](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#log_bin_index) option.
* **The binary log files** have an extension using consecutive numbers, starting with `.000001`. (The higher the number, the newer the log file is.)
* **The binary log files for** [**GTID binlog indexing**](https://mariadb.com/docs/server/ha-and-performance/standard-replication/gtid#binlog-indexing) (available from MariaDB 11.4) have an `.idx` extension.

A new binary log file with a new extension (number) is created:

* Every time the server starts.
* When the logs are flushed with a [`FLUSH LOGS`](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/flush-commands/flush) statement.
* When the maximum size for a binary log file is reached. (This is determined by [max\_binlog\_size](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#max_binlog_size).)

## Turning off Logging per Session

Clients with the [BINLOG ADMIN](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant#binlog-admin) privilege can disable and re-enable binary logging for the current session by setting the [sql\_log\_bin](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#sql_log_bin) variable:

```sql
SET sql_log_bin = 0; -- turns off logging
SET sql_log_bin = 1; -- turns on logging
```

## Reading Log Files

{% if  %}
{% hint style="info" %}
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](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/innodb-based-binary-log) for more information.
{% endhint %}
{% endif %}

Log files, with the exception of the *index* log file, are binary-encoded. To display them in a human-readable format, change directory to the [storage location](#viewing-log-files) of the log files, and issue this [mariadb-binlog](https://mariadb.com/docs/server/clients-and-utilities/logging-tools/mariadb-binlog) command:

```bash
mariadb-binlog c525d37c-b2ff-4543-b06f-87012d142d44-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#260205 17:13:09 server id 1  end_log_pos 256 CRC32 0x59977cc7 	Start: binlog v 4, server v 12.1.2-MariaDB-log created 260205 17:13:09 at startup
ROLLBACK/*!*/;
…
```

To store that output permanently (for instance, for later processing), issue a command like this:

```bash
mariadb-binlog c525d37c-b2ff-4543-b06f-87012d142d44-bin.000001 > binlog.sql
```

## Log File Security

{% if  %}
{% hint style="info" %}
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](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/innodb-based-binary-log) for more information.
{% endhint %}
{% endif %}

For instructions how to secure binary log **files**, see [Securing MariaDB Logs](https://mariadb.com/docs/server/server-management/server-monitoring-logs/securing-mariadb-logs).

## Binary Log Format

There are three formats for the binary log. The default is [mixed logging](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log-formats#mixed-logging), which is a mix of [statement-based](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log-formats#statement-based-logging) and [row-based logging](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log-formats#row-based-logging). See [Binary Log Formats](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/binary-log-formats) for a detailed discussion.

## See Also

* [Setting sql\_log\_bin](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-sql_log_bin)
* [PURGE LOGS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/purge-binary-logs) - Delete logs
* [FLUSH LOGS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/flush-commands/flush) - Close and rotate logs
* [GTID binlog indexing](https://mariadb.com/docs/server/ha-and-performance/standard-replication/gtid#binlog-indexing)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}