MySQL 在 my.cnf 中忽略 expire_logs_days

expire_logs_days ignored in my.cnf

提问by Cody S

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

我有一对为主从复制设置的 MySQL 数据库。奴隶做得很好。

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

另一方面，尽管我尽了最大的（自动化）努力，但 master 一直在囤积二进制日志。

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:

我正在尝试在 MySQL 的 my.cnf 文件中设置“expire_logs_days”变量，但由于某种原因它似乎被忽略了。我的 my.cnf 文件看起来像：

[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

但是当我SHOW VARIABLES WHERE Variable_Name='expire_logs_days'在 MySQL 中运行时，它返回一个值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.cnffile is located at /etc/my.cnf
  • There are no files called my.cnf in the other locations
• SET GLOBAL expire_logs_days=3DOES work within MySQL, but doesn't really solve my problem per se

• 重启 MySQL
• 使用这一行： expire_logs_days='3'
• 检查其他 my.cnf 文件：
  • mysqld --help --verbose | grep cnf
  • 找到一行： /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf order of preference
  • 我的my.cnf文件位于/etc/my.cnf
  • 其他位置没有名为 my.cnf 的文件
• SET GLOBAL expire_logs_days=3确实在 MySQL 中工作，但本身并没有真正解决我的问题

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.

这就是我能想到的所有事情。我已经运行了手动 PURGE 命令，它工作得很好，但我更喜欢（虽然，如果没有办法解决它，我还是会这样做）不使用 cron 运行 PURGE 命令。

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

有人有任何想法吗？我快被敲响了。

Thanks.

谢谢。

采纳答案by RolandoMySQLDBA

Facts From Your Question

你的问题中的事实

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

• 二进制日志不能轮换
• 你说你可以运行 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

由于您可以使用 擦除二进制日志PURGE BINARY LOGS;，因此我有两个地方供您查看，但您没有提到

PLACE #1 : mysql-bin.index

地点#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).

此文件包含所有二进制日志的位置。当设置 expire_logs_days 时，mysqld 将打开此文本文件，检查每个文件中的日期时间戳，直到遇到时间戳小于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.

mysql-bin.index 中的二进制日志预计在数字上是连续的。如果二进制日志在数字上不连续，则禁用日志轮换。

PLACE #2 : /var/log/mysqlfolder

地点#2：/var/log/mysql文件夹

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

根据您的my.cnf, 此文件夹包含所有二进制日志。

Here are 2 questions:

这里有2个问题：

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

1. 是否有任何二进制日志在/var/log/mysql数字上不是连续的？
2. 是否有任何二进制日志/var/log/mysql被NOT INmysql-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.indexto internally track the existence of binary logs. Simply deleting binary logs with rm -flogically breaking the log rotation mechanism as mysqld knows it.

有时，人们会删除操作系统中的二进制日志。这可以摆脱 mysqld，因为 mysqld 用于mysql-bin.index在内部跟踪二进制日志的存在。简单地删除二进制日志，并在rm -f逻辑上打破 mysqld 所知道的日志轮换机制。

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

试一试 ！！！

回答by Brian Papantonio

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.

更改该配置文件中的任何 OTHER 选项是否有任何影响？我们需要确定我们正在使用正确的配置文件。更改 server-id 的值可能是一个不错的选择。

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

如果没有效果，让我们通过运行找到正确的文件：

ps aux | grep mysqld

ps辅助| grep mysqld

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

并查看 --defaults-file 或 --defaults-extra-file 的值

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

如果您仍然感到困惑，请确保没有在某处设置 --init-file 将值设置为 0。

回答by Dutch Glory

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

Mysql（社区）版本 8.0.17-1.sles12 - OpenSUSE 风滚草 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)

..

..

回答by ethrbunny

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:

我不确定您的 my.cnf 发生了什么，但是对于更大的问题，您似乎有几个选择：

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:

1. 运行 mysqldump 并指定您希望刷新/清除文件
2. 运行“清除”（就像你一样）
3. 设置max_binlog_size。根据文档：

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.

此外，当二进制日志的大小达到 max_binlog_size 系统变量的值时，将刷新二进制日志。

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

听起来#3 会expire_logs_days在执行冲洗/清除时执行此操作。