Results 1 to 5 of 5

Thread: A poor mans MySQL backup solution.

  1. #1
    Hi mom!
    Join Date
    Aug 2001
    Posts
    1,103

    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 &gt; $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
    I wish to express my gratitude to the people of Italy. Thank you for inventing pizza.

  2. #2
    Senior Member
    Join Date
    Mar 2004
    Posts
    557
    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 ).
    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 &gt;particular_db_%thedate%.sql
    %MYSQL_DIR%mysqldump -h mysql -u mysql_maintenance --password=something  -A &gt;all_dbs_%thedate%.sql
    
    
    rem Now you can zip them, backup_rotate them, ...
    Cheers
    If the only tool you have is a hammer, you tend to see every problem as a nail.
    (Abraham Maslow, Psychologist, 1908-70)

  3. #3
    Senior Member
    Join Date
    Jan 2002
    Posts
    1,207
    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

  4. #4
    Hi mom!
    Join Date
    Aug 2001
    Posts
    1,103
    '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.
    I wish to express my gratitude to the people of Italy. Thank you for inventing pizza.

  5. #5
    Leftie Linux Lover the_JinX's Avatar
    Join Date
    Nov 2001
    Location
    Beverwijk Netherlands
    Posts
    2,534
    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 &&gt; /dev/null
    #
    #	# Run weekly cron job at 21:30 on sat ( haha backup does work weekends ):
    #	30 21 * * sun /home/backup/backup weekly &&gt; /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&gt; /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&gt; /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&gt;&1 | grep Disc | grep blank ) == "" ]]
      then 
        echo "not usable!" 
        nogood=true
        if [[ $( dvd+rw-mediainfo $DEVICE 2&gt;&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 &gt;&gt; $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 &gt;&gt; $ARGET/folders.list
          target="$DBTARGET/sql-$DATE-$DATABASE.dmp"
          $SQLDUMP $DATABASE &gt; $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
    ASCII stupid question, get a stupid ANSI.
    When in Russia, pet a PETSCII.

    Get your ass over to SLAYRadio the best station for C64 Remixes !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •