HowTo Make & Schedule Daily & Weekly Backup Scripts
General Overview

mySQL Script

Overview:

Since databases are often accessed during all hours, they need special backup progams that pull the information directly from the databases, instead of just backing up the files themselves. This script is an example of how to create a backup file of a mySQL server running on FreeBSD using the mysqldump utility that is part of mySQL.

Also, this example is not a prefered method for a large heavily accessed server, if you are maintaing a large server, you will want to look into activating and maintaining transactions logs as well as doing regular dumps of your databases

The Work:

You will need to decide a few things when using this script. You need to look at the amount of use your mySQL server has, and the times of the day when this use is heaviest. Another thing to consider is where you want to write the backup files to. It is necessary to examine the amount of space free on your drives, and the use of those drives. On a heavily accessed server, you will not want to dump your databases to the same physical drive, to keep from slowing the performance of the current database transactions.

When I was writing this script, I decided that I wanted to have a seperate backup for each database on my server, and yet, have only one file in the end. I also didn't want to have to change this script when I add or remove a database from the system. It is for these two reasons that a section of the script file is there to determine the databases on the server before making the dump files, and then it creates a single archive from those multiple files. I can then after uncompressing the archive file choose which databases I want to restore from the dump files. You may want to take this a step further, and create a seperate dump file for each table in each database.

#!/bin/sh
#
PATH=/usr/local/bin:/bin:/usr/bin

# Variables
# One less than the number of backups you want to save.
# You will want to consider how often you are running the backups when 
# making this choice. Also, this must be 3 or greater for the rest of this 
# script to work.
count=6
# Directory to store backup files in.
budir="/root/backups"
# Directory to store temporary dump files in.
tempdump=$budir
# Name of current backup file.
tarfile=$budir"/mySQL.tar" # Temp until compressed.
bzip2=$budir"/mySQL.tar.bz2" # Final Compressed archive file.

# Format Mail Header To Allow Piped Output Mailed To User
echo "From root@mydomain.Org"
echo "From: mySQL Backup"
echo "To: root@mydomain.Org"
echo "Subject: mySQL BackUp Log"
echo "MIME-Version: 1.0"
echo "Content-Type: TEXT/PLAIN; charset=US-ASCII"

echo ""
# Begin Body of Mail Message, and begin to do the work
echo "Backup Job Started....";date

# Delete Oldest mySQL Archive file, If Exists.
# And Rename Susbiquent mySQL archive files.
case1=$count
case2=$(($count-1))
until [ $count -eq 0 ]
 do
  case "$count" in
    $case1)
      echo ""
      echo "Removed Files...."
      if [ -e $bzip2.old$count ]
        then
          rm -v $bzip2.old$count
      fi
      ;;
    $case2)
      echo ""
      echo "Renamed Files...."
      until [ $count -lt 1 ]
        do
          if [ -e $bzip2.old$count ]
            then
              mv -v $bzip2.old$count $bzip2.old$(($count+1))
          fi
          count=$(($count-1))
      done
      count=$(($count+2))
      ;;
    1)
      if [ -e $bzip2 ]
        then
        mv -v $bzip2 $bzip2.old$count
      fi
      ;;
  esac
  count=$(($count-1))
done

# Create mySQL Dump Files For Live Data Archive
echo ""
echo "Creating mySQL Dump Files....";date

 # root directory for your mySQL server data files.
 mysqldir="/mysql"

 # File to temporarily store the names of your databases.
 dirfile="/root/backups/scripts/mysql.dir"

 # Create the above file.
 ls -F $mysqldir | grep "/" | sed s/[/]/""/g | grep -n "" > $dirfile

 # Count the number of Databases on your server.
 count=`cat $dirfile | grep -c ":"`

 # Create a Dump file for each database.
 until [ $count -eq 0 ]
  do
   # Get the database name from your temporary file.
   database=`cat $dirfile | grep ^$count: | sed s/[$count:]/""/g`
   # Create the temporary dump file, this will be removed after the  
   # archive is completed.
   mysqldump -a -B $database > $tempdump/mySQL.$database.dump
   count=$(($count-1))
  done

# Remove temporary file that you stored your database names in.
rm $dirfile

# Create mySQL tar File From Dump Files
echo ""
echo "Creating mySQL Dump tar File From Dump Files....";date

tar -cvf $tarfile $tempdump/mySQL.*.dump 

# Remove Temporary Dump Files.
echo ""
echo "Removing Temporary Dump Files....";date

rm -f -v $tempdump/mySQL.*.dump

# Compress mySQL Dump File with bzip2
echo ""
echo "Compressing mySQL tar File with bzip2....";date

bzip2 -8 $tarfile

echo ""
echo "Backup Job Completed....";date