Recovering mysql data - mysqlbinlog

[Matthew Seaman]

Mel wrote:
> On Thursday 01 May 2008 22:24:33 Paul Schmehl wrote:
>> --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.

Ummm... actually this is not a particularly good idea.  MySQL keeps an internal
list of all the binlogs it has available, and doing this will break that.  The
best method is to cron a script that will run eg.

    PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);

for whatever interval suits you.  binlogs can absorb a lot of space for very
little return, especially on a busy server.  There's really no point in keeping
binlogs from before the earliest full database dump in your backup cycle,
and even keeping that many is probably a little OTT for most purposes.

You are regularly dumping the database aren't you?

> Yes, in this particular case it can, not changing hostnames is a plus then (as 
> in, getting hostname from your dhcp server) ;)
> Actually, I think you can use a fixed name, but it's been a while since I 
> looked at the bin-log related variables.
> 
> However if you're using the bin-log, to recover accidental deletes or 
> replications, then you need to use the mysql provided SQL commands for it. 
> It's scriptable (periodic/crontab), but not for use in newsyslog.
> 

MySQL recommends that you use a fixed name for binary logs nowadays.
mysql-bin.NNNNNNN typically.  The reasoning seems to be that it makes it
easier to deal with replication -- although relay logs are all still labeled
by the hostname of the master server.

	Cheers,

	Matthew

-- 
Dr Matthew J Seaman MA, D.Phil.                   7 Priory Courtyard
                                                  Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey     Ramsgate
                                                  Kent, CT11 9PW

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 258 bytes
Desc: OpenPGP digital signature
Url : http://lists.freebsd.org/pipermail/freebsd-questions/attachments/20080502/9bb90743/signature.pgp