dump_live.sh

#!/bin/sh -e

# dont echo anything, comment out if needed
echo() { :; }

# DB settings
USER="user"
DATABASE="db"
HOST="127.0.0.1"
DIRECTORY="dumps"

# general Variables
TIMESTAMP=$(date +%Y%m%d-%H%M)
FILESUFFIX="-live"
AGE=7

# optional exclude tables (add one table per line)
EXCLUDED_TABLES=(
#exampleTable
)
EXCLUDED_TABLES_STRING=''

for TABLE in "${EXCLUDED_TABLES[@]}"
do :
    EXCLUDED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done

# create folder for dumps if needed
[ -d ${DIRECTORY} ] || mkdir ${DIRECTORY}

echo "$(date +%H:%M) - DUMPING SQL - Need MySQL password for ${DATABASE}"

mysqldump -h ${HOST} -u ${USER} -p ${DATABASE} -a --opt ${EXCLUDED_TABLES_STRING} | gzip > ${DIRECTORY}/${TIMESTAMP}_${DATABASE}${FILESUFFIX}.sql.gz

echo "$(date +%H:%M) - DUMPING ${DATABASE} COMPLETED"

# delete files older than $AGE days
if [[ -n $(find $DIRECTORY -type f -name '*.sql.gz' -mtime +$AGE) ]]; then
    echo "$(date +%H:%M) - DELETING DUMPS OLDER THAN ${AGE} DAYS"
    find ${DIRECTORY} -type f -name '*.sql.gz' -mtime +$AGE -delete -print
    echo "$(date +%H:%M) - DELETING OLD DUMPS COMPLETED"
fi