From owner-freebsd-questions@FreeBSD.ORG Tue Sep 30 23:12:47 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 D3614106568E for ; Tue, 30 Sep 2008 23:12:47 +0000 (UTC) (envelope-from eagletree@hughes.net) Received: from smtprelay.b.hostedemail.com (smtprelay0126.b.hostedemail.com [64.98.42.126]) by mx1.freebsd.org (Postfix) with ESMTP id A366E8FC15 for ; Tue, 30 Sep 2008 23:12:47 +0000 (UTC) (envelope-from eagletree@hughes.net) Received: from filter.hostedemail.com (b-bigip1 [10.5.19.254]) by smtprelay01.b.hostedemail.com (Postfix) with SMTP id 16438176AA; Tue, 30 Sep 2008 23:12:46 +0000 (UTC) X-SpamScore: 1 X-Spam-Summary: 2, 0, 0, f713603100c568a3, 3b98415b86034787, eagletree@hughes.net, , RULES_HIT:355:379:541:564:599:601:945:988:989:1260:1261:1277:1311:1313:1314:1345:1359:1437:1515:1516:1518:1534:1541:1593:1594:1711:1730:1747:1766:1792:2196:2199:2393:2559:2562:2693:3027:3353:3653:3865:3866:3867:3868:3869:3870:3871:3873:3874:4470:5007:7652:8700, 0, RBL:none, CacheIP:none, Bayesian:0.5, 0.5, 0.5, Netcheck:none, DomainCache:0, MSF:not bulk, SPF:, MSBL:none, DNSBL:none Received: from [192.168.0.3] (dpc6744118153.direcpc.com [67.44.118.153]) (Authenticated sender: eagletree@hughes.net) by omf11.b.hostedemail.com (Postfix) with ESMTP; Tue, 30 Sep 2008 23:12:39 +0000 (UTC) In-Reply-To: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> References: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> Mime-Version: 1.0 (Apple Message framework v753) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <7F7FCA1B-E46C-4A44-9569-D6638A550FCE@hughes.net> Content-Transfer-Encoding: 7bit From: Chris Pratt Date: Tue, 30 Sep 2008 16:12:35 -0700 To: John Almberg X-Mailer: Apple Mail (2.753) X-session-marker: 6561676C6574726565406875676865732E6E6574 Cc: freebsd-questions@freebsd.org Subject: Re: Best way to back up mysql database 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: Tue, 30 Sep 2008 23:12:47 -0000 > > 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... > > Most certainly would want you to not not go home having been there before. Here is a crude way to do this. Find an elegant solution at leisure. The downside is that you if you crash at the wrong time, your job won't start for the next day. Be forewarned, then you stop making backups. You just need to monitor your atq. The gzip step should probably be part of a pipe for efficiency. You could cron this to get around that. I saw the response about repairing corruptions, REPAIR TABLE has thus far kept me from ever reloading. See man on date and use something other than %a to generate a numeric date unique back, that would give you numerous backups if you have the storage. DATE=`date +%a` # echo $DATE # echo Backup Mysql database mysqldump -h localhost -u YOURSQLUSERID -pYOURPASSWORD YOURDATABASE >/ usr/somedirectory/somefile_$DATE.backup gzip -f /usr/somedirectory/somefile_$DATE.backup /usr/bin/at -f /usr/somedirectory/mysqlbackup.sh midnight