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
Description: OpenPGP digital signature