Return to db_backup CVS log

Up to [Local Repository] / badi / public_scripts / db_backup

import from IVT CVS

#!/bin/sh

# db_backup v0.02

# Backup mysql or postgresql db

# db_backup's goal is to dump the data of your databases consistently and
# compress resulting file. It names its backups adding by weekday names. Like
# this, if you backup daily, you get a backup for every day for a period of one week. 
# The files just get overwritten every week because they get the same name again.
# Call db_backup from cron or from parallelstarter (and cron).

# Note: For PostgreSQL there must be a ~/.pgpass file as described under
# http://wiki.postgresql.org/wiki/Pgpass
# If you call this script by parallelstarter, you may use its mechanism to get
# this file created for you automatically out of $DBLIST.


# file to read hostname/db/user/pw from
DBLIST="/etc/db_backup/dbs"

# directory where to store backups
DUMPDIR="/backup/db_backup"

# group to own files
GROUP="root"

# path to pg_dump
PG_DUMP="/Applications/PostgreSQLClient/pgAdmin3.app/Contents/SharedSupport/pg_dump"


# Help message
function usage()  
{
USAGE="
Usage: `basename $0` -h <database host> -D <database name> -u <user name> -c <other config file>

Backup a MySQL databse using mysqldump and a password file. Compress the resulting
file and rotate it weekly. The password is retrieved from a well secured file.

  -h|--host			Specify the host address of your database server.
  -t|--type <mysql|postgresql>	Type of database: either mysql or postgresql
  -D|--database			The name of your database to backup.
  -u|--user			The user name to connect to your database.
  -c|--config			Use another config file. Default points to: /etc/db_backup/dbs

"
echo "$USAGE"
}


# get password from password file
function get_password()
{
	# check config file existence
	if [ ! -e "$DBLIST" ]; then
		echo "Error: Config file $DBLIST not found."
		exit 1  
	fi

	# is my password file save?
	if [ ! $(ls -l "$DBLIST" | grep -ce "^-r[w-]------- \{1,\}1 root \{1,\}\(wheel\|root\) .*$") -eq 1 ]; then
		echo "Error: The file $DBLIST must be protected by 0600 root:root or root:wheel."
		exit 1
	fi
	
	pwnotfound=true
	while read dbinfo ; do
		
		# ignore comment and empty lines
		if [ $(echo "$dbinfo" | egrep -c "^[ \t]*#.*$") -gt 0 ]; then
			continue
		fi
		
		# ignore empty lines or malformed lines
		if [ $(echo "$dbinfo" | sed 's/[^:]//g' | wc -m) -lt 5 ]; then
			continue
		fi
		
		# get current lines's connection information
		curhost="`echo $(echo "$dbinfo" | awk -F ':' '{print $1}')`"
		curdbtype="`echo $(echo "$dbinfo" | awk -F ':' '{print $2}')`"
		curdb="`echo $(echo "$dbinfo" | awk -F ':' '{print $3}')`"
		curuser="`echo $(echo "$dbinfo" | awk -F ':' '{print $4}')`"
		
		# is the current line the searched one?
		if [ "$DBHOST-$DBTYPE-$DB-$DBUSER" = "$curhost-$curdbtype-$curdb-$curuser" ]; then
			# read password
			PW="`echo $(echo "$dbinfo" | awk -F ':' '{print $5}')`"
			pwnotfound=false
			break;
		fi
		
	done < "$DBLIST"
	
	# exit if no password found
	if $pwnotfound; then
		echo "Error: No password found for user $DBUSER on $DBTYPE database $DB for host $DBHOST in file $DBLIST found." 
		exit 1
	fi
}


# get command line arguments
function get_commandlineswitches()
{
	# no args? Help needed aparently.
	if [ -z "$*" ]; then
		usage
		exit 0
	fi

	# read the users' wish
	while [ "$#" -gt 0 ]; do
	
	   case "$1" in
	
	      -h|--host)
	         unset DBHOST
	         shift
	         DBHOST="$1"
                 shift
	         ;;

	      -t|--type)
	         unset DBTYPE
		 shift
		 DBTYPE="$1"
		 shift
		 ;;
		 
	      -D|--database)
	         unset DB
	         shift
	         DB="$1"
                 shift
	         ;;

	      -u|--user)
	         unset DBUSER
	         shift
	         DBUSER="$1"
                 shift
	         ;;

	      -c|--config)
	         unset DBLIST
		 shift
	         DBLIST="$1"
                 shift
	         ;;

	      --help|"-?")
	         usage
	         exit 0
	         ;;

	      *)
	         echo "Invalid argument: $1. See `basename $0` --help for more information." 
		 exit 1
	         ;;
	   esac
	done

	# -h must be given!
	if [ -z "$DBHOST" ]; then
		echo "No argument -h or --host given. This is a mandatory argument."
		exit 1
	fi

	# -D must be given!
	if [ -z "$DB" ]; then
		echo "No argument -D or --database given. This is a mandatory argument."
		exit 1
	fi

	# -u must be given!
	if [ -z "$DBUSER" ]; then
		echo "No argument -u or --user given. This is a mandatory argument."
		exit 1
	fi

	# -t must be given!
	if [ -z "$DBTYPE" ]; then
		echo "No argument -t or --type given. This is a mandatory argument."
		exit 1
	fi
}


#
# MAINMAINMAINMAINMAINMAINMAIN   M A I N   MAINMAINMAINMAINMAINMAINMAIN
#

get_commandlineswitches "$@"

PW=""
get_password

# securely create backupdir, filename
DUMPFILE="$DUMPDIR/$DBHOST/$DB-$(date "+%A" | tr '[A-Z]' '[a-z]').sql.gz"

if [ ! -e "$DUMPDIR/$DBHOST" ]; then
	mkdir -p "$DUMPDIR/$DBHOST"
fi

# remove existing dumpfile
if [ -e "$DUMPFILE" ]; then
	# This must be an old dump
	rm "$DUMPFILE"
fi
touch "$DUMPFILE"
chmod 0660 "$DUMPFILE"
chgrp "$GROUP" "$DUMPFILE"

# remove old error log
if [ -e "$DUMPFILE.err" ]; then
	rm "$DUMPFILE.err"
fi
touch "$DUMPFILE.err"
chmod 0660 "$DUMPFILE.err"
chgrp "$GROUP" "$DUMPFILE.err"

# dump and compress
if [ "$DBTYPE" = "mysql" ]; then
	mysqldump --add-drop-table --create-options --extended-insert --disable-keys \
        	  --force --lock-tables --password="$PW" -u "$DBUSER" -h "$DBHOST" "$DB" 2>>"$DUMPFILE.err" |\
	  	gzip -2 -f 2>>"$DUMPFILE.err" 1>>"$DUMPFILE"
elif [ "$DBTYPE" = "postgresql" ]; then

	"$PG_DUMP" -c -h "$DBHOST" -U "$DBUSER" "$DB" |\
	  	gzip -2 -f 2>>"$DUMPFILE.err" 1>>"$DUMPFILE"
else
	echo "Unsupported database type. This version supports only \"mysql\" or \"postgresql\"."
	exit 1
fi

# exit cleanly
if [ -s "$DUMPFILE.err" ]; then
	echo -e "Error occured. See \"$DUMPFILE.err\" for details."
	exit 10
else
	rm "$DUMPFILE.err"
fi


exit 0