MySql Backup & Remove - with slight modifications

MySql Backup & Remove

#!/bin/bash
# Script will output dumps for all databases using seperate files
# Derived from this post: http://www.cyberciti.biz/faq/ubuntu-linux-mysql-nas-ftp-backup-script/

USER="local_backup_script"
PASSWORD="LocalBackupScript#429"
HOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

#OUTPUT_DIR="/mnt/web_db_backups/tapemacs"
OUTPUT_DIR="/home/shared/backups"

DAYSOLD=7

NOW="$(date +"%Y-%m-%d")"


# DB skip list
SKIP="information_schema
mysql
performance_schema
sys
tm_develop"

DBS="$($MYSQL -u $USER -h $HOST -p$PASSWORD -Bse 'show databases')"

for db in $DBS
do
    skipdb=-1
    if [ "$SKIP" != "" ];
    then
		for i in $SKIP
		do
			[ "$db" == "$i" ] && skipdb=1 || :
		done
    fi

    if [ "$skipdb" == "-1" ] ; then
			echo "DUMPING DATABASE: $db"

			tbl_count=0

			DBST="$($MYSQL -NBA -h $HOST -u $USER -p$PASSWORD -D $db -e 'show tables')"

			for t in $DBST
			do
				echo "DUMPING TABLE: $db.$t"

				FILE="$OUTPUT_DIR/$NOW.$db.$t.sql.gz"

				$MYSQLDUMP --no-create-db --extended-insert=FALSE --add-drop-table --create-options --single-transaction -u $USER -h $HOST -p$PASSWORD --skip-lock-tables $db $t | $GZIP > $FILE
			done

    fi
done

# Delete all backups older than X days
find $OUTPUT_DIR -mtime +$DAYSOLD -exec rm -f {} \;

MySql Backup & Remove 2

#!/bin/bash

export PATH=/bin:/usr/bin:/usr/local/bin
TODAY=`date +"%d%b%Y"`

################## Update below values  ########################

DB_BACKUP_PATH='/home/you-ssh-user/backup'
MYSQL_HOST='localhost'
MYSQL_PORT='3306'
MYSQL_USER='root'
MYSQL_PASSWORD='mysecret'
DATABASE_NAME='mydb'
BACKUP_RETAIN_DAYS=30

#################################################################

mkdir -p ${DB_BACKUP_PATH}/${TODAY}
echo "Backup directory creation done"

mysqldump -h ${MYSQL_HOST} \
   -P ${MYSQL_PORT} \
   -u ${MYSQL_USER} \
   -p${MYSQL_PASSWORD} \
   ${DATABASE_NAME} | gzip > ${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz


if [ $? -eq 0 ]; then
  echo "Database backup done"
else
  echo "Error while backup"
  exit 1
fi

DBDELDATE=`date +"%d%b%Y" --date="${BACKUP_RETAIN_DAYS} days ago"`

if [ ! -z ${DB_BACKUP_PATH} ]; then
      cd ${DB_BACKUP_PATH}
      if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then
            rm -rf ${DBDELDATE}
      fi
fi