Hourly and Daily MYSQL Backup / Dump with Retention Period

README.md

Steps

Create directories

mkdir -p /backup/mysql/hourly
mkdir -p /backup/mysql/daily

Set permissions on scripts:

chmod 755 /usr/local/sbin/hourly_mysqlbackup.sh
chmod +x /usr/local/sbin/hourly_mysqlbackup.sh

chmod 755 /usr/local/sbin/daily_mysqlbackup.sh
chmod +x /usr/local/sbin/daily_mysqlbackup.sh

Setup crontab

sudo crontab -e

cron

# Daily at 1am
0 1 * * * sh /usr/local/sbin/daily_mysqlbackup.sh
#
#
# Every hour
#
0 * * * * sh /usr/local/sbin/hourly_mysqlbackup.sh

daily_mysqlbackup.sh

# Source: https://www.kinamo.be/en/support/faq/mysql-automatic-backup-of-database
#
# Crontab
# 0 1 * * * sh /usr/local/sbin/daily_mysqlbackup.sh
#
#!/bin/bash

# Basic configuration: datestamp e.g. YYYYMMDD

DATE=$(date +"%Y%m%d")

# Location of your backups (create the directory first!)

BACKUP_DIR="/backup/mysql/daily"

# MySQL login details

MYSQL_USER="root"
MYSQL_PASSWORD="YOURSECUREPASSWORD"

# MySQL executable locations (no need to change this)

MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

# MySQL databases you wish to skip

SKIPDATABASES="Database|information_schema|performance_schema|mysql"

# Number of days to keep the directories (older than X days will be removed)

RETENTION=7

# ---- DO NOT CHANGE BELOW THIS LINE ------------------------------------------
#
# Create a new directory into backup directory location for this date

mkdir -p $BACKUP_DIR/$DATE

# Retrieve a list of all databases

databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"`

# Dumb the databases in seperate names and gzip the .sql file

for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done

# Remove files older than X days

find $BACKUP_DIR/* -mtime +$RETENTION -delete

hourly_mysqlbackup.sh

# Source: https://www.kinamo.be/en/support/faq/mysql-automatic-backup-of-database
#
# Crontab
# 0 * * * * sh /usr/local/sbin/hourly_mysqlbackup.sh
#
#!/bin/bash

# Basic configuration: datestamp e.g. YYYYMMDD

DATE=$(date +"%Y%m%d-%H00")

# Location of your backups (create the directory first!)

BACKUP_DIR="/backup/mysql/hourly"

# MySQL login details

MYSQL_USER="root"
MYSQL_PASSWORD="YOURSECUREPASSWORD"

# MySQL executable locations (no need to change this)

MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

# MySQL databases you wish to skip

SKIPDATABASES="Database|information_schema|performance_schema|mysql"

# Number of MINUTES to keep the directories (older than X minutes will be removed)
# 60 * hours = 60 * 12 = 720 
#
RETENTION=720

# ---- DO NOT CHANGE BELOW THIS LINE ------------------------------------------
#
# Create a new directory into backup directory location for this date

mkdir -p $BACKUP_DIR/$DATE

# Retrieve a list of all databases

databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"`

# Dumb the databases in seperate names and gzip the .sql file

for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done

# Remove files older than X days

find $BACKUP_DIR/* -mmin +$RETENTION -delete