handling mysql binlog data

[Chris Pratt]

On May 2, 2008, at 8:58 AM, Zbigniew Szalbot wrote:

>
>> The procedure for this is here:
>> http://dev.mysql.com/doc/refman/5.1/en/purge-master-logs.html
>> for 5.1 and here:
>> http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html
>> for 5.0
>
> Thanks a lot! It did help me get rid of a few files in a safe way.  
> I only wonder now how to set up a cron job to do it on a permanent  
> basis?
>
> 1/ I would probably be better off setting this mysql query (URGE  
> {MASTER | BINARY} LOGS BEFORE 'date') in a separate file an run it  
> from cron?
>
> 2/ Even if I do that, I would still have to change the 'date' value  
> each month... hmm... as non-programmer I will probably do best to  
> simply enter a task in a calendar to run this manually... each  
> month :)
>
> Thank you anyway - this was very helpful and I instantly saved a  
> lot of space on a shrinking /var partition!
>

I find it most comfortable to do this manually so I can check
my backups first. There is an example in the reply comments
below the documentation on the 5.0 version of the mysql
doc page that shows a "unix" way to set up a cron script
and automate the process. I've not tried it.

Shrinking /var partition?: I found the ports setup of mysql to
be overly restrictive by using the /var method. It was simple
to install, shutdown mysqld, copy the contents of the /var
database files (preserving the appropriate ownership and
permissions). I then added (assuming /usr is your large
partition)

mysql_dbdir="/usr/mysql"
mysql_datadir="/usr/mysql"

to /etc/rc.conf and restarted. It is an outage but it helped given
I'd never have thought to size /var anywhere near what a
medium size database required.