backup-mysqldump

#!/bin/bash

MYSQL_USER=
MYSQL_PASSWORD=
MYSQL_HOST=
MYSQL_PORT=3306
COMPRESSION_METHOD=
RSH=
SPLIT_FILES=

# The leading ":" suppresses error messages from
while getopts ":u:p:h:P:d:c:e:x" opt; do
  case $opt in
    d)
      BACKUP_FILE=$OPTARG
      ;;
    u)
      MYSQL_USER=$OPTARG
      ;;
    p)
      MYSQL_PASSWORD=$OPTARG
      ;;
    P)
      MYSQL_PORT=$OPTARG
      ;;
    h)
      MYSQL_HOST=$OPTARG
      ;;
    c)
      COMPRESSION_METHOD=$OPTARG
      ;;
    e)
      RSH=$OPTARG
      ;;
    x)
      SPLIT_FILES=1
      ;;
    \?)
      echo "Invalid option: -$OPTARG" >&2
      exit 1
      ;;
    :)
      echo "Option -$OPTARG requires an argument." >&2
      exit 1
      ;;
  esac
done

if [ -z "$BACKUP_FILE" ]; then
	BACKUP_FILE=/var/backups/$(date +"%Y-%m-%d-%H%M%S")_${MYSQL_HOST}_mysqldump
fi

if [ -z "$MYSQL_USER" ] || [ -z "$MYSQL_PASSWORD" ]; then
	echo
	echo Usage: $0 -u mysqluser -p mysqlpassword -h mysqlhost -c bzip2
	echo
	echo "  -u  Specifies the MySQL user (required)"
	echo "  -p  Specifies the MySQL password (required)"
	echo "  -h  Specifies the MySQL host (required)"
	echo "  -P  Specifies the MySQL port (optional)"
	echo "  -d  Specifies the backup file where to put the backup (default: /var/backups/CURRENT_DATETIME_MYSQLHOST_mysqldump)"
	echo "  -c  Specifies the compression method which should be used to compress the dump file (bzip2 or gzip)"
	echo "  -e  Specified the remote shell which should be used (e.g. \"ssh -C user@remotehost\")"
	echo "  -x  If specified the backup will create a seperate file for each database/table"
	echo
	exit 1
fi

echo Using the following configuration:
echo
echo "    backup_file:        ${BACKUP_FILE}"
echo "    mysql_user:         ${MYSQL_USER}"
echo "    mysql_password:     ****** (not shown)"
echo "    mysql_host:         ${MYSQL_HOST}"
echo "    mysql_port:         ${MYSQL_PORT}"
echo "    compression_method: ${COMPRESSION_METHOD}"
echo "    rsh:                ${RSH}"
echo

MYSQL_OPTS="-u${MYSQL_USER} -p${MYSQL_PASSWORD} -h${MYSQL_HOST} -P${MYSQL_PORT}"
MYSQLDUMP_OPTS="--single-transaction --routines --triggers"

if [ -n "$SPLIT_FILES" ]; then
	for db in $(${RSH} mysql ${MYSQL_OPTS} -e show\ databases -s --skip-column-names | grep -v information_schema | grep -v performance_schema); do
		for table in $(${RSH} mysql ${MYSQL_OPTS} $db -e show\ tables -s --skip-column-names); do
			echo "Dumping ${db}.${table}"
			
			echo "SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;" >${BACKUP_FILE}_${db}.${table} 
			${RSH} mysqldump ${MYSQL_OPTS} ${MYSQLDUMP_OPTS} ${db} ${table} >>${BACKUP_FILE}_${db}.${table} 
			echo "SET autocommit=1;SET unique_checks=1;SET foreign_key_checks=1;commit;" >>${BACKUP_FILE}_${db}.${table} 
		
			RETVAL=$?
		done
	done
else
	${RSH} mysqldump ${MYSQL_OPTS} ${MYSQLDUMP_OPTS} --all-databases >${BACKUP_FILE}
	RETVAL=$?
fi

# compression step
if [ "$RETVAL" == 0 ]; then
	if [ "$COMPRESSION_METHOD" = "bzip2" ]; then
		echo Compressing backup using bzip2 compression method.
		bzip2 --best ${BACKUP_FILE}*
		RETVAL=$?
	fi
	
	if [ "$COMPRESSION_METHOD" = "gzip" ]; then
		echo Compressing backup using gzip compression method.
		gzip --best ${BACKUP_FILE}*
		RETVAL=$?
	fi
fi

if [ "$RETVAL" == 0 ]; then
	echo Backup finished successfully.
	exit 0 
else
	echo Backup failed with errors!
	exit 1
fi