MySQL Database Backup

The script show below can be run on Linux, Solaris and other Unix flavours as daily cron job. It creates a MySQL database dump for every database in a separate file. Older backups are consolidated by recycling the file name so that the hard disk or NFS share isn't filled.

Daily backupfile named after weekday (e. g. database.daily_Wednesday.sql). After seven days it will be overwritten.
On the last day in the month the file is named after the month number (e. g. database.monthly_09.sql). After twelve months it is overwritten.
On 31 december the file is named after the year (e. g. database.yearly_2013.sql. This file is kept forever.

Program execution as follows:

/usr/local/bin/mysqlbackup.sh dbhost1.example.com:3306 dbhost2.example.com:3306

In der MySQL Datenbank muss ein User mit folgenden Privilegien erstellt werden:

grant show databases,show view,file,select,lock tables \
on *.* to 'backup'@'backuphost.example.com' identified by 'changeme';

Add user and password in the MySQL configuration file .my.cnf in the user home directory.

[client]
user=backup
password=changeme

[mysqldump]
max-allowed-packet=2147483648
quote-names=true
verbose=true

Here's the bash script:

#!/bin/bash

PATH="/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin"
BACKUPDIR="/var/backup/mysql"
CONFIG="/home/user/.my.cnf"
EXCLUDEDB='^(Database|information_schema|performance_schema)$'
MAILFROM="backup@example.com"
MAILTO="user@example.com"

# Simple housekeeping
# Daily backups with week day names
# Monthly backups on the last day of month
# Yearly backups on last day of december

year=$(date "+%Y")
month=$(date "+%m")
monthname=$(date "+%B")
day=$(date "+%d")
dayname=$(date "+%A")

d=28
if (( $year %   4 == 0 )); then d=29; fi
if (( $year % 100 == 0 )); then d=28; fi
if (( $year % 400 == 0 )); then d=29; fi

case "$month:$day" in
  01:31) timestamp="monthly_${month}"   ;;
  02:$d) timestamp="monthly_${month}"   ;;
  03:31) timestamp="monthly_${month}"   ;;
  04:30) timestamp="monthly_${month}"   ;;
  05:31) timestamp="monthly_${month}"   ;;
  06:30) timestamp="monthly_${month}"   ;;
  07:31) timestamp="monthly_${month}"   ;;
  08:31) timestamp="monthly_${month}"   ;;
  09:30) timestamp="monthly_${month}"   ;;
  10:31) timestamp="monthly_${month}"   ;;
  11:30) timestamp="monthly_${month}"   ;;
  12:31) timestamp="yearly_${year}"   ;; 
  *)     timestamp="daily_${dayname}" ;;
esac

for hostport in $@; do
  echo ""
  echo "*******************************************************************************"
  echo "Backing up MySQL on $hostport to $BACKUPDIR" 
  echo "*******************************************************************************"
  echo ""
  host=$(echo $hostport | awk -F: '{print $1}')
  port=$(echo $hostport | awk -F: '{print $2}')

  hostdir="$BACKUPDIR/$hostport"
  test -d $hostdir || mkdir -p $hostdir

  databases=$(echo "show databases;" \
    | mysql --defaults-file="$CONFIG" -h "$host" -P "$port" | grep -Ev "$EXCLUDEDB")

  for db in $databases; do
    echo -n "Dumping database $db ... "

    dbdir="$hostdir/$db"
    dumpfile="$dbdir/${db}.${timestamp}.sql"
    logfile="$dbdir/${db}.${timestamp}.log"

    test -d $dbdir || mkdir -p $dbdir
    mysqldump --defaults-file="$CONFIG" --log-error="$logfile" \
      -h "$host" -P "$port" "$db" >$dumpfile
    retval=$?
    chmod 0644 $logfile
    chmod 0400 $dumpfile

    case $retval in
      0) echo "OK" ;;
      *) echo "FAILED"
         ( echo "From: $MAILFROM"
           echo "To: $MAILTO"
           echo "Subject: Error backing up MySQL database $db on $hostport"
           echo ""
           echo "The command"
           echo "mysqldump --defaults-file=$CONFIG --log-error=$logfile -h $host -P $port $db"
           echo "returned value: $retval"
           echo ""
           echo "Error log: $logfile"
           echo "$(cat $logfile)"
           echo "."
         ) | sendmail -t
        ;;
    esac 
  done
done