Backup mysql db both as a whole and with single tables

backup.sh

#!/bin/sh

BACKUP_BASE_DIR=/var/www/backups/db/
MYSQL_USER=""
MYSQL_PWD=""
MYSQL_DB=""
MYSQL_HOST="localhost"


TAR="$(which tar)"
GZIP="$(which gzip)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
NOW=$(date +"%Y-%m-%d-%H-%M-%S")
BACKUPDIR=$BACKUP_BASE_DIR/$MYSQL_DB/$NOW

mkdir -p $BACKUPDIR

# dump whole db into one file
$MYSQLDUMP --add-drop-table --allow-keywords -q --single-transaction -c -u $MYSQL_USER -h $MYSQL_HOST -p$MYSQL_PWD $MYSQL_DB | $GZIP > $BACKUPDIR.sql.gz

# additionally dump every table into separate files
for db_table in `echo "show tables" | $MYSQL -u $MYSQL_USER -h $MYSQL_HOST -p$MYSQL_PWD $MYSQL_DB|grep -v Tables_in_`;
do
FILE=$BACKUPDIR/$db_table.sql.gz
echo $db_table; $MYSQLDUMP --add-drop-table --allow-keywords -q --single-transaction -c -u $MYSQL_USER -h $MYSQL_HOST -p$MYSQL_PWD $MYSQL_DB $db_table | $GZIP > $FILE
done

# delete everything older than 30 days
find $BACKUP_BASE_DIR/$MYSQL_DB/ -mtime +30 -exec rm -fr {} \;