From owner-freebsd-questions@FreeBSD.ORG Wed Oct 1 00:04:18 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 B00D01065691 for ; Wed, 1 Oct 2008 00:04:18 +0000 (UTC) (envelope-from fcondo@quinn.com) Received: from sushi.quinn.com (sushi.quinn.com [216.27.181.95]) by mx1.freebsd.org (Postfix) with ESMTP id 8DDC08FC14 for ; Wed, 1 Oct 2008 00:04:18 +0000 (UTC) (envelope-from fcondo@quinn.com) Received: from [192.168.1.100] (cpe-76-170-216-193.socal.res.rr.com [76.170.216.193]) (authenticated bits=0) by sushi.quinn.com (8.14.2/8.14.2) with ESMTP id m8UNcWPw013462 (version=TLSv1/SSLv3 cipher=AES128-SHA bits=128 verify=NO); Tue, 30 Sep 2008 16:38:35 -0700 (PDT) (envelope-from fcondo@quinn.com) Message-Id: <55B7D764-CCC4-458C-8D68-5D2509EAFFC9@quinn.com> From: Fred Condo To: John Almberg In-Reply-To: Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (Apple Message framework v929.2) Date: Tue, 30 Sep 2008 16:38:32 -0700 References: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> <7F7FCA1B-E46C-4A44-9569-D6638A550FCE@hughes.net> X-Mailer: Apple Mail (2.929.2) 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: Wed, 01 Oct 2008 00:04:18 -0000 I run a script from root's crontab (not /etc/crontab) and keep the login credentials in /root/.my.cnf so they don't have to be embedded in the script. Not that $gzip is defined as /bin/cat because I move copies offsite via rsync and disk space is abundant. This script keeps 30 daily backups (configurable). Crontab entry: 13 20 * * * cd /bak/databases && /root/db_backup "db_backup" perl script: #! /usr/bin/perl use strict; my $maxbackups = 30; my $gz='gz'; my $mysqldump = '/usr/local/bin/mysqldump'; my $gzip = '/bin/cat'; my $newfile; my $filename = 'all_databases.sql'; my $curfile = $filename . ".$maxbackups"; unlink $curfile if -f $curfile; my ($i, $j); for ($i = $maxbackups - 2; $i >= 0; $i--) { $j = $i + 1; $curfile = $filename . '.' . $i; $newfile = $filename . '.' . $j; rename $curfile, $newfile if -f $curfile; } $curfile = $filename . '.' . '0'; my $command = "$mysqldump --opt --all-databases | $gzip > $curfile"; my $result; $result = system $command and warn "$result"; On Sep 30, 2008, at 4:22 PM, John Almberg wrote: >> >> 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 > > Ah, a much simpler solution than my ruby script. I hadn't thought to > zip up the file before transferring it. That's an improvement I must > add. > > Thanks: John > _______________________________________________ > freebsd-questions@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-questions > To unsubscribe, send any mail to "freebsd-questions-unsubscribe@freebsd.org > " >