Can expire_logs_days be less than 1 day in MySQL?

Question

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.

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

Solution 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)

Solution 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..

Solution 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

View more solutions

Comments

[lg.]

I think you chose the best solution.

[blacktip]

dev.mysql.com/doc/refman/5.0/en/numeric-types.html isn't terribly clear. There are exact numeric types and approximate numeric types. It technically looks like a decimal could be accepted, but the field would need to be formatted for it.

[blacktip]

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=0xF; ERROR 1232 (42000): Incorrect argument type to variable 'expire_logs_days'

[Grizly]

Well, that answers that then.. perhaps you could get the source and modify it.. waay out of my league atm..

[Grizly]

or just echo "FLUSH LOGS; PURGE BINARY LOGS BEFORE NOW() - INTERVAL 1 HOUR;" | mysql -uroot -p... Can chain multiple queries into pipeline and simply echo, no need for multiple scripts. ;-)