Home / server / Questions / 179165
In Process
blacktip
Asked: 2010-09-09 04:47:16 +0800 CST

Can expire_logs_days be less than 1 day in MySQL?

  • 772

So... yesterday I received an "after the fact email" about a campaign that has started for one of the services that I run. Now the DB server is getting hammered, hard, to the tune of about 300mb/min in binary logging for the replicate. As you could imagine, this is chewing up space at a fairly tremendous rate.

My normal 7 day expiry of binary logs just isn't cutting it. I've resorted to truncating logs to just the last for 4 hours with(I'm verifying that replication is up to date with mk-heartbeat):

PURGE MASTER LOGS BEFORE DATE_SUB( NOW(), INTERVAL 4 HOUR);

I'm just running that from cron every few hours to weather the storm, but it made me question the minimum value for expire_logs_days. I haven't come across a value that is less than 1, but that doesn't mean that it isn't possible. http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_expire_logs_days gives the type as being numeric, but doesn't indicate if it's expecting integers.

mysql logging log-files mysql5

4 Answers

  1. Actually, there is a way to emulate it.

    Here are the steps to purge binary logs to 1 hour.

    STEP 01) Create an SQL script that will delete all binary logs whose timestamp is older than an hour:

    echo "FLUSH LOGS;" > /usr/bin/purge.sql
echo "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 1 HOUR;" >> /usr/bin/purge.sql

    STEP 02) Create a shell script (/usr/bin/purge.sh) to call mysql with purge.sql

    mysql -uroot -p... < /usr/bin/purge.sql

    STEP 03) Make /usr/bin/purge.sh executable

    chmod +x /usr/bin/purge.sh

    STEP 04) Add usr/bin/purge.sh to the crontab to kick off every hour

    0 * * * * /usr/bin/purge.sh

    Give it a Try !!!

    • 7

  2. Experimenting was the order of the evening...

    mysql> set @@global.expire_logs_days=0.75;
ERROR 1232 (42000): Incorrect argument type to variable 'expire_logs_days'
mysql> set @@global.expire_logs_days=.75;
ERROR 1232 (42000): Incorrect argument type to variable 'expire_logs_days'
mysql> set @@global.expire_logs_days=3.4;
ERROR 1232 (42000): Incorrect argument type to variable 'expire_logs_days'
mysql> set @@global.expire_logs_days=3/4;
ERROR 1232 (42000): Incorrect argument type to variable 'expire_logs_days'
mysql> set @@global.expire_logs_days=F;
ERROR 1232 (42000): Incorrect argument type to variable 'expire_logs_days'
mysql> set @@global.expire_logs_days=0xF;
ERROR 1232 (42000): Incorrect argument type to variable 'expire_logs_days'
mysql> set @@global.expire_logs_days=1;
Query OK, 0 rows affected (0.00 sec)
    • 6

  3. That page does say the range is 0-99.. so yeah, it is an integer.

    0 = No expire..

    You have got me wondering what 0.5 would do.. I'm thinking it would ignore the .5 part and just not expire them..

    • 3

  4. 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)

    ..

    SET PERSIST binlog_expire_logs_seconds = 86400;

    I added this my.cnf file

    [mysqld]
binlog_expire_logs_seconds = 86400
expire_logs_days = 1
    • 1