From owner-freebsd-questions@FreeBSD.ORG Thu Aug 16 15:45:50 2007 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 A4C3216A418 for ; Thu, 16 Aug 2007 15:45:50 +0000 (UTC) (envelope-from youshi10@u.washington.edu) Received: from mxout4.cac.washington.edu (mxout4.cac.washington.edu [140.142.33.19]) by mx1.freebsd.org (Postfix) with ESMTP id 834DC13C480 for ; Thu, 16 Aug 2007 15:45:50 +0000 (UTC) (envelope-from youshi10@u.washington.edu) Received: from smtp.washington.edu (smtp.washington.edu [140.142.33.7] (may be forged)) by mxout4.cac.washington.edu (8.13.7+UW06.06/8.13.7+UW07.06) with ESMTP id l7GFjnYT024695 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Thu, 16 Aug 2007 08:45:50 -0700 X-Auth-Received: from [192.168.10.45] (c-24-10-12-194.hsd1.ca.comcast.net [24.10.12.194]) (authenticated authid=youshi10) by smtp.washington.edu (8.13.7+UW06.06/8.13.7+UW07.03) with ESMTP id l7GFjnRS019519 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Thu, 16 Aug 2007 08:45:49 -0700 Message-ID: <46C47129.4090206@u.washington.edu> Date: Thu, 16 Aug 2007 08:45:45 -0700 From: Garrett Cooper User-Agent: Thunderbird 2.0.0.6 (Windows/20070728) MIME-Version: 1.0 To: Eric Crist References: <1A4196AD-1749-40BC-8F41-3F4E4715096C@gmail.com> In-Reply-To: <1A4196AD-1749-40BC-8F41-3F4E4715096C@gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-PMX-Version: 5.3.3.310218, Antispam-Engine: 2.5.1.298604, Antispam-Data: 2007.8.16.82524 X-Uwash-Spam: Gauge=IIIIIII, Probability=7%, Report='__CT 0, __CTE 0, __CT_TEXT_PLAIN 0, __HAS_MSGID 0, __MIME_TEXT_ONLY 0, __MIME_VERSION 0, __SANE_MSGID 0, __USER_AGENT 0' Cc: Questions User Subject: Re: mysqldump/gzip shell scripting question... 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, 16 Aug 2007 15:45:50 -0000 Eric Crist wrote: > Hey all, > > First off, I don't care if you send example in perl, php, or sh, but > we're not a python shop here, so those recommendation will not be > useful... > > I'm trying to write a shell script that scans our databases for tables > starting with archive_ which are created by other scripts/departments, > etc. This script needs to perform a mysqldump of that table, and then > gzip it. It's MUCH quick to pipe directly to gzip, than perform the > dump, then gzip that. The problem is, this table to filesystem dump > is also going to drop those archive_* tables. We would like to know > that the mysqldump worked before we do this. The problem we're > having, as I'm sure others have run into (at least according to > Google), is that a command such as the following leaves no apparent > easy way to capture the exit status of the mysqldump command: > > # mysqldump -u $USER -p$PASS $DBHOST $DATABASE $TABLE | gzip > > $TABLE.sql.gz > > Anyone have any good recommendations? > > Thanks! > > Eric Crist perldoc DBI if you want to access the database info directly from Perl (as opposed to mysqldump). Honestly, you're going to have to dig through some information in the API, and fish out the MySQL interfaces, but Perl or some other structured query API is probably a decent bet for what you want to do (unless you have a lot of data, in which I suggest using C equivalent methods or maybe Python if you want to stick with a scripting language), because it provides you with information and return statuses that straight mysqldump may not provide. Plus with Perl (at least) you could pipe file reading through an alternate method to ensure that things passed by searching the output for particular keys, etc. PHP also supports DB access methods though. Bourne/tcsh shell equivalent solutions would be kludgy and ill built for what you're trying to accomplish IMO. Cheers, -Garrett