From owner-freebsd-questions@FreeBSD.ORG Fri May 2 06:09:22 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 1A1D01065673 for ; Fri, 2 May 2008 06:09:22 +0000 (UTC) (envelope-from m.seaman@infracaninophile.co.uk) Received: from smtp.infracaninophile.co.uk (gate6.infracaninophile.co.uk [IPv6:2001:8b0:151:1::1]) by mx1.freebsd.org (Postfix) with ESMTP id 626748FC15 for ; Fri, 2 May 2008 06:09:21 +0000 (UTC) (envelope-from m.seaman@infracaninophile.co.uk) Received: from happy-idiot-talk.infracaninophile.co.uk (localhost [IPv6:::1]) (authenticated bits=0) by smtp.infracaninophile.co.uk (8.14.2/8.14.2) with ESMTP id m42696ao008832; Fri, 2 May 2008 07:09:14 +0100 (BST) (envelope-from m.seaman@infracaninophile.co.uk) X-DKIM: Sendmail DKIM Filter v2.5.5 smtp.infracaninophile.co.uk m42696ao008832 DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=infracaninophile.co.uk; s=200708; t=1209708554; bh=KM9uXloA+kffw2 qDKjEQQf1iZNmOiIWSlBhWgSHsGkI=; h=Message-ID:Date:From:MIME-Version: To:CC:Subject:References:In-Reply-To:Content-Type:Cc:Content-Type: Date:From:In-Reply-To:Message-ID:Mime-Version:References:To; z=Mes sage-ID:=20<481AAFFC.80101@infracaninophile.co.uk>|Date:=20Fri,=200 2=20May=202008=2007:09:00=20+0100|From:=20Matthew=20Seaman=20|Organization:=20Infracaninophile|User-A gent:=20Thunderbird=202.0.0.12=20(X11/20080427)|MIME-Version:=201.0 |To:=20Mel=20|CC:=20freebsd-qu estions@freebsd.org,=20John=20,=20=0D=0A=20Paul=2 0Schmehl=20|Subject:=20Re:=20Recovering=20mysql =20data=20-=20mysqlbinlog|References:=20<200805011913.DSZ93243@jmu. edu>=09<200805012152.06354.fbsd.questions@rachie.is-a-geek.net>=09< B7797CB8B91FC30D58FEBAC9@utd65257.utdallas.edu>=20<200805012255.456 00.fbsd.questions@rachie.is-a-geek.net>|In-Reply-To:=20<20080501225 5.45600.fbsd.questions@rachie.is-a-geek.net>|X-Enigmail-Version:=20 0.95.6|Content-Type:=20multipart/signed=3B=20micalg=3Dpgp-sha256=3B =0D=0A=20protocol=3D"application/pgp-signature"=3B=0D=0A=20boundary =3D"------------enig969BD8896FBD52C751B888BA"; b=dN2em8UG/5vM6DJlHO ZxBCD11j6dJPVsVuwgGoNOC6H8+EylzfKp1/+RX7IN3ueDFKdvQ5O5KGyXH8qkbdRC+ Hlu+QKGDJj0fy3oKpNmraMAdw82an9oV2aurJ+fWChuLReqw/rLR+sA2F6U03c8Iu6q PgV4QEXe9f405UIfJV0= Message-ID: <481AAFFC.80101@infracaninophile.co.uk> Date: Fri, 02 May 2008 07:09:00 +0100 From: Matthew Seaman Organization: Infracaninophile User-Agent: Thunderbird 2.0.0.12 (X11/20080427) MIME-Version: 1.0 To: Mel References: <200805011913.DSZ93243@jmu.edu> <200805012152.06354.fbsd.questions@rachie.is-a-geek.net> <200805012255.45600.fbsd.questions@rachie.is-a-geek.net> In-Reply-To: <200805012255.45600.fbsd.questions@rachie.is-a-geek.net> X-Enigmail-Version: 0.95.6 Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------enig969BD8896FBD52C751B888BA" X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (smtp.infracaninophile.co.uk [IPv6:::1]); Fri, 02 May 2008 07:09:14 +0100 (BST) X-Virus-Scanned: ClamAV version 0.93, clamav-milter version 0.93 on happy-idiot-talk.infracaninophile.co.uk X-Virus-Status: Clean X-Spam-Status: No, score=-3.0 required=5.0 tests=AWL,BAYES_00,DKIM_SIGNED, DKIM_VERIFIED,NO_RELAYS autolearn=ham version=3.2.4 X-Spam-Checker-Version: SpamAssassin 3.2.4 (2008-01-01) on happy-idiot-talk.infracaninophile.co.uk Cc: John , Paul Schmehl , freebsd-questions@freebsd.org 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: Fri, 02 May 2008 06:09:22 -0000 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig969BD8896FBD52C751B888BA Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Mel wrote: > 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 po= int >>>> in time recovery and replication. I'll be adding a my.cnf file (usi= ng >>>> 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 t= he >>>> 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 delete= d >>> 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 ful= l >>> /var partition. >> That can be alleviated by adding the logs to newsyslog.conf and gzippi= ng >> 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 cou= rse, >> use your own hostname and fully qualified hostname. Ummm... actually this is not a particularly good idea. MySQL keeps an in= ternal 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 v= ery little return, especially on a busy server. There's really no point in k= eeping 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 t= hen (as=20 > in, getting hostname from your dhcp server) ;) > Actually, I think you can use a fixed name, but it's been a while since= I=20 > looked at the bin-log related variables. >=20 > However if you're using the bin-log, to recover accidental deletes or=20 > replications, then you need to use the mysql provided SQL commands for = it.=20 > It's scriptable (periodic/crontab), but not for use in newsyslog. >=20 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 labe= led by the hostname of the master server. Cheers, Matthew --=20 Dr Matthew J Seaman MA, D.Phil. 7 Priory Courtyard Flat 3 PGP: http://www.infracaninophile.co.uk/pgpkey Ramsgate Kent, CT11 9PW --------------enig969BD8896FBD52C751B888BA Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.9 (FreeBSD) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEAREIAAYFAkgasAIACgkQ8Mjk52CukIwnRQCfbQ0gmWPD8zmF+viqhfDa5kJ7 gPUAnjFSZgJfgnumZFy7Ye7tvxjvkOFD =h5R+ -----END PGP SIGNATURE----- --------------enig969BD8896FBD52C751B888BA--