Skip to content

Instantly share code, notes, and snippets.

@Tegos
Last active July 15, 2022 07:40
Show Gist options
  • Save Tegos/6a80591f37d7c0bf3de5e05e59633fe2 to your computer and use it in GitHub Desktop.
Save Tegos/6a80591f37d7c0bf3de5e05e59633fe2 to your computer and use it in GitHub Desktop.
Mysql backup bash script for production
#!/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
[client]
user = "backup"
password = "backup"
host = "localhost"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment