Best practices for managing binary logs, including setting expiration policies (`expire_logs_days`), purging old logs, and using the `mysqlbinlog` utility.
See Overview of the Binary Log for a general overview of what the binary log is and Activating the Binary Log for how to make sure it's running on your system.
For details on using the binary log for replication, see the Replication section.
To delete all binary log files on the server, run the RESET MASTER command. To delete all binary logs before a certain datetime, or up to a certain number, use PURGE BINARY LOGS.
If a replica is active but has yet to read from a binary log file you attempt to delete, the statement will fail with an error. However, if the replica is not connected and has yet to read from a log file you delete, the file will be deleted, but the replica will be unable to continue replicating once it connects again.
Log files can also be removed automatically with the system variable. This is set to 0 by default (no removal), but can be set to a time, in days, after which a binary log file will be automatically removed. Log files will only be checked for being older than upon log rotation, so if your binary log only fills up slowly and does not reach on a daily basis, you may see older log files still being kept. You can also force log rotation, and so expiry deletes, by running on a regular basis. Always set higher than any possible replica lag.
From , the variable allows more precise control over binlog deletion and takes precedence if both are non-zero.
If the binary log index file has been removed, or incorrectly manually edited, all of the above forms of purging log files will 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.
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 .
Go to each replica server and run 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
MariaDB starting with
From , it's possible to limit the size of the binlog by setting the system variable. If not set to zero, the total size of the binlog will be stored in the status variable. It's also possible to limit the size of a single binlog file by setting .
There are three formats for the binary log. The default is statement-based logging, while row-based logging and a mix of the two formats are also possible. See for a full discussion.
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 .
--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 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 statement.
For row-based logging, the server will log any updates to any tables in the named database/s, irrespective of the current database.
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.
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 will be logged. If row-based logging is used, the example will not be logged as updates are written to the tables in the employees database.
Assume instead 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.
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 will not be logged. If row-based logging is used, the example will be logged as updates are written to the tables in the employees database.
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:
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:
This page is licensed: CC BY-SA / Gnu FDL
Purge all log files before (not including) the file identified above.
PURGE BINARY LOGS TO 'mariadb-bin.000063';PURGE BINARY LOGS BEFORE '2013-04-22 09:55:22';USE customers;
UPDATE employees.details SET bonus=bonus*1.2;USE customers;
UPDATE employees.details SET bonus=bonus*1.2;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 secs2018-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")