How to change the expiration date of binlog files?

Question

I have a server with Ubuntu 18.04 and MySQL 8.0.11

I have binlog files in /var/lib/mysql that make several GO and my server will soon saturate.

How to change the expiration date of binlog files ?

If I go to :

nano /etc/mysql/my.cnf

The file contains :

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

If I go to :

nano /etc/mysql/conf.d/mysql.cnf

The file contains :

[mysql]

If I go to :

nano /etc/mysql/mysql.conf.d/mysqld.cnf

The file contains :

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

I can not find the file to configure. Is it safe for the database to change the timeout ?

Answer 1

How to change the expiration date of binlog files ?

To purge binary logs prior to a binary log file or prior to a particular date, use purge binary logs statement.

To set an expiration period for binary logs, use binlog_expire_logs_seconds. I would recommend to keep this more than 2 days, whether or not you have replication enabled.

I can not find the file to configure.

To see the order in which MySQL looks at the configuration files, run the following:

mysqld --help --verbose | grep -A 2 "Default options are read from"

If you also want to see what values the different variables would take on start, remove the grep part.

For a variable whose value is specified multiple times in the configuration files, or multiple times in the same file, the last value would be considered by MySQL. Some of these configuration files may not already be present, in which case you can create them on your own, and under [mysqld] in the configuration file, add the parameters you want to set.

Is it safe for the database to change the timeout ?

Yes, it is safe if there is no replication involved. Even if a slave does exist, if all the logs that are to be deleted have been already processed and executed by the slave, it is safe to delete those logs from master.

Answer 2

log_bin = 0 to turn off writing to the binlogs at all.

Answer 3

Directly from the command line / terminal, connected to the server with root privileges SET PERSIST or SET GLOBAL is the command to use, you can put directly the value in seconds or the conversion math operation in parentheses:

mysql> SET PERSIST binlog_expire_logs_seconds = (60*60*24*3);

... this is to set the variable to 3 days (in seconds).

You can see more related commands in verge's answer: https://dba.stackexchange.com/a/306954/163431.

Related official documentation: https://dev.mysql.com/blog-archive/mysql-8-0-persisting-configuration-variables/.