The master's binary logs and the slave's replication logs keep growing while the masters perform their normal activities and the slaves replicate their work. They need to be rotated and old files need to be deleted, to avoid running out of disk space. This section explains how to do this.
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:
FLUSH LOGS
FLUSH BINARY LOGS
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:
SHOW SLAVE STATUS
.Master_Log_File
column indicates which binary logfile the slave is reading. A script can do this by ordering these values alphabetically.binlog-000050
. To delete older files, we will use the following statement: PURGE BINARY LOGS TO 'binlog.000050';
.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:
FLUSH RELAY LOGS
FLUSH LOGS
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.