0

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 ?

  • An idea please ? – ML61 Mar 26 at 10:13
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.

  • Well, I put 600 seconds yesterday. Should I restore? – ML61 Mar 26 at 21:20
  • Did you use 600 seconds for binlog_expire_logs_seconds? – Alok Patwal Mar 26 at 21:24
  • yes 600 seconds for binlog_expire_logs_seconds I only have one server, there is no replication. I read in other questions that you have to put 0 – ML61 Mar 27 at 8:18
  • Do you know if I have to restore my server? – ML61 Mar 28 at 16:56
  • A value of 0 for binlog_expire_logs_seconds would mean that automatic removal of binary logs will not happen if expire_logs_days is also not set. expire_logs_days is a deprecated variable in MySQL 8.0. I would recommend you go through the official MySQL docs for answers to such questions. binlog_expire_logs_seconds, expire_logs_days – Alok Patwal Mar 31 at 8:12
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.