Recovering mysql data - mysqlbinlog

[Paul Schmehl]

--On Thursday, May 01, 2008 21:52:05 +0200 Mel 
<fbsd.questions at rachie.is-a-geek.net> wrote:

> On Thursday 01 May 2008 21:13:41 John wrote:
>> Thank you Mel and Paul for the suggestions.  From what I understand the
>> general query log is more for debugging and the binary log is for point in
>> time recovery and replication.  I'll be adding a my.cnf file (using the
>> my-large.cnf as a skeleton) soon.  I'm glad the issue was caught earlier on
>> and now I'm the wiser thanks to you guys.  I wonder why the default is no.
>> I can't think of anyone who wouldn't find the binary logging beneficial.
>
> I can think of a reason for FreeBSD. The binary logs are never deleted and
> upon every server restart a new one is created. If you're like me, developing
> on a laptop with a webenvironment including 'Mysql server', shutting down
> your laptop daily, you quickly find yourself having full /var partition.
>

That can be alleviated by adding the logs to newsyslog.conf and gzipping and 
rotating them regularly.
If you don't restart mysql much, something like this would work:

/var/db/mysql/[hostname]-bin.*    mysql:mysql  660  7     *  $W6D0     JBG 
/var/db/mysql/[FQHN].pid

If you're restarting it daily, something like this should work:

/var/db/mysql/[hostname]-bin.*    mysql:mysql 660 25     * $D0  JBG 
/var/db/mysql/[FQHN].pid

Adjust the counts and the rotation schedule to your liking and, of course, use 
your own hostname and fully qualified hostname.

-- 
Paul Schmehl (pauls at utdallas.edu)
Senior Information Security Analyst
The University of Texas at Dallas
http://www.utdallas.edu/ir/security/