Enrico Boldori / Wiki

« Go back ⤓ Download

Linux - MySQL - MySQL backup script.sh

#!/bin/bash
#
# ------------------------------------------------------------
# Author:      Enrico Boldori
# Description: MySQL backup script
#
# Script function:
# This script uses mysqldump to automatically backup each MySQL database, excluding system-related
# ones (sys, mysql, information_schema, performance_schema).
#
# Backups are automatically rotated, keeping the last 6 hours backups under the daily folder.
# If today's daily backup has not been created yet, the hourly backup gets copied under the daily folder.
# Daily backups are retained for 8 days.
# If the current day is a Sunday, today's backup is copied under the weekly folder.
# If the current day is the first day of the month, today's backup is copied under the monthly folder.
#
# This script should be configured to use a credentials file with the following content:
#
# [client]
# user = backup user's username
# password = backup user's password
#
# To prevent anyone from reading the credentials file, its permissions should be set to 400.
#
# This user should be granted the following permissions:
# CREATE USER 'backup'@'localhost' IDENTIFIED BY 'Some complex password here';
# GRANT SELECT, LOCK TABLES ON *.* TO 'backup'@'localhost';
#
# Finally, this script should be added to the crontab file (crontab -e) with the following line:
# 0 */4 * * * /root/mysql_backup.sh
# ------------------------------------------------------------

# Backup configuration
BACKUP_DIR="/var/www/mysqldump"
CREDENTIALS_FILE="/root/.mysql_backup_credentials"

BACKUP_DIR_HOURLY="${BACKUP_DIR}/hourly"
BACKUP_DIR_DAILY="${BACKUP_DIR}/daily"
BACKUP_DIR_WEEKLY="${BACKUP_DIR}/weekly"
BACKUP_DIR_MONTHLY="${BACKUP_DIR}/monthly"

# Define commands
MYSQLDUMP="/usr/bin/mysqldump --defaults-extra-file=${CREDENTIALS_FILE} --add-drop-database --add-drop-table --hex-blob --single-transaction --skip-lock-tables --no-tablespaces"
MYSQLSHOW="/usr/bin/mysqlshow --defaults-extra-file=${CREDENTIALS_FILE}"

# Create the backup folders if they do not exist
mkdir -p "${BACKUP_DIR}"
mkdir -p "${BACKUP_DIR_HOURLY}"
mkdir -p "${BACKUP_DIR_DAILY}"
mkdir -p "${BACKUP_DIR_WEEKLY}"
mkdir -p "${BACKUP_DIR_MONTHLY}"

chmod 700 "${BACKUP_DIR}"
chmod 700 "${BACKUP_DIR_HOURLY}"
chmod 700 "${BACKUP_DIR_DAILY}"
chmod 700 "${BACKUP_DIR_WEEKLY}"
chmod 700 "${BACKUP_DIR_MONTHLY}"

# List all the databases
DATABASES=$(${MYSQLSHOW} | head -n -1 | tail -n +4 | tr -d " |")

# Generate a timestamp in the format YYYY-MM-DD
TODAY=$(date +\%F)

# Get the day of week (1..7, 1 is Monday) and day of month (1..31)
DAY_OF_WEEK=$(date +\%u)
DAY_OF_MONTH=$(date +\%d)

# Get the current time
CURRENT_TIME=$(date +\%H-\%M)

# Delete old database dumps
find "${BACKUP_DIR_HOURLY}"  -type f -name "*.sql.gz" -mmin  +1470 -delete
find "${BACKUP_DIR_DAILY}"   -type f -name "*.sql.gz" -mtime +6    -delete
find "${BACKUP_DIR_WEEKLY}"  -type f -name "*.sql.gz" -mtime +27   -delete
find "${BACKUP_DIR_MONTHLY}" -type f -name "*.sql.gz" -mtime +365  -delete

# Dump each database
for DATABASE in ${DATABASES}; do

	# Exclude certain databases from the dump
	if [ "${DATABASE}" == "" ];                   then continue; fi
	if [ "${DATABASE}" == "sys" ];                then continue; fi
	if [ "${DATABASE}" == "mysql" ];              then continue; fi
	if [ "${DATABASE}" == "information_schema" ]; then continue; fi
	if [ "${DATABASE}" == "performance_schema" ]; then continue; fi

	# Dump the hourly database
	HOURLY_DATABASE_DUMP_NAME="${DATABASE}_${TODAY}_${CURRENT_TIME}.sql.gz"
	HOURLY_DATABASE_DUMP_FILE="${BACKUP_DIR_HOURLY}/${HOURLY_DATABASE_DUMP_NAME}"

	${MYSQLDUMP} "${DATABASE}" | gzip -9 -c > "${HOURLY_DATABASE_DUMP_FILE}"

	# Ensure that only root can read the dumped file
	chmod 400 "${HOURLY_DATABASE_DUMP_FILE}"

	# Copy it under the DAILY folder if it does not already exist
	DAILY_DATABASE_DUMP_NAME="${DATABASE}_${TODAY}.sql.gz"
	DAILY_DATABASE_DUMP_FILE="${BACKUP_DIR_DAILY}/${DAILY_DATABASE_DUMP_NAME}"

	if [ -f "${DAILY_DATABASE_DUMP_FILE}" ]; then continue; fi

	ln "${HOURLY_DATABASE_DUMP_FILE}" "${BACKUP_DIR_DAILY}/${DAILY_DATABASE_DUMP_NAME}";

	# If on a Sunday, copy the backup to the weekly folder
	if [ ${DAY_OF_WEEK} -eq 7 ]; then ln "${DAILY_DATABASE_DUMP_FILE}" "${BACKUP_DIR_WEEKLY}/${DAILY_DATABASE_DUMP_NAME}"; fi

	# If on the first day of the month, copy the backup to the monthly folder
	if [ ${DAY_OF_MONTH} -eq 1 ]; then ln "${DAILY_DATABASE_DUMP_FILE}" "${BACKUP_DIR_MONTHLY}/${DAILY_DATABASE_DUMP_NAME}"; fi
done