Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
2  
I think you chose the best solution. –  lg. Sep 9 '10 at 8:04

3 Answers 3

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)
share|improve this answer

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

share|improve this answer
    
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 Sep 9 '10 at 2:33
    
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' –  blacktip Sep 9 '10 at 2:34
    
Well, that answers that then.. perhaps you could get the source and modify it.. waay out of my league atm.. –  Grizly Sep 9 '10 at 8:50

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

share|improve this answer
    
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. ;-) –  Grizly Mar 12 '13 at 22:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.