Activating the Binary Log
Contents
To enable binary logging, start the server with the --log-bin[=name]
option.
If you specify a filename with an extension (for example .log
), the extension will be silently ignored.
If you don't provide a name (which can, optionally, include an absolute path), the default will be datadir/log-basename-bin
, datadir/mysql-bin
or datadir/mariadb-bin
(the latter two if --log-basename is not specified, and dependent on server version). Datadir is determined by the value of the datadir system variable.
We strongly recommend you use either --log-basename or specify a filename to ensure that replication doesn't stop if the hostname of the computer changes.
The directory storing the binary logs will contain a binary log index, as well as the individual binary log files.
The binary log files will have a series of numbers as filename extensions. Each additional binary log will increment the extension number, so the oldest binary logs will have lower numbers, the most recent, higher numbers.
A new binary log, with a new extension, is created every time the server starts, the logs are flushed, or the maximum size is reached (determined by max_binlog_size).
The binary log index file contains a master list of all the binary logs, in order. From MariaDB 11.4, if GTID binlog indexing is enabled (the default), an additional index file (.idx
) is present.
A sample listing from a directory containing the binary logs:
shell> ls -l total 100 ... -rw-rw---- 1 mysql adm 2098 Apr 19 00:46 mariadb-bin.000079 -rw-rw---- 1 mysql adm 332 Apr 19 00:56 mariadb-bin.000080 -rw-rw---- 1 mysql adm 347 Apr 19 07:36 mariadb-bin.000081 -rw-rw---- 1 mysql adm 306 Apr 20 07:15 mariadb-bin.000082 -rw-rw---- 1 mysql adm 332 Apr 20 07:41 mariadb-bin.000083 -rw-rw---- 1 mysql adm 373 Apr 21 07:56 mariadb-bin.000084 -rw-rw---- 1 mysql adm 347 Apr 21 09:09 mariadb-bin.000085 -rw-rw---- 1 mysql adm 398 Apr 21 21:24 mariadb-bin.000086 -rw-rw---- 1 mysql adm 816 Apr 21 17:05 mariadb-bin.index
The binary log index file will by default have the same name as the individual binary logs, with the extension .index. You can specify an alternative name with the --log-bin-index[=filename]
option.
Clients with the SUPER privilege (or, from MariaDB 10.5.2, the BINLOG ADMIN privilege, can disable and re-enable the binary log for the current session by setting the sql_log_bin variable.
SET sql_log_bin = 0; SET sql_log_bin = 1;
Binary Log Format
There are three formats for the binary log. The default is mixed logging, which is a mix of statement-based and row-based logging. See Binary Log Formats for a full discussion.
See Also
- Setting sql_log_bin
- PURGE LOGS - Delete logs
- FLUSH LOGS - Close and rotate logs
- GTID binlog indexing