SQL Database automatic backup

database_backup.sh

#!/bin/sh

#################################################################
#  Define your variables here:
#################################################################

FILESTOKEEP=8
BACKUP_DIR=/var/www/html/database_backup/

BMYSQL_USER=jhon
BMYSQL_PWD=johndoe123
DATE=$(date +"%Y_%m_%d__%H_%M_%S")

BMYSQL_HOST=localhost
BMYSQL_DBNAME=mydb
BMYSQL_DBFILENAME=mydb_backup__$DATE

#################################################################
#  Make sure output directory exists.
#################################################################

if [ ! -d $BACKUP_DIR ]; then
    mkdir -p $BACKUP_DIR
fi

#################################################################
#  Create backup
#################################################################

mysqldump -u$BMYSQL_USER -p$BMYSQL_PWD $BMYSQL_DBNAME  > $BACKUP_DIR/$BMYSQL_DBFILENAME.sql

#################################################################
#  Remove old backups
#  - this will list files according to date (DESC)
#  - skip the first few files (FILESTOKEEP)
#  - remove all files past that
#  NOTE: Make sure not to save the backups into any directory
#  where there are other files other than these backup ones.
#
#  Uncomment when you are confident in rest of setup
#################################################################
#      cd $BACKUP_DIR
#      ls -t1 | tail -n +$(($FILESTOKEEP+1)) | xargs rm


	cd $BACKUP_DIR

	# Get the number of file
	nb=$(ls -t1 | wc -l)

	# Check if it is greater then 8
	if [ "$nb" -gt 8 ]
	then
		# Get the amount of file to delete
		nbtodelete=`expr $nb - 8`

		# Sort files in descending order and delete some
    		ls -t1 | tail -n $(($nbtodelete)) | xargs rm
	fi