Database - Hourly Dumps

Day-to-Day Users
1

nthurman wrote on Wednesday, June 16, 2010:

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.

While this script is useful in creating snapshots of your database the usual cautions apply:
1) It is not a backup - regular on and off site storage is also required
2) it is only the database - modifications to the code, stored images, system updates are not backed up
3) it does not ensure data loss - you have a window of 1 hour (if the database is hosed) or 1 day (if you backup off site nightly)

the script is below but when we make changes it will be posted to www.openhealthit.info/category/tools/

Lack of tabs removes the indentation (sorry) .

#!/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
let OLDFILES=${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
then
mysqldump ${OPTIONS} -user=${USERNAME} -password=${PASSWORD} openemr | gzip >${LOCATION}/${FILENAME}.gz
else
mysqldump ${OPTIONS} -user=${USERNAME} -password=${PASSWORD} openemr >${LOCATION}/${FILENAME}
fi
if
then
FILENAMES=`ls ${LOCATION}`
for fname in ${FILENAMES}
do
FDATE=`echo ${fname} | cut -delimiter=. -f 2`
if
then
echo Deleting ${fname}
echo rm ${fname}
fi
done
fi

2

nthurman wrote on Wednesday, June 16, 2010:

As a note.   The fourth line from the bottom is commented out (echo).  This should be removed if you want to automatically delete files at midnight.

3

bradymiller wrote on Thursday, June 17, 2010:

hey,
It would be great if you’d post this on a new wiki page here:
http://www.openmedsoftware.org/wiki/OpenEMR_Backup_Tools
-brady

4

octort wrote on Tuesday, February 15, 2011:

root@ubuntu:/var/www# ./hrbackup.sh

When I run the script I get an error:

./hrbackup.sh: 25: let: not found
mysqldump: Got error: 1045: Access denied for user ‘root’@‘localhost’ (using password: NO) when trying to connect

Any ideas? I have Ubuntu 10.04, OpenEMR 3.2, I can access MySQL with no problem thru the Terminal.

thanks!

5

yehster wrote on Wednesday, February 16, 2011:

You need to edit your script to specify the password for mysqldump.
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_password

6

octort wrote on Thursday, February 17, 2011:

I can run the script from the Terminal with no problem but if I pu it on etc/cron.hourly it doesn’t create the file every hour.
any ideas?

7

octort wrote on Thursday, February 17, 2011:

Finally, the script works!
If I put the script in cron.hourly folder it doesn’t work on Ubuntu 10.04
so, I installed the scheduler and then it works.

8

nthurman wrote on Thursday, March 31, 2011:

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}`  

9

octort wrote on Thursday, March 31, 2011:

thanks!