Mailing List Archive


Back to the month index Back to the list index
Kerry Garrison (garrison@delta.net)
Fri, 20 Sep 1996 09:45:22 -0700 

Message-Id: <3.0b19.32.19960920094522.00730c84@mail.deltanet.com>
Date: Fri, 20 Sep 1996 09:45:22 -0700
From: Kerry Garrison <garrison@delta.net>
Subject: Re: [mSQL] about crash recovery

At 04:19 PM 9/20/96 +0100, you wrote:
> Is there someone who knows if there is a built-in crash recovery?
> Or something that can be used as ?
>
> Thanks in advance.
> Bye.

After asking this same question, David Perry
(deperry@hermes.nerosworld.com) sent out this great backup script.

---------------------< cut here > ------------------------

#!/bin/sh
# $Id: msqlbckp v. 1.0 1996/6/22 $
# by: clatham@nerosworld.com
#
# usage: msqlbckp [-h host] 'backup directory'
#
# This script creates daily backups of mSQL databases. Input parameters are
# host machine (if msqld is not running on local machine) and the pathname
# of a directory where the backups will be made. The backups will be named:
# {table name}.{day of week}.gz (They are gzipped).
#
# The structure of the backup files are in a format that can be read
# by the 'msql' program to completely restore the database, by first
# dropping the corrupt table, then recreating the table and populating it
# with data (it is required that the database itself already exists, which
# may require the database administrator to use msqladmin to create the
# database in extreme circumstances).
#
# Access is required to msqldump, relshow and gzip.
#
# Set crontab to execute this script at, say, 4 am every day, every other
# day, or however often you want backups of your databases.
#

err( ) {
        echo usage: msqlbckp [-h host] 'backup directory'
        exit 1
}

case $# in
1)
        bdir=$1
        ;;
3)
        if [ $1 = "-h" ]
        then
                host="-h "$2
        else
                err
        fi
        bdir=$3
        ;;
*)
        err
        ;;
esac

dow=`date '+%A'` # Get the day of the week

# Use relshow to get a list of the available databases, and pare that down
# into a file listing one database name per line...
relshow ${host} | \
        sed -n -e '/^....[ \-].*/d' -e 's/ \| //' -e 's/ *\|//p' \
>${bdir}/db.names

# Get each database name from the file for table processing
dbline=1
while [ 1 ] # Do forever until no more db names
do
        dodb=`cat ${bdir}/db.names | sed -n "${dbline}p"`
        if [ "${dodb}" = "" ] # if no more db names to do
        then
                break
        fi

        cf=${bdir}/${dodb}.${dow} # Define current working file

echo "#
# mSQL Dump of Database: ${dodb}
#
# Begin by dropping all tables
#---------------------------------------------

" > ${cf}

        # Now add commands to 'drop' each table in the database
        relshow ${host} ${dodb} | \
        sed -n -e '/^....[ \-].*/d' -e 's/ \| //' -e 's/ *\|//p' | \
        awk '($0 !~ /^$/) { print "DROP TABLE", $1, "\\g" }' >> ${cf}

        echo " " >> ${cf}

        # Dump the database stucture and data into the backup file
        msqldump ${host} ${dodb} >> ${cf}

        # Finally, gzip the file
        gzip -f ${cf}

        # Next database name
        dbline=`expr ${dbline} + 1`
done

rm ${bdir}/db.names # Get rid of temp file

---------------------< cut here > ------------------------

-Kerry
-----------------------------------------------------------------
Kerry Garrison garrison@delta.net
Delta Design Services http://www.delta-design.com
      Web Site Design, On-line Business Planning, and Training
               (714) 778-0370 (714) 778-1064 FAX
             http://www.deltanet.com/users/garrison/
-----------------------------------------------------------------
--------------------------------------------------------------------------
To remove yourself from the Mini SQL mailing list send a message containing
"unsubscribe" to "unsubscribe" to msql-list-request@bunyip.com. Send a message containing
"info msql-list" to majordomo@bunyip.com for info on monthly archives of
the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!