expire_logs_days ignored in my.cnf

Question

I've got a pair of MySQL databases that are set up for Master Slave replication. The slave is doing just fine.

The master, on the other hand, has been hoarding binary logs despite my best (automated) efforts.

I'm trying to set up the 'expire_logs_days' variable in MySQL's my.cnf file, but for some reason it seems to be being ignored. My my.cnf file looks something like:

[mysqld]
...
log-bin=/var/log/mysql/mysql-bin.log
server-id=1
expire_logs_days=3
log_bin_trust_function_creators=TRUE
sync_binlog=1

[mysqld_safe]
...

But when I run SHOW VARIABLES WHERE Variable_Name='expire_logs_days' in MySQL, it returns me a value of 0

I've tried:

• Restarting MySQL
• Using this line: expire_logs_days='3'
• Checked for other my.cnf files:
  • mysqld --help --verbose | grep cnf
  • Found the line: /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf order of preference
  • My my.cnf file is located at /etc/my.cnf
  • There are no files called my.cnf in the other locations
• SET GLOBAL expire_logs_days=3 DOES work within MySQL, but doesn't really solve my problem per se

And that's about all I could think to do. I've run the manual PURGE command, which works just fine, but I'd prefer (although, if there's no way around it, I'll do it anyway) to not run the PURGE command using cron.

Anybody have any ideas? I'm just about tapped.

Thanks.

Answer 1

Facts From Your Question

• Binary Logs cannot rotate out
• You said you can run PURGE BINARY LOGS

Here is my Working Theory

Since you can erase binary logs using PURGE BINARY LOGS;, I have two places for you to look that you have not mentioned

PLACE #1 : mysql-bin.index

This file contains the location of all binary logs. When expire_logs_days is set, mysqld will open this text file, check the datetime stamps in each file until it encounters a binary logs that has a timestamp less than NOW() - INTERVAL expire_logs_days DAY).

The binary logs in mysql-bin.index is expected to be numerically consecutive. If the binary logs are not numerically consecutive, log rotation is disabled.

PLACE #2 : /var/log/mysql folder

According to your my.cnf, This folder holds all the binary logs.

Here are 2 questions:

1. Are there any binary logs in /var/log/mysql that are not numerically consecutive?
2. Are there any binary logs in /var/log/mysql that are NOT IN mysql-bin.index ?

Why would these situations come up ?

Sometimes, people delete binary logs in the OS. This can throw off mysqld since mysqld uses mysql-bin.index to internally track the existence of binary logs. Simply deleting binary logs with rm -f logically breaking the log rotation mechanism as mysqld knows it.

RECOMMENDATION

If either or both are the case, you can clean this up as follows:

mysql -ANe"RESET MASTER"
service mysql stop
cd /var/log/mysql
rm -f mysql-bin.*
cd
service mysql start

After this, you should have a brand spanking new binary log setup.

Give it a Try !!!

Answer 2

Mysql (community) Version 8.0.17-1.sles12 - OpenSUSE tumbleweed 2019.10.02

mysql> SET GLOBAL expire_logs_days = 4;
ERROR 3683 (HY000): The option expire_logs_days and binlog_expire_logs_seconds
cannot be used together. Please use binlog_expire_logs_seconds to set the expire
time (expire_logs_days is deprecated)

..