The binary log files are used when you have configured replication between two (or more) mySQL or MariaDB databases.
For example, let's say you have configured Master-Slave replication on Linux.
The following command can be used to determine the primary configuration file being used by mySQL or MariaDB. In this example, /etc/my.cnf is the primary configuration file being used by mySQL or MariaDB.
~]# mysql --help | grep /my.cnf | xargs ls ls: cannot access /etc/mysql/my.cnf: No such file or directory ls: cannot access /usr/etc/my.cnf: No such file or directory ls: cannot access ~/.my.cnf: No such file or directory /etc/my.cnf
The log-bin directive in the main mySQL configuration file (e.g. /etc/my.cnf) will define the name and location of the binary log files. In this example, the binary log files are located in the /var/lib/mysql directory.
Likewise, the max_binlog_size directive will determine how large a binary log file can become. It usually does not make much sense to increase or decrease this value if the goal is to reduce the number of binary logs that are being rotated, as a smaller size will just create more logs and a larger size will create less logs. At the end of the day, the logs will still use up the same amount of storage.
The expire_logs_days directive will determine how long the binary logs will be retained. This option has been deprecated after mySQL version 8.0.
If using mySQL version 8.0 or higher, the binlog_expire_logs_seconds directive can be used. The mysql -V command can be used to display the version of mySQL.
These directives will make it so that the binary log files are rotated when the log file reaches 100 MB.
~]# ls --long /var/lib/mysql -rw-r----- 1 mysql mysql 524288398 Jan 10 17:07 binary-log.000003 -rw-r----- 1 mysql mysql 524288398 Jan 10 17:07 binary-log.000002 -rw-r----- 1 mysql mysql 134427636 Jan 11 05:51 binary-log.000001 -rw-r----- 1 mysql mysql 66 Jan 10 17:07 binary-log.index
Before purging the binary log files, you will want to determine the current binary log files being used for replication. Assuming you have logged into your MariaDB or mySQL server or configure passwordless authentication, and the user issuing the following commands has the Alter_priv, you can use the show master status command to get the name of the binary log file currently being used by the master.
show master status;
Which should return something like this. If no output is returned, this may suggest that replication has not been configured.
On the other mySQL or MariaDB systems being used for replication, the show slave status command can be used to determine the binary log file being used for replication.
show slave status;
Which should return something like this. Notice in this example the the slave is running and is using the same binary log as the master.
Master_Log_File: binary-log.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes
In this example, since binary-log.000003 is being used for replication, the 000001 and 000002 binary log files can be purged. The following command will purge binary-log.000001 and binary-log.000002. Basically this command does not purge the binary log file listed in the command, and instead purges the binary log files prior to the binary log file listed in this command.
purge binary logs to 'binary-log.0000003'