mysqldump/gzip shell scripting question...

[Garrett Cooper]

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