Mel wrote:
On Thursday 01 May 2008 22:24:33 Paul Schmehl wrote:
--On Thursday, May 01, 2008 21:52:05 +0200 Mel

<[EMAIL PROTECTED]> 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

Attachment: signature.asc
Description: OpenPGP digital signature

Reply via email to