CODEWITHSUNDEEP
mysqldatabase-replication
Cody S
Cody S

Reputation: 4824

expire_logs_days ignored in my.cnf

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:

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.

Upvotes: 17

Views: 44388

Answers (5)

tobiasbhn
tobiasbhn

Reputation: 66

For everyone hitting this Error running on MariaDB Versions 11.4.1 and 11.4.2: There is a bug (more like unwanted behaviour) in mentioned Versions.

https://jira.mariadb.org/browse/MDEV-34504

The following config fixed it for us, focus on slave_connections_needed_for_purge:

[mariadb]
log_bin=binlog
expire_logs_days=5
# new settings in mariadb version 11.4.2 which
# breaks previous automatic cleanup expire_logs_days
slave_connections_needed_for_purge=0

On the first restart afterwards the binlogs did clean up automatically.

Upvotes: 0

Dutch Glory
Dutch Glory

Reputation: 26483

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)

..

Upvotes: 2

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

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 !!!

Upvotes: 13

Brian Papantonio
Brian Papantonio

Reputation: 870

Does changing any OTHER option in that config file have any effect? We need to be positive we're hitting the right config file. Changing the value of server-id is probably a good candidate.

If no effect, let's find the right file by running:

ps aux | grep mysqld

And look at the values of --defaults-file or --defaults-extra-file

And if you're still stumped, make sure there isn't --init-file set somewhere setting the value to 0.

Upvotes: 0

ethrbunny
ethrbunny

Reputation: 10469

I'm not sure what's happening with your my.cnf but w/re to the larger issue it looks like you have a few choices:

  1. run mysqldump and specify that you want the files flushed / purged
  2. run 'purge' (as you have been )
  3. set max_binlog_size. According to the docs:

Possible removals happen at startup and when the binary log is flushed.

and

In addition, the binary log is flushed when its size reaches the value of the max_binlog_size system variable.

It sounds like #3 will do the expire_logs_days when it does the flush / purge.

Upvotes: 0

Related Questions