Backup database script

backup_all_database.sh

#! /bin/bash

DB_USER=username
DB_PASS=password
BACKUP_DIR=absolute_dirpath/ #don't forget the trailing slash
DIR_NAME=$(date +%Y%m%d_%H%M%S)
NUM_BACKUP=14

DATABASES=$(mysql -u${DB_USER} -p${DB_PASS} -e 'show databases;' 2>&1)
if [[ $DATABASES == *"ERROR"* ]]; then
    echo "ERROR ${DATABASES##*ERROR}"
    exit 3
fi
DATABASES=$(echo "$DATABASES" | sed -n '3,$ p')

FULL_DIR=${BACKUP_DIR}${DIR_NAME}
mkdir ${FULL_DIR}
echo "Created Dir: " . ${FULL_DIR}

for DB_NAME in $DATABASES;
do
    if [[ ! $DB_NAME == 'information_schema' ]]; then
        $(mysqldump --single-transaction --quick --lock-tables=false  -u${DB_USER} -p${DB_PASS} "${DB_NAME}" 2>&1 | sed -n "2,$ p" | gzip --best > ${FULL_DIR}/${DB_NAME}.sql.gz)
        echo "Backup: ${DB_NAME}"
    fi
done

#Delete old backup
BACKUP_LIST=(`find ${BACKUP_DIR}* -type d | sort`)
BACKUP_LIST_LEN=${#BACKUP_LIST[@]}
for (( i=0; i<$BACKUP_LIST_LEN-$NUM_BACKUP; i++ ));
do
  rm -r ${BACKUP_LIST[$i]}
  echo "Deleted: ${BACKUP_LIST[$i]}"
done

backup_database.sh

#! /bin/bash

DB_NAME=database
DB_USER=username
DB_PASS=password
BACKUP_DIR=backup_dir/
FILE_NAME=${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql.gz
NUM_BACKUP=14

mysqldump --single-transaction --quick --lock-tables=false -u${DB_USER} -p${DB_PASS} ${DB_NAME} 2>&1 | sed -n "2,$ p" | gzip --best > ${BACKUP_DIR}${FILE_NAME}
echo "Created: ${BACKUP_DIR}${FILE_NAME}"

#Delete old backup
BACKUP_LIST=(`find ${BACKUP_DIR}* | sort`)
BACKUP_LIST_LEN=${#BACKUP_LIST[@]}
for (( i=0; i<$BACKUP_LIST_LEN-$NUM_BACKUP; i++ ));
do
  rm ${BACKUP_LIST[$i]}
  echo "Deleted: ${BACKUP_LIST[$i]}"
done