The current binary log filename is determined by the --log-bin startup option. If an extension is specified, it is ignored and only the basename is used. By default, the binary log is written into the data directory. If the filename is not set, the default value is the hostname followed by the -bin suffix.

The max_binlog_size server variable determines the maximum size of the current file, in bytes. When this size is reached, the binary log rotates: the current file is renamed and a new file with the same name is created. The archive files have names that follow this pattern: <basename>.<prog_id> where basename is identical to the current file's basename and prog_id is a sequence number consisting of six digits. The first number is 000001. There is also an index file whose name follows this pattern: <basename>.index. The name of the index file can be changed with --log-bin-index. The index file is human-readable and contains a list of the existing archived files, plus the current one. It should not be deleted or manually edited.

The binary log rotation also happens on server restart, and when one of the following commands is executed:

It is important to keep a certain number of archived binary log files. They can still be useful for incremental backups, as explained in Chapter 8, Backup and Disaster Recovery. They are still useful for replication: remember that slaves do not immediately receive events when they are written into the logs. They could lag behind, and they can possibly need to read files that have been generated days or months ago.

To retrieve the list of existing binary log files, SHOW BINARY LOGS can be used:

MariaDB [(none)]> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000040 |      3402 |
| binlog.000041 |       384 |
| binlog.000042 |       342 |
| binlog.000043 |       390 |
| binlog.000044 |      6516 |
| binlog.000045 |       342 |
| binlog.000046 |       737 |
| binlog.000047 |       932 |
| binlog.000048 |       940 |
| binlog.000049 |      2891 |
| binlog.000050 |      2410 |
| binlog.000051 |       359 |
+---------------+-----------+
12 rows in set (0.00 sec)

The @@expire_logs_days server variable, if its value is not 0, causes the old binary log files to be automatically deleted when their age reaches the specified number of days. Setting this value is dangerous, unless we are absolutely sure that no slave still needs files that reached this age. But, when determining a safe value, we should consider that a slave could crash and stay down for a file, or serious network problems could arise. Such issues could perceptibly delay the replication.

The old files can also be deleted on demand, using SQL statements. This method is much safer, but requires more work. For this reason, it is generally done by a script that is triggered via a cron job.

The procedure to delete the binary logfiles that are no longer needed is as follows:

The PURGE BINARY LOGS statement can also be used to delete the files that only contain statements older than a given datetime. For example:

PURGE BINARY LOGS BEFORE '2014-04-01 0:0:0';

When a running standalone server must become a replication master, we should take a backup of the current data, so that we can load it into the slaves. Then, the binary log can be completely deleted, because we do not need to replicate again the event that occurred until this point. To do this, we can use RESET MASTER. This statement completely deletes all the binary logfiles, empties the binary logfiles index, and recreates an empty current file whose sequence number is 000001.

The relay log is written to files following the same rules as the binary log. By default, the relay logfiles have names following the pattern: <hostname>-relay-bin.<prog_id> where hostname is the name of the slave host, and prog_id is a sequence number that has the same format as the one used by the binary log. A different name can be specified with the --relay-log startup option. There is an index file that has the same purpose and format as the binary log index file. Its default name follows this pattern: <hostname>-relay-bin.index and can be changed with --relay-log-index. Both files are located in the data directory, if no path is explicitly specified.

When the current file reaches its maximum size, MariaDB creates a new relay logfile. The maximum size is determined by the max_relay_log_size server variable. If this variable is set to 0, the maximum size is the value of max_binlog_size, which cannot be set to 0.

A new relay log file is also created each time the slave I/O thread starts. The following statements can be used to force a new file creation:

When the slave SQL thread executed the last events in a relay logfile, the file is automatically deleted, unless it is the current file. There is no way or need to affect this mechanism.

The master log information is stored by default in a file called master.info, in the data directory. Its name and path can be changed using the --master-info-file startup option. The relay log info is stored by default in a file called relay-log.info, which is in the data directory too. Its default name and path can be overridden by the --relay-log-info-file startup option.

These files should contain the information that is shown when a SHOW SLAVE STATUS statement is executed. However, while the slave threads are running, the information in the files is likely to be outdated, because the replication status data is stored in memory. The files should always be up-to-date when the slave is not running.

These files do not grow and no rotation or special maintenance operation is needed.