From owner-freebsd-questions@FreeBSD.ORG Thu May 1 20:55:48 2008 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 848A0106566B for ; Thu, 1 May 2008 20:55:48 +0000 (UTC) (envelope-from fbsd.questions@rachie.is-a-geek.net) Received: from snoogles.rachie.is-a-geek.net (rachie.is-a-geek.net [66.230.99.27]) by mx1.freebsd.org (Postfix) with ESMTP id 4CD838FC0C for ; Thu, 1 May 2008 20:55:48 +0000 (UTC) (envelope-from fbsd.questions@rachie.is-a-geek.net) Received: from localhost (localhost [127.0.0.1]) by snoogles.rachie.is-a-geek.net (Postfix) with ESMTP id D6AC81CC91; Thu, 1 May 2008 12:55:46 -0800 (AKDT) From: Mel To: freebsd-questions@freebsd.org Date: Thu, 1 May 2008 22:55:42 +0200 User-Agent: KMail/1.9.7 References: <200805011913.DSZ93243@jmu.edu> <200805012152.06354.fbsd.questions@rachie.is-a-geek.net> In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200805012255.45600.fbsd.questions@rachie.is-a-geek.net> Cc: John , Paul Schmehl Subject: Re: Recovering mysql data - mysqlbinlog X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 01 May 2008 20:55:48 -0000 On Thursday 01 May 2008 22:24:33 Paul Schmehl wrote: > --On Thursday, May 01, 2008 21:52:05 +0200 Mel > > 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. 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. -- Mel Problem with today's modular software: they start with the modules and never get to the software part.