db_backup.sh

#! /bin/bash

# set user
USER="usr"

# set port
PORT="22"

# set remote IP (put yours here)
HOST="127.0.0.1"

# set src directory
SRC="/src/dir/here"

# set backup directory
DST="/backup/dir/here"

# set name of database to test
DBNAME="db_test"

# get yesterday's date (Daylight Savings Time-safe)
PREVDAY=$(date -d "yesterday 13:00 " '+%Y%m%d')

# get today's date
TODAY=$(date '+%Y%m%d')

# copy the files over
# this requires the sshpass utility to do the transfer
# (put in the password for your remote server where it says "password"
sshpass -p 'password' scp -P $PORT $USER@$HOST:$SRC/*.sql $DST

# delete all the old backups if backups exist from today now
if [ -f "$DST/$DBNAME.$TODAY.sql" ]
  then rm -rf $DST/*.$PREVDAY.sql
fi