MySQL backup script on steroids

database-backup.sh

#!/bin/bash

CMD_MYSQL="/usr/bin/mysql"
CMD_MYSQLDUMP="/usr/bin/mysqldump"
CMD_GZIP="gzip"
CMD_MKDIR="/bin/mkdir"
CMD_TAR="/bin/tar"
CMD_RM="/bin/rm"
CMD_S3='/root/s3cmd/s3cmd --access_key=XXXXXXXXXXXX --secret_key=YYYYYYYYYYYYY put --reduced-redundancy' # s3cmd with some options

STORAGE_FODER="s3://" # the destination bucket
MYSQL_USER="" # mysql user with enough grants to read whatever you want to backup
MYSQL_PASS="" # mysql password
MYSQL_HOST="localhost" # mysql host

BACKUP_SERVER=`hostname`
BACKUP_DIR="/var/backup/database"
BACKUP_CONTENT="/tmp/dumps"
BACKUP_NAME=`LC_ALL=en_US.utf8 /bin/date +%A | tr "[:upper:]" "[:lower:]"`

function _log {
	echo -n `date`
	echo " - $1"
}

function _dump  {
	_log "dumping $1.$2"
	STORAGE_DIR="$3/$1"
	$CMD_MKDIR -p $STORAGE_DIR
	$CMD_MYSQLDUMP -h $MYSQL_HOST -u $MYSQL_USER --password=$MYSQL_PASS $1 $2 | $CMD_GZIP > $STORAGE_DIR/$2.sql.gz
}


_log ":: starting backup proccess"

_log "removing old dumps"
$CMD_RM -r $BACKUP_CONTENT/*


_log "starting backup"
MYSQL_DBS=`$CMD_MYSQL -h $MYSQL_HOST -u $MYSQL_USER --password=$MYSQL_PASS -B -N -e "show databases"`


for database in $MYSQL_DBS; do

	_log "exporting \"$database\" database"

	TABLES=`$CMD_MYSQL -h $MYSQL_HOST -u $MYSQL_USER --password=$MYSQL_PASS -B -N -e "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='$database'"`

	for table in $TABLES; do
		_dump $database $table $BACKUP_CONTENT
	done
done

_log "packaging backup"
$CMD_TAR -zcf $BACKUP_DIR/$BACKUP_SERVER.tar.gz $BACKUP_CONTENT

_log "uploading to S3"
$CMD_S3 put "$BACKUP_DIR/$BACKUP_SERVER.tar.gz" "$STORAGE_FODER/$BACKUP_NAME.tar.gz"


_log ":: backup proccess finished"