OpenEMR – Dump Database

Tools, ,

Updated: On some systems (debian squeeze) the statement (let OLDFILES=${FILENAMEDATE}-${KEEPDAYS}) will no longer execute properly so it should be updated to (OLDFILES=`expr ${FILENAMEDATE} – ${KEEPDAYS}`).   The correction has been applied to the script below.

As part of our backup process on the OpenEMR system we create hourly dumps of the database. The script below handles the database export, compression, and daily cleanup. This script can be run manually but we choose to place it in /etc/cron.hourly.

The script is currently running on a Debian (lenny) system but should work on ubuntu and Centos.

#!/bin/sh
#
# dump openemr db
#
# This script will create a backup of the openemr database
# and store it in LOCATION with the file format of
# FILENAMEPREFIX.FILENAMEDATE.FILENAMEHOUR.FILENAMESUFFIX
# for example at 2am on June 16 2010 the file would appear as
# /var/log/mysql/backup/mysql_openemr.167.02.dump.gz
#
# LOCATION=directory to store the backups
LOCATION=/var/lib/mysql/backup
#
FILENAMEPREFIX=”mysql_openemr”
FILENAMESUFFIX=”dump”
#
FILENAMEDATE=`date +%j`
FILENAMEHOUR=`date +%H`
#
FILENAME=${FILENAMEPREFIX}.${FILENAMEDATE}.${FILENAMEHOUR}.${FILENAMESUFFIX}
#echo $FILENAME
#
# KEEPDAYS=number of days to hold dump file before deletion
KEEPDAYS=7
OLDFILES=`expr ${FILENAMEDATE} – ${KEEPDAYS}`
#
# mysqldump options
OPTIONS=”–opt”
#
# GZIP=0 do not zip files
# GZIP=1 zip files
GZIP=0
#
# Username and Password for mysql
# change this for your installation
USERNAME=”myopenemrusername”
PASSWORD=”openemrdbpassword”
#
# dump the database
# zip the file if GZIP set
#
if [ ${GZIP} -ne 0 ]
then
mysqldump ${OPTIONS} –user=${USERNAME} –password=${PASSWORD} openemr | gzip >${LOCATION}/${FILENAME}.gz
else
mysqldump ${OPTIONS} –user=${USERNAME} –password=${PASSWORD} openemr >${LOCATION}/${FILENAME}
fi
if [ ${FILENAMEHOUR} -eq 0 ]
then
FILENAMES=`ls ${LOCATION}`
for fname in ${FILENAMES}
do
FDATE=`echo ${fname} | cut –delimiter=. -f 2`
if [ ${FDATE} -lt ${OLDFILES} ]
then
echo Deleting ${fname}
rm ${LOCATION}/${fname}
fi
done
fi

5 thoughts on “OpenEMR – Dump Database”

  1. Is there a similar process for hourly dumps for windows systems? and are there any written instructions for accomplishing the same results?

    Thanks,

    Michael

  2. Michael,
    our team does not have a script for Windows but someone on the OpenEMR forum may have accomplished something similar.

    This script is part of a much larger backup solution which copies the information to an off-site storage facility every night. The hourly backup is used as a “snapshot” in the event we need to roll back the database.

  4. The script is scheduled to backup each hour but it is not a requirement. A daily backup should be sufficient,however, we capture the database each hour to reduce the amount of potential data loss in the event of a system failure.

  5. Another (and much more efficient) way of deleting files.
    Replace the lines (below) at the end of the script

    if [ ${FILENAMEHOUR} -eq 0 ]
    then
    FILENAMES=`ls ${LOCATION}`
    for fname in ${FILENAMES}
    do
    FDATE=`echo ${fname} | cut –delimiter=. -f 2`
    if [ ${FDATE} -lt ${OLDFILES} ]
    then
    echo Deleting ${fname}
    rm ${LOCATION}/${fname}
    fi
    done
    fi

    With these lines

    if [ ${FILENAMEHOUR} -eq 6 ]
    then
    find ${LOCATION} -type f -mtime +${KEEPDAYS} -print -exec rm {} \;
    fi

Leave a Reply