Mysql backup bash script for production

mysql-backup.sh

#!/bin/bash

TODAY=`date +%d.%m.%Y`
TODAY_TIME=`date +%d.%m.%Y-%T`

DB_BACKUP_PATH='/backups/mysql-backups'
MYSQL_CONFIG_PATH='/backups/mysql.cnf'
DATABASE_NAME='asg'

DB_FILE=${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY_TIME}.sql

BACKUP_RETAIN_DAYS=5

EXCLUDED_TABLES=(
failed_jobs  
formatting_price_history  
jobs
loggers  
request_logs  
request_logs_fingerprints  
)
 
IGNORED_TABLES_STRING=''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE_NAME}.${TABLE}"
done

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

mkdir -p ${DB_BACKUP_PATH}/${TODAY}
echo "Backup started for database - ${DATABASE_NAME}"


echo "Dump structure"
mysqldump --defaults-extra-file=${MYSQL_CONFIG_PATH}  --skip-lock-tables --single-transaction --no-data --routines ${DATABASE_NAME} > ${DB_FILE}


echo "Dump content"

mysqldump \
--defaults-extra-file=${MYSQL_CONFIG_PATH} \
--skip-lock-tables \
--single-transaction \
--insert-ignore \
${DATABASE_NAME} \
--no-create-info \
--skip-triggers \
${IGNORED_TABLES_STRING} >> ${DB_FILE}

gzip ${DB_FILE}


if [ $? -eq 0 ]; then
echo "Database backup successfully completed"
else
echo "Error found during backup"
exit 1
fi

# Remove backups older than {BACKUP_RETAIN_DAYS} days #

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

# Restore into a new database

# mysql -u user -p newdatabase < database_structure.sql
# mysql -u user -p newdatabase < database_data.sql

mysql.cnf

[client]
user = "backup"
password = "backup"
host = "localhost"