Return to db_backup CVS log

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

- new default path for backup files
- infix dash added

#!/bin/bash

# db_backup v0.0.6

# 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="/var/local/lib/db_backup"

# group to own files
GROUP="root"
#GROUP="wheel"

# path to pg_dump
PG_DUMP="$(which pg_dump)"
#PG_DUMP="/Applications/PostgreSQLClient/pgAdmin3.app/Contents/SharedSupport/pg_dump"

# path to mysqldump
MYSQL_DUMP="$(which mysqldump)"
#MYSQL_DUMP="/opt/local/bin/mysqldump5"


# Help message
function usage()
{
USAGE="
Usage: `basename $0` -h <database host> [-p <port>] -D <database name> -u <user name> [-c <other config file>] [-f <SQL|XML>] [--backup-file-infix <infix>]

Backup a MySQL or PostgreSQL databse using mysqldump resp. pg_dump 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.
  -p|--port			Port to connect to.
  -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
  -f|--format			Specify output format. Defaults to SQL.
  --backup-file-infix		Add a supplement to the default backup file name.

"
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}')`"
		
		curport="$(echo "$curhost" | sed -e "s/.*:\([0-9]\{1,\}\) *$/\1/")"
		if [ "$curhost" != "$curport" ]; then
			# remove port information from host string
			curhost="$(echo "$curhost" | sed -e "s/\(.*\):[0-9]\{1,\} *$/\1/")"
		else
			curport="$(getdefault_dbport)"
		fi

		# is the current line the searched one?
		if [ "$DBHOST-$DBPORT-$DBTYPE-$DB-$DBUSER" = "$curhost-$curport-$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." 
		exit 1
	fi
}


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

	BKPFILE_INFIX=""

	# read the users' wish
	while [ "$#" -gt 0 ]; do

	   case "$1" in

	      -h|--host)
			unset DBHOST
			shift
			DBHOST="$1"
			shift
			;;

	      -p|--port)
			unset DBPORT
			shift
			DBPORT="$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
			;;
 	      
 	      -f|--format)
			unset FORMAT
			shift
			FORMAT="$1"
			shift
			;;

	      --backup-file-infix)
			unset BKPFILE_INFIX
			shift
			BKPFILE_INFIX="$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

	# -p has defaults
	elif [ -z "$DBPORT" ]; then
		DBPORT="$(getdefault_dbport)"
	fi

	# -f has defaults
 	if [ -z "$FORMAT" ]; then
 		FORMAT="SQL"
 	fi
 	if [ "$DBTYPE" != "mysql" -a "$FORMAT" != "SQL" ]; then
 		echo "For $DBTYPE only the format SQL is supported."
 		exit 1
 	fi
 	case "$FORMAT" in

 		SQL)
 		   unset FORMAT_SW
 		   ;;

 		XML)
 		   FORMAT_SW="--xml"
 		   ;;

 		*)
 		   echo "Format $FORMAT is unsupported."
 		   exit 1
 		   ;;
 	esac
}


# the default ports
function getdefault_dbport {
	if [ "$DBTYPE" = "mysql" ]; then
		echo -n "3306"
	elif [ "$DBTYPE" = "postgresql" ]; then
		echo -n "5432"
	fi
}


#
# MAINMAINMAINMAINMAINMAINMAIN   M A I N   MAINMAINMAINMAINMAINMAINMAIN
#

get_commandlineswitches "$@"

PW=""
get_password

# securely create backupdir, filename
if [ -n "$BKPFILE_INFIX" ]; then
        BKPFILE_INFIX="-${BKPFILE_INFIX}"
fi
DUMPFILE="$DUMPDIR/$DBHOST/${DB}-$(date "+%A" | tr '[A-Z]' '[a-z]')${BKPFILE_INFIX}.$(echo "$FORMAT" | tr '[A-Z]' '[a-z]').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
logger -t "$(basename "$0")[$$]" "Start dumping $DBTYPE DB \"$DB\" to file $DUMPFILE ..."
if [ "$DBTYPE" = "mysql" ]; then
	if [ -n "$PW" ]; then
		PW="--password=$PW"
	else
		PW=""
	fi
	"$MYSQL_DUMP" $FORMAT_SW --add-drop-table --create-options --extended-insert --disable-keys \
        	  --force --lock-tables -u "$DBUSER" $PW -P "$DBPORT" -h "$DBHOST" "$DB" 2>>"$DUMPFILE.err" |\
	  	gzip -2 -f 2>>"$DUMPFILE.err" 1>>"$DUMPFILE"
elif [ "$DBTYPE" = "postgresql" ]; then

	"$PG_DUMP" -c -h "$DBHOST" -p "$DBPORT" -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 "An error occured: $(cat "$DUMPFILE.err")"
	logger -t "$(basename "$0")[$$]" "... failed: $(cat "$DUMPFILE.err")"
	exit 10
else
	rm "$DUMPFILE.err"
	logger -t "$(basename "$0")[$$]" "... DB \"$DB\" dumped to $DUMPFILE."
fi


exit 0