MySQL Database Backup Shell Script

mysqlbackup.sh

#!/bin/sh
export PATH="$HOME/.local/bin:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin"
umask 0027

# Local backup destination root
LOCAL_BACKUP_PATH="/u01/backups/mysql"

# How long to keep local backup files
LOCAL_BACKUPS_DAYS_TO_KEEP=10

# Backup file name prefix
LOCAL_HOSTNAME=`hostname`
BACKUP_FILE_NAME_PREFIX="mysql-${LOCAL_HOSTNAME}"

# List of MySQL databases to backup
DATABASES=""

# Amazon S3 destination bucket
S3_BUCKET=""

# Amazon S3 destination prefix
S3_DESTINATION_PREFIX=""

# Amazon S3 bucket region
S3_BUCKET_REGION=""

# Flag: Enable or disable AWS S3 sync (0 = disable, 1 = enable)
S3_SYNC_ENABLED=0

# Amazon S3 sync log directory
S3_SYNC_LOG_DIRECTORY="/u01/logs/backups"

# Amazon S3 sync log file name
S3_SYNC_LOG_FILE="s3-mysql.log"
S3_SYNC_LOG="${S3_SYNC_LOG_DIRECTORY}/${S3_SYNC_LOG_FILE}"

# Create a date/timestamp string to include in backup file names
TIMESTAMP=`date "+%Y%m%d%H%M%S"`

# Create the local backup path and S3 sync log directories if they don't already exist
if [ ! -d ${LOCAL_BACKUP_PATH} ]; then
	mkdir -p ${LOCAL_BACKUP_PATH}
fi

if [ ! -d ${S3_SYNC_LOG_DIRECTORY} ]; then
	mkdir -p ${S3_SYNC_LOG_DIRECTORY}
fi

# Loop through each database and run mysqldump and save out bzip2-compressed output
for db in ${DATABASES}
do
	# Check to see if the database backup directory exists, create if it doesn't
	LOCAL_DB_BACKUP_PATH="${LOCAL_BACKUP_PATH}/${db}"
	if [ ! -d ${LOCAL_DB_BACKUP_PATH} ]; then
		mkdir -p ${LOCAL_DB_BACKUP_PATH}
	fi

	# Use mysqldump to output datbase backup and bzip2 compress the output
	DB_BACKUP_FILE_PATH="${LOCAL_DB_BACKUP_PATH}/${db}-${TIMESTAMP}.sql.bz2"
	mysqldump --opt --default-character-set=utf8mb4 --events --databases ${db} | bzip2 -c > ${DB_BACKUP_FILE_PATH}

	# If the database backup was successful, remove backup files older than days to keep
	if [ $? -eq 0 ]; then
		find ${LOCAL_DB_BACKUP_PATH} -type f -mtime +${LOCAL_BACKUPS_DAYS_TO_KEEP} -exec rm {} \;
	fi
done

# Allow admin group read access
#chown -R root:admin ${LOCAL_BACKUP_PATH}

# If enabled, sync backup to S3
if [ ${S3_SYNC_ENABLED} -eq 1 ]; then
	aws s3 sync --only-show-errors ${LOCAL_BACKUP_PATH} ${S3_BUCKET}/${S3_DESTINATION_PREFIX}/ >> ${S3_SYNC_LOG} 2>&1
fi