A script to create mysql backups and keep backups with a retention scheme.

mysqlbackup.sh

#!/bin/bash
#
# Copyright 2013 Rene Diepstraten
# 
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
# 
#   http://www.apache.org/licenses/LICENSE-2.0
# 
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

backupdir=/var/backups/mysql
day=$(date '+%F')
hour=$(date '+%R')

removedir() {
    if [[ -d "$1" ]]
    then
        echo "Removing $1 ..."
        rm -r $1
    fi
}

[[ ! -d ${backupdir}/${day}/${hour} ]] && mkdir -p ${backupdir}/${day}/${hour}/dumps

#Create a full backup
if [[ "${hour}" =~ (00|03|06|09|12|15|18|21):00 ]]
then
    logfile=$(mktemp)
    echo "Creating full backup..."
    /usr/bin/innobackupex --no-timestamp --slave-info --safe-slave-backup --no-lock ${backupdir}/${day}/${hour}/full >${logfile} 2>&1
    /usr/bin/innobackupex --apply-log ${backupdir}/${day}/${hour}/full >> ${logfile} 2>&1
    mv ${logfile} ${backupdir}/${day}/${hour}/full/innobackupex.log
    
fi

#Create dumps
logfile=$(mktemp)
for database in $(mysql -sNe "show databases" | grep -vE 'information_schema|performance_schema')
do
    echo "Dumping database ${database} ..." | tee -a ${logfile}
    mysqldump -E ${database} 2>> ${logfile} | gzip > ${backupdir}/${day}/${hour}/dumps/${database}.sql.gz
done
mv ${logfile} ${backupdir}/${day}/${hour}/dumps/mysqldump.log


echo "Cleanup..."

if [[ "${hour}" == "00:00" ]]
then
    # Delete the folder from last year if it is the 1st .
    if [[ "$(date '+%d')" == "01" ]]
    then
        removedir ${backupdir}/$(date -d "last year" '+%F')
    fi

    # Delete the folder from last month if it is the 8th, 15th, 22th or 29th .
    if [[ "$(date '+%d')" =~ (08|15|22|29)$ ]]
    then
        removedir ${backupdir}/$(date -d "last month" '+%F')
    fi

    # Delete the folder from 8 days ago, unless it is the 1st, 8th, 15th, 22th or 29th .
    if [[ ! "$(date '+%d')" =~ (01|08|15|22|29)$ ]]
    then
        removedir ${backupdir}/$(date -d "8 days ago" '+%F')
    fi
else
    # Delete the folder from 96 hours ago
    removedir ${backupdir}/$(date -d "4 days ago" '+%F')/${hour}
fi

# Delete the folder from 48 hours ago, unless it is on each 6th hour .
if [[ "${hour}" =~ (03|09|15|21):00 ]]
then
    removedir ${backupdir}/$(date -d "2 days ago" '+%F')/${hour}
fi

# Delete the folder from 24 hours ago, unless it is on each 3rd hour .
if [[ ! "${hour}" =~ (00|03|06|09|12|15|18|21):00 ]]
then
    removedir ${backupdir}/$(date -d "yesterday" '+%F')/${hour}
fi

mysqlbackup

A script to create mysql backups and keep backups with a retention scheme.
It creates backups every 15 minutes and every three hours a backup with Percona XtraBackup.

Requirements:

• Percona XtraBackup.

Installation:

1. Create a .my.cnf file with mysql root credentials in /root:

[client]
user     = <mysql-root-user>
password = <password>

2. Install the script as /usr/local/sbin/backup
3. Setup a cron job:

*/15 * * * * /usr/local/sbin/backup

Retention scheme:

• 12 monthly backups are maintained
• 4 weekly backups
• 7 daily backups
• 96 backups of every 6 hours
• 48 backups of every 3 hours
• 96 quarterly backups