# Using and Maintaining the Binary Log
See [Overview of the Binary Log](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/overview-of-the-binary-log) for a general overview of what the binary log is, and [Activating the Binary Log](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/activating-the-binary-log) for how to make sure it's running on your system.
Binary log file security is documented on [Securing MariaDB Logs](https://mariadb.com/docs/server/server-management/server-monitoring-logs/securing-mariadb-logs).
For details on using the binary log for replication, see the [Replication](https://mariadb.com/docs/server/ha-and-performance/standard-replication) section.
## Purging Log Files
To delete all binary logs on the server, run the [RESET MASTER](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/replication-statements/reset-master) command. To delete all binary logs before a certain datetime, or up to a certain number, use [PURGE BINARY LOGS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/purge-binary-logs).
If a replica is active but has yet to read from a binary log you attempt to delete, the statement fails with an error. However, if the replica is not connected and has yet to read from a log you delete, the file is deleted, but the replica is unable to continue replicating once it connects again.
Logs can also be removed automatically with the [expire\_logs\_days](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#expire_logs_days) system variable. This is set to `0` by default (no removal). It can be set to a time, in days, after which a binary log file is automatically removed. Log files are only checked for being older than [expire\_logs\_days](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#expire_logs_days) upon log rotation, so if your binary log fills up slowly and does not reach [max\_binlog\_size](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#max_binlog_size) on a daily basis, you may see older logs still being kept. You can also force log rotation, and so expiry deletes, by running [FLUSH BINARY LOGS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/flush-commands/flush) on a regular basis. Always set [expire\_logs\_days](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#expire_logs_days) higher than any possible replica lag.
From [MariaDB 10.6](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/10.6), the [binlog\_expire\_logs\_seconds](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#binlog_expire_logs_seconds) variable allows more precise control over binlog deletion and takes precedence if both are non-zero.
{% 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 %}
If the binary log index file has been removed, or incorrectly manually edited, all of the above forms of purging logs fail. The .index file is a plain text file and can be manually recreated or edited so that it lists only the binary log files that are present, in numeric/age order.
### Examples of Log File Purging
```sql
PURGE BINARY LOGS TO 'mariadb-bin.000063';
```
```sql
PURGE BINARY LOGS BEFORE '2013-04-22 09:55:22';
```
### Safely Purging Binary Log Files While Replicating
To be sure replication is not broken while deleting log files, perform the following steps:
* Get a listing of binary log files on the primary by running [SHOW BINARY LOGS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-binary-logs).
* Go to each replica server and run [SHOW REPLICA STATUS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-replica-status) to check which binary log file each replica is currently reading.
* Find the earliest log file still being read by a replica. No log files before this one will be needed.
* If you wish, make a backup of the log files to be deleted
* Purge all log files before (not including) the file identified above.
## Limiting the Binlog Size
{% hint style="info" %}
This feature is available from MariaDB 11.4.
{% endhint %}
You can limit the size of the binlog by setting the [max\_binlog\_total\_size](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#max_binlog_total_size) system variable. If not set to zero, the total size of the binlog is stored in the [binlog\_disk\_use](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-status-variables#binlog_disk_use) status variable. It's also possible to limit the size of a single binlog file by setting [max\_binlog\_size](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables#max_binlog_size).
## Selectively Logging to the Binary Log
By default, all changes to data or data structure are logged. This behavior can be changed by starting the server with the `--binlog-ignore-db=database_name` or `--binlog-do-db=database_name` [options](https://mariadb.com/docs/server/server-management/starting-and-stopping-mariadb/mariadbd-options).
`--binlog-ignore-db=database_name` specified a database to ignore for logging purposes, while `--binlog-do-db=database_name` will not log any statements unless they apply to the specified database.
Neither option accepts comma-delimited lists of multiple databases as an option, since a database name can contain a comma. To apply to multiple databases, use the option multiple times.
`--binlog-ignore-db=database_name` behaves differently depending on whether statement-based or row-based logging is used. For statement-based logging, the server will not log any statement where the *default database* is database\_name. The default database is set with the [USE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/use-database) statement.
Similarly, `--binlog-do-db=database_name` also behaves differently depending on whether statement-based or row-based logging is used.
For statement-based logging, the server will only log statement where the *default database* is database\_name. The default database is set with the [USE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/use-database) statement.
For row-based logging, the server will log any updates to any tables in the named database/s, irrespective of the current database.
### Examples of Selective Logging
Assume the server has started with the option `--binlog-ignore-db=employees`. The following example *is* logged if statement-based logging is used, and *is not* logged with row-based logging.
```sql
USE customers;
UPDATE employees.details SET bonus=bonus*1.2;
```
This is because statement-based logging examines the default database, in this case, `customers`. Since `customers` is not specified in the ignore list, the statement is logged. If row-based logging is used, the example is not logged, because updates are written to the tables in the `employees` database.
Assume now that the server started with the option `--binlog-do-db=employees`. The following example *is not* logged if statement-based logging is used, and *is* logged with row-based logging.
```sql
USE customers;
UPDATE employees.details SET bonus=bonus*1.2;
```
This is again because statement-based logging examines the default database, in this case, `customers`. Since `customers` is not specified in the do list, the statement is not logged. If row-based logging is used, the example is logged as updates are written to the tables in the `employees` database.
## Effects of Full Disk Errors on Binary Logging
{% 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 %}
If MariaDB encounters a full disk error while trying to write to a binary log file, then it will keep retrying the write every 60 seconds. Log messages will get written to the error log every 600 seconds. For example:
```
2018-11-27 2:46:46 140278181563136 [Warning] mysqld: Disk is full writing '/var/lib/mariadb-bin.00001' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
2018-11-27 2:46:46 140278181563136 [Warning] mysqld: Retry in 60 secs. Message reprinted in 600 secs
```
However, if MariaDB encounters a full disk error while trying to open a new binary log file, then it will disable binary logging entirely. A log message like the following will be written to the error log:
```
2018-11-27 3:30:49 140278181563136 [ERROR] Could not open '/var/lib/mariadb-bin.00002 for logging (error 28). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
2018-11-27 3:30:49 140278181563136 [ERROR] mysqld: Error writing file '(null)' (errno: 9 "Bad file descriptor")
2018-11-27 3:30:49 140278181563136 [ERROR] mysqld: Error writing file '(null)' (errno: 28 "No space left on device")
```
## See Also
* [PURGE LOGS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/purge-binary-logs)
*This page is licensed: CC BY-SA / Gnu FDL*
{% @marketo/form formId="4316" %}
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/using-and-maintaining-the-binary-log.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.