From owner-freebsd-questions@FreeBSD.ORG Wed Oct 1 01:37:53 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 9FEAE1065696 for ; Wed, 1 Oct 2008 01:37:53 +0000 (UTC) (envelope-from pschmehl_lists@tx.rr.com) Received: from cdptpa-omtalb.mail.rr.com (cdptpa-omtalb.mail.rr.com [75.180.132.120]) by mx1.freebsd.org (Postfix) with ESMTP id 654CA8FC0A for ; Wed, 1 Oct 2008 01:37:53 +0000 (UTC) (envelope-from pschmehl_lists@tx.rr.com) Received: from [192.168.2.102] (really [24.175.90.48]) by cdptpa-omta06.mail.rr.com with ESMTP id <20081001013751.LZRG28641.cdptpa-omta06.mail.rr.com@[192.168.2.102]>; Wed, 1 Oct 2008 01:37:51 +0000 Date: Tue, 30 Sep 2008 20:37:46 -0500 From: Paul Schmehl To: John Almberg , freebsd-questions@freebsd.org Message-ID: In-Reply-To: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> References: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> X-Mailer: Mulberry/4.0.8 (Mac OS X) X-Munged-Reply-To: To reply - figure it out MIME-Version: 1.0 Content-Type: multipart/signed; micalg=sha1; protocol="application/pkcs7-signature"; boundary="==========15EE95FEBB9E7470EDD2==========" X-Content-Filtered-By: Mailman/MimeDel 2.1.5 Cc: Subject: Re: Best way to back up mysql database X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list Reply-To: Paul Schmehl List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 01 Oct 2008 01:37:53 -0000 --==========15EE95FEBB9E7470EDD2========== Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline --On September 30, 2008 6:18:35 PM -0400 John Almberg=20 wrote: > First, I wanted to say how great this list is. I'm a newbie FreeBSD > admin and, besides the Handbook and "Absolute FreeBSD" (which never > seems to leave my desk), this list is the best resource I have. > > I just had a huge scare today... One of the websites on my server uses a > large Mysql database. Somehow, one of the tables got corrupted today. > > I have been blithely backing up mysql with a simple cron script that ran > mysqldump every night. Simple, reliable, and I've never needed it. > > Today, when I realized the database was corrupted, I scrambled for my > backup, and realized that if I hadn't caught the problem today, tomorrow > my backup would have been overwritten, and I would have been... well, > not a happy camper. > > Again, I have run into a problem which is stupidly obvious to > experienced admins, I'm sure. I want to slap myself, but don't have > time. I'll do that after I have a better backup system in place. > > I am just about to dive into Google in search of a solution, but thought > I would fire off a quick request, in case there is an obvious solution > that everyone uses. If there is, a name or URL will do. I'll figure out > the rest. > > Any hints much appreciated. Not going home until this is fixed... Found this on the mysql documentation site: #!/bin/sh date=3D`date -I` mysqldump --opt --all-databases | bzip2 -c > /var/backup/databasebackup-$date.sql.bz2 The date must be something from linux, but you can do it like this in = FSBD: #!/bin/sh date=3D`date "+%Y-%m-%d.%H:%M:%S"` mysqldump --opt --all-databases | bzip2 -c > /var/backup/databasebackup-$date.sql.bz2 Using this makes every dump uniquely named, even if you run several a day, = so you would need to edit newsyslog.conf to rotate the dumps after a=20 number of dumps that you choose so you don't keep writing dumps until the=20 hard drive is full. Paul Schmehl, If it isn't already obvious, my opinions are my own and not those of my employer. ****************************************** WARNING: Check the headers before replying --==========15EE95FEBB9E7470EDD2==========--