16

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.

Cody S
  • 4,744
  • 8
  • 33
  • 64

4 Answers4

12

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

Community
  • 1
  • 1
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
  • thanks, I've hit **2.2** - since I do not want to reset master; this command would show what to remove `diff <(find /var/log/mysql/ -iname "mysql-bin.[0-9]*" |sort -n) /var/log/mysql/mysql-bin.index | sed -ne 's/^ – Matija Nalis Mar 24 '16 at 02:26
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)

..

Dutch Glory
  • 23,373
  • 1
  • 17
  • 6
0

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.

ethrbunny
  • 10,379
  • 9
  • 69
  • 131
  • 1) I'm not interested in dumping mysql. 2) Yeah, I could do that, and I may have to, but fundamentally at this point I find the issue I'm having to be pretty fascinating, so I'm hoping for an answer rather than a work-around (anything is better than nothing though...) 3) Frankly, I don't care if the binlog is 1KB or 1TB, I just want it deleted after 3 days...so I don't know that this option will really achieve what I'm looking for. – Cody S Mar 19 '13 at 00:31
0

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.

Brian Papantonio
  • 860
  • 1
  • 7
  • 9