A poor mans MySQL backup solution.

Summary
In this tutorial you’ll find a poor-mans backup solution for a MySQL database server. Implementing this solution, you’ll end up with a script that:

• creates daily local backups going back up to a month
• saves one of those backups each month. The others are discarded.
• mails a copy of the backups somewhere (root, for example), to create an offsite backup.

For Windows users
This tutorial is not for you. A MySQL Administator that provides backup functionality can be downloaded from http://www.mysql.com.

Background
I wanted to create a back-up solution for a database-server I had no regular physical access to. The databases I use aren’t that big and the server is connected to the internet through a DSL connection, so using E-mail to send out the backups wasn’t a problem. I chose to mail on Sundays, because my network traffic is low in the weekends. I came up with this SH-based script, which runs every night using cron.

I wrote this script on a FreeBSD machine, but you should be able to run it on most machines running a ‘sh’ shell. I have the script ran by a dedicated backup-user (‘backup’). The script uses these commands. Most are very basic, but if your OS doesn’t support them, you’ll have to replace them with an alternative suitable to your situation:

• sh
• mail
• uuencode (to attach files to mail)
• date (returns current date)
• mysqldump (comes with MySQL)
• gzip (compress data)

Implementation
Below you’ll find the source of the script. Copy it into a file (preferably with a .sh extension), and use ‘chmod’ to make that file executable (chmod 500 filename.sh). Make sure to replace USER and PWD – you should enter a suitable MySQL account here.

Create the directories specified in the first few lines of the script. Defaults are these:

• /home/backup/mysql
• /home/backup/mysql/old
• /home/backup/mysql/monthly

Now, run the script, and check for errors. Running the script should result in a file called something like backup_datebases-2004-10-29-02:00:00.sql.gz in /home/backup/mysql. If it happens to be the first of the month, you’ll find the file in the monthly/ directory. If it happens to be a Sunday, a copy will be mailed to root. Check the size of your backup, and make sure that you have at least 31 times that size as free space on your partition, plus one time that size for each month you plan to use this script.

Finally, setup cron to run this script daily, preferably at a time the databases aren’t used. As you can see in the filename, I’m running it at 2 AM.

Enter this to edit your cron table:
crontab -e

Add a line like this:
0 2 * * * /path/to/filename.sh

That’s it! Next Sunday, you should be mailed a copy of the backup.

Restore information
As said, this script outputs several gzipped tarfiles. After decompressing, you'll find ordinary SQL in these files (you should be able to look at it using any text editor, unless your database consists of binary data, of course).

You can use this SQL to restore your databases, or parts of it, if needed. Normally, you'd issue this command to do perform a restore: mysql -u root -p < /path/to/ungzippedfile.sql. You’ll be prompted for a password before any restoring takes place.

Depending on your situation, you could have to add some commands (to delete existing/left-over old data for example). You’ll have to make an assessment of your current situation before you do any restoring. Don’t just execute commands, or you might end up creating a bigger mess than the one you were already in. Databases with false information usually are a bigger problem than databases with no information at all.

You could alter the parameters for mysqldump used in the backup-script, so that the resulting data matches your criteria better. I used the --opt parameter, which is shorthand for a bunch of default parameters, but you might want to include or exclude some others. For more information, have a look at the MySQL documentation, paragraph 8.8 ("The mysqldump Database Backup Program") at http://dev.mysql.com/doc/mysql/en/mysqldump.html.

I found this script to be useful, but if you have any suggestions to add functionality, or improve this script in any other way, please reply to this thread.

Script code

Code:

---

#!/bin/sh
date=`date "+%Y-%m-%d-%H:%M:%S"`
file="backup_datebases-$date.sql.gz"
workdir="/home/backup/mysql"
old="$workdir/old"
monthly="$workdir/monthly"

if [ -d $workdir ] && [ -d $old ] && [ -d $monthly ]; then

 # Toss out old backups each month
 if [ `date "+%d"` = 1 ]; then
        mv $workdir/backup* $old/
 fi

 # Dump all databases.
 /usr/local/bin/mysqldump --all-databases -u USER --password="PWD" --opt | gzip > $workdir/$file

 # Finish tossing out those old backups.
 if [ `date "+%d"` = 1 ]; then
        rm $old/*
 fi


 # Mail a copy to root each sunday (offsite backup)
 if [ `date "+%w"` = 0 ]; then
        uuencode $workdir/$file $file | mail -s "Uuencoded database backup $file" root
 fi

 # Save one copy of the databases each month in monthly/
 if [ `date "+%d"` = 1 ]; then
        cp $workdir/$file $monthly/
 fi

else
        echo "Database-backup not executed at $date - Working directories are missing.";
fi

---

Hi

Nice script, I like it. Good work!

I actually use something similar, that involves a backup-server (called repository) and the
mysql-server (called mysql). I mention it, because it runs under windows, and might be
of interest for some people. The basics to create a more complex script, like yours, is
given. Note, there is no tar-zcf'ing, no backup_rotating, no mail-notification etc.
(otherwise I would write a tutorial of my own :D ).
It is running on a daily basis, using the windows scheduler.

Code:

---

@echo off
net use m: \\mysql\bin /user:mysql_maintenance
net use r: \\repository\mysql /user:mysql_maintenance

set thedate=%DATE%
set MYSQL_DIR=m:\
set BACKUP_DIR=r:\

echo Backup to  %BACKUP_DIR%
cd r:\
r:\

%MYSQL_DIR%mysqldump -h mysql -u mysql_maintenance --password=something particular_db >particular_db_%thedate%.sql
%MYSQL_DIR%mysqldump -h mysql -u mysql_maintenance --password=something  -A >all_dbs_%thedate%.sql


rem Now you can zip them, backup_rotate them, ...

---

Cheers

Again, you made the critical mistake made by a lot of people's backup plans:

There are no instructions as to how to restore a database (or part thereof). Please fix.

Slarty

'Again'? Dude, lighten up, this is my first how-to-install-dash-backup-something tutorial. Nevertheless, you're right, I should've included some restore-information. Read this as an addition to the tutorial.

Restore information
As said, this script outputs several gzipped tarfiles. After decompressing, you'll find ordinary SQL in these files (you should be able to look at it using any text editor, unless your database consists of binary data, of course).

You can use this SQL to restore your databases, or parts of it, if needed. Normally, you'd issue this command to do perform a restore: mysql -u root -p &lt; /path/to/ungzippedfile.sql. You’ll be prompted for a password before any restoring takes place.

Depending on your situation, you could have to add some commands (to delete existing/left-over old data for example). You’ll have to make an assessment of your current situation before you do any restoring. Don’t just execute commands, or you might end up creating a bigger mess than the one you were already in. Databases with false information usually are a bigger problem than databases with no information at all.

You could alter the parameters for mysqldump used in the backup-script, so that the resulting data matches your criteria better. I used the --opt parameter, which is shorthand for a bunch of default parameters, but you might want to include or exclude some others. For more information, have a look at the MySQL documentation, paragraph 8.8 ("The mysqldump Database Backup Program") at http://dev.mysql.com/doc/mysql/en/mysqldump.html.

Here's my backup script..

Code:

---

#!/bin/bash

#  Backup script
#
#        (c) 2003 Anne Jan Brouwer
#        Vernet Verzuim netwerk BV
#        anne_jan@vernet.nl
#
#        Revised October 2004 to make it generally more usable
#        by Anne Jan Brouwer (the_JinX)
#
#        Backups to DVD+R
#        Requires dvd+rw-tools ( dvd+rw-mediainfo and growisofs )
#
#        This version works with MySQL and PostgreSQL databases
#
#        In the example config backup is installed in /home/backup/
#        Add lines like the following to the root crontab to schedule backups
#
#        # Run daily cron job at 21:20 every week day:
#        20 21 * * mon-fri /home/backup/backup daily &> /dev/null
#
#        # Run weekly cron job at 21:30 on sat ( haha backup does work weekends ):
#        30 21 * * sun /home/backup/backup weekly &> /dev/null
#
#  GNU GPL

# CONFIG SYSTEM

ISO="/home/backup/backup.iso"                                        # Disk image (iso) file name
ISOTEMP="/home/backup/iso"                                        # ISO temp folder
MINFREE=1                                                        # Minimum % free space on TARGET
KEEP=5                                                                # Keep nr of files (5 is one week 10 is two weeks etc.)
SOURCE="/home/the_jinx /etc"                                        # Folder(s) to be backed up
TARGET="/home/backup/files"                                        # Folder to place backups
TARFLAGS=""                                                        # Extra tar flags, 'rvf' are always done (man tar)
DATABASES="mydatabase yourdatabase"                                # Database(s) to backup
DBTARGET="/home/backup/sql"                                        # Folder to place db backups
#SQLDUMP="/usr/local/pgsql/bin/pg_dump -C -U postgres"                # PostgreSQL version
SQLDUMP="/usr/bin/mysqldump -c"                                        # MySQL version

# CONFIG MAIL ADRESSES

MAIL="the_jinx@localhost"                                        # Mail adress of backup operator
ADMINMAIL="root@localhost"                                        # Mail adress of sys admin
FROM="backup@localhost"                                                # Mail from adress (backup)

# CONFIG MAIL OPTIONS

SUCCESSHEADER="Backup: Successfull :)"                                # Mail header (success)
NODISKHEADER="Backup: no DVD present"                                # Mail header (no disk)
NOTEMPTYHEADER="Backup: DVD is full !"                                # Mail header (not empty)       
FATALHEADER="Fatal, no backup made !!"                                # Mail header supplement (fatal (AFTER) no disk/notempty)
BODYFILE="/home/backup/donderdag.txt"                                # Mail text file
WARNBODYFILE="/home/backup/friday.txt"                                # Warning mail text file (thursday)
ERRORBODYFILE="/home/backup/failure.txt"                        # Error mail text file


# System config ( DO NOT TOUCH UNLESS YOU KNOW WTF YOU ARE DOING !! )
VERSION="0.3"
DEVICE="/dev/dvd"
LOGGER="/usr/bin/logger -s"

# Inits
EXT=$1        # make $1 available to "functions"
DATE=`date +%Y%m%d`


# log it !
logit()
{
  # syslog
  $LOGGER $*
}


# ERROR HANDLER !!
error()
{
  logit $*
  cat $ERRORBODYFILE | mail -s "Backup: $*" -r "$FROM" $MAIL $ADMINEMAIL
  exit 1
}
 

# checks if there arn't allready too much backups
check_nr()
{
  i=1
  for file in $( ls -r $TARGET/folders-* 2> /dev/null)
  do
    if [[ $i -ge $KEEP ]]
    then
      echo "DELETED: $file"
      rm -f $file
    fi   
    let "i++"
  done

  NRDB=0
  for db in $DATABASES
  do
    let "NRDB++"
  done
  let "DBKEEP=NRDB*KEEP"
 
  i=1
  for file in $( ls -r $DBTARGET/sql-* 2> /dev/null)
  do
    if [[ $i -ge $DBKEEP ]]
    then
      echo "DELETED: $file"
      rm -f $file
    fi   
    let "i++"
  done

}

# check for day of the week
disk_check()
{
  echo -n "Checking DVD: "
  nogood=false
  if [[ $( dvd+rw-mediainfo $DEVICE 2>&1 | grep Disc | grep blank ) == "" ]]
  then
    echo "not usable!"
    nogood=true
    if [[ $( dvd+rw-mediainfo $DEVICE 2>&1 | grep Disc | grep complete ) == "" ]]
    then
      mailheader=$NOTEMPTYHEADER
    else
      mailheader=$NODISKHEADER
    fi
  else
    echo "usable.."
  fi
 
  # check for disk
  if [[ $nogood == true ]]
  then
    if [[ $EXT == "weekly" ]]
    then
      error $mailheader$FATALHEADER
    else
      echo "not fatal"
    fi
   
    case $( date +%u ) in
      3 ) cat $BODYFILE | mail -s "$mailheader" -r  "$FROM" $MAIL && logit $mailheader ;;
      4 ) cat $WARNBODYFILE | mail -s "$mailheader" -r "$FROM" $MAIL $ADMINEMAIL && logit $mailheader ;;
      * ) echo "ignoring"
    esac

  else
    logit "DVD is empty, good!"
  fi
}

check_source()
{
  # source folder check
  if [[ ! -e $1 || ! -d $1 ]]
  then
    error "Source folder $1 does not exis!"
  fi
}

check_target()
{
  if [[ ! -e $1 ]]
  then
    echo -n "Target folder does not exist, attempting to create: "
    mkdir $1
    if [[ $? == 0 ]]
    then
      echo "successfull."
    else
      error "failed to create $1"
    fi
  fi
  if [[ ! -d $1 ]]
  then
    error "Target folder $1 exist but is a file!"
  fi
}

check_space()
{
  # free space check
  space=$(df -h $1)
  noend=${space%"%"*}
  clean=${noend##*" "}
  let "freespace = 100 - $clean"
  if [[ $freespace -lt $MINFREE ]]
  then
    error "Backup skipped, space on $TARGET is only $freespace%, while $MINFREE% is required!"
  fi
}

# do checks
do_checks()
{
  for folder in $SOURCE
  do
    check_source $folder
  done
 
  # user check
  if [[ `id -u` != 0 ]]
  then
    logit `whoami`" is not allowd to do backups!"
    exit 13
  fi

  check_target $TARGET
  check_target $DBTARGET
  check_target $ISOTEMP
 
  check_space $TARGET
  check_space $DBTARGET
  check_space $ISOTEMP
 
  logit "Backup $VERSION: operation: $EXT"
}


# daily backups
backup_daily()
{
  do_checks
  disk_check
  rm $ISOTEMP -r
  check_nr   

  # files
  if [[ -e $TARGET/folders.list ]]
  then
    rm $TARGET/folders.list
  fi
  logit "Gathering files"
  target="$TARGET/folders-$DATE.tar"
  for folder in $SOURCE
  do
    tar rvf $TARFLAGS $target $folder >> $ARGET/folders.list
    if [[ $? != 0 ]]
    then
      error "Error while trying to gather files: $folder"
    fi
  done
  logit "Compressing"
  bzip2 $target
  if [[ $? != 0 ]]
  then
    error "Error while trying to compress $target"
  fi
 
  if [[ -e $DBTARGET/databases.list ]]
  then
    rm $DBTARGET/databases.list
  fi 
  for DATABASE in $DATABASES
  do
      # SQL
      logit "SQL dump"
      echo $DATABASE >> $ARGET/folders.list
      target="$DBTARGET/sql-$DATE-$DATABASE.dmp"
      $SQLDUMP $DATABASE > $target
      if [[ $? != 0 ]]
      then
        error "Error while trying to dump psql dbase: $DATABASE to $target"
      fi
      logit "Compressing dump"
      bzip2 $target
      if [[ $? != 0 ]]
      then
      error "Error while trying to compress $target"
      fi
  done
}

# weekly backups
backup_weekly()
{
  do_checks
  disk_check

  target=$( ls $TARGET/folders-* | tail -n1 )
  dbtargets=$( ls $DBTARGET/sql-* | tail -n$NRDB )
 
  # make iso temp folder
  logit "preparing for iso creation"
  ln $target $ISOTEMP
  for dbtarget in $dbtargets
  do
    ln $dbtarget $ISOTEMP
  done
  cp $TARGET/folders.list $DBTARGET/databases.list $ISOTEMP
 
  # make iso
  mkisofs -o $ISO -J -r -V "backup_$DATE" $ISOTEMP
  if [[ $? != 0 ]]
  then
    error "Error while trying to create iso $ISO from $TARGET"
  else
    logit "iso created !"
    rm -r $ISOTEMP
  fi
 
  # burn iso
  growisofs -Z $DEVICE=$ISO
  if [[ $? != 0 ]]
  then
    error "Error while trying to write iso $ISO to $DEVICE"
  else
    logit "dvd created !"
    rm $ISO
    logit "Backup burned successfull !"
    echo "Backup made ;)" | mail -s "$SUCESSHEADER" -r "$FROM" $MAIL $ADMINEMAIL
  fi
}


# The main execution
case "$1" in
'daily')
  backup_daily
  ;;
'weekly')
  backup_weekly
  ;;
*)
  echo "Usage: $0 { weekly | daily }"
esac

#EOF

---

This baby has no restore info either and is released AS IS under the GNU GPL