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