The right way to regularly backup an OpenEMR

Day-to-Day Users
1

fkasmani wrote on Wednesday, November 07, 2012:

Hello,

I have an OpenEMR installation we’ve been using for about 6mths now, without any form of backups and it’s really fearful, knowing how “PC issues” come up anytime and from anywhere.

For this reason, we’ve signed up for a remote (online) data backup service and would like regular backups of our OpenEMR installation (preferably daily).
The method we’d prefer to use is th rsync incremental backup.

However, my concerns are about the mySQL databases involved. At first I was thinking that we could take up the whole c:\xampp folder and thereafter do incremental backups of the same daily, but I was alerted that this would not be the right method and instead the right way to go would be to do daily dumps of the mySQL database(s). (comment’s pls)

This has really got me confused and while dumping of the DB’s would only secure the DB’s, what about the rest of the stuff - the base application, documents, etc?

While rsync can be scheduled as a windows cron job, can dumping of the database(s) be scheduled the same way?

What would be the right way forward, pls?

2

tmccormi wrote on Wednesday, November 07, 2012:

You need to do both.   On a timed basis before the offline backup starts dump the database into a location that the rsync will pickup.    I suggest naming the dump with a datatime stamp, like openemr_backup_20121105-241317.sql  or similar
Tony
www.mi-squared.com / @tonymi2
oemr.org / @OEMR_org

3

sunsetsystems wrote on Wednesday, November 07, 2012:

Rsync is a fine tool for backup.  The most important thing to keep in mind is that testing the restore process is mandatory.  If you have not done that successfully, then your backups cannot be trusted.  You’d be surprised how easily things can go wrong.

A database dump is more portable, but I think since you’re backing up the mysql distribution also then it’s fine to do it all at the filesystem level.  Again, the proof is in the restore.  That should be tested on a regular basis.

Rod
www.sunsetsystems.com

4

cverk wrote on Wednesday, November 07, 2012:

I just made a command script that shuts down apache and mysql, runs the encrypted sync backup of the entire xampp folder to the amazon cloud and then restarts apache and mysql.  I set up windows scheduler to run it nightly.  I also used Truecrypt to make two encrypted USB drives to do similar backups to, taking one home and leaving the other in the machine. So I feel pretty safe with 1 onsite, 1 offsite and 1 cloud backup daily.

5

drjccaldwell wrote on Friday, January 04, 2013:

Cverk,
Would you consider posting the script (minus your details of course) as many of us are working on getting solid back up going. Your method may help me and a number of others.
thanks

6

hitechelp wrote on Friday, January 04, 2013:

Has this scenario been addressed for Ubuntu based systems?

7

juggernautsei wrote on Friday, January 04, 2013:

I am going to jump in on this conversation as i have done something like this.

Here is my script that is working great. I added the feature of creating a folder for each day so that we get an incremental backup was the thinking. Because I have had the problem of having something go wrong and it has been a few days before catching it and the backup has the error in it so I had no clean backup. Now I keep two weeks of incremental data from the file level

#Batch file to backup East Practice every day data backup

for /f “tokens=1* delims=” %%a in (‘date /T’) do set datestr=%%a
mkdir F:\Client"%datestr%"                                #This creates a new folder using the current date

net stop wampapache       #stopping the apache and mysqld service
net stop wampmysqld

xcopy %WAMP%bin\mysql\mysql5.5.8\data\east F:\Client"%datestr%"
#using xcopy to grab the file level transfer of files of to an external drive

net start wampmysqld
net start wampapache     #restarting both service

I still do a structural dump of the database once a month. And do a mysqldump once a week.
Can’t have too many backups. All of this together has saved me many times over.

Sherwin
openmedpractice.com

8

penguin8r wrote on Friday, January 04, 2013:

For Ubuntu based systems, there is a handy linux utility called mysqlbackup (available here on Sourceforge), which will make time stamped daily, weekly, monthly backup snapshots of your MySQL database(s).
That, in conjunction with whatever backup solution you choose to use, will take care of everything.
I like a software package called Lone-Tar, but that is not FOSS, it’s a pay to play product.

9

bradymiller wrote on Saturday, January 05, 2013:

Hi,

Please feel free to place you backup strategies and script on the OpenEMR wiki at:
http://www.open-emr.org/wiki/index.php/OpenEMR_Backup_Tools
The more options/methods listed, the better.

-brady
OpenEMR

10

cverk wrote on Saturday, January 05, 2013:

Here is a script for backing up from drive N to drive M such as you would use to back up to a usb thumb drive.  I use truecrypt encrypted USB drives.

net stop “mysql”
net stop “apache2.4”
cmd /c cd N:\
if exist N:\check.bat xcopy N:\xampp\*.* M:\xampp\ /d /e /c /i /f /h /k /y
net start “apache2.4”
net start “mysql”

11

cverk wrote on Saturday, January 05, 2013:

The amazon cloud one is a little more complex but looks like this.

net stop “mysql”
net stop “apache2.4”
SET DGTOOLS_ACCESS_KEY=xxxxxx
SET DGTOOLS_SECRET_KEY=xxxxxx
SET DGTOOLS_ENCRYPTION_PASSWORD=xxxxx
SET DGTOOLS_DECRYPTION_PASSWORD_0=xxxx
SET DGTOOLS_DECRYPTION_PASSWORD_1=xxxx
cd c:\Dragon
start /wait dgsync.exe -z -e aes-256-cbc “C:/xampp/” “s3://bucket name/xampp/”
net start “apache2.4”
net start “mysql”

Below is a link to the freeware program I put in a folder at C:\dragon with dgsync.exe in it, and then set the script to run nightly with windows scheduler.  There are linux instructions at the link as well. This was not very original on my part, more like just putting together available tools. You have to set up an amazon S3 account and get the key numbers from that account, but it is really pretty cheap.

http://www.dragondisk.com/faq/31-faq-dragondisk/84-how-to-synchronize-files-using-dgsync-command-.html

12

pfwilliams wrote on Monday, January 07, 2013:

I didn’t find once-a-day really sufficient, but also realized that kicking all the users out of OpenEMR multiple times a day (as required when using the mysqldump.exe utility) is not going to make me popular.

I’ve been running the following batch file for a few weeks now without issue. Five times a day, without interrupting the users,  it uses the DOS XCOPY command to backup the /sites folder, where installation specific settings and custom forms are located, and it uses the mysqldbexport.exe utility to do live backups of the database. It also creates a pass/fail log entry each time it’s run.

rem @ECHO OFF
SET TODAY=%date:~10,4%-%date:~4,2%-%date:~7,2%
SET HH=%TIME:~0,2%
IF "%HH:~0,1%" == " " SET HH=0%HH:~1,1%
SET MM=%TIME:~3,2%
SET sourcepath=\\OpenEMR\xampp\htdocs\openemr\sites
SET destpath=\\Nas1\Backup\OpenEMR
SET xopt=/i /e /c /f /h /k /y
XCOPY %sourcepath%\*.* %destpath%\ %xopt%
SET WORKBENCH=C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE
SET PYTHONHOME=%WORKBENCH%\Python
SET PYTHONPATH=%WORKBENCH%\Python;%WORKBENCH%\Python\DLLs;%WORKBENCH%\Python\Lib;%WORKBENCH%\Python\mysql_libs.zip;%WORKBENCH%\Python\Tools\Scripts
CD %WORKBENCH%\Utilities
mysqldbexport --server=root:pass@openemr:3306 --bulk-insert --export=both openemr>MySQL_Backup_%TODAY%_%HH%-%MM%.sql
Copy MySQL_Backup_*.* %destpath%\
if %errorlevel% neq 0 goto :error
Echo %TODAY% %HH%:%MM% - Backup Created >>%destpath%\MySQL_Backup.log
DEL MySQL_Backup_*.sql
Exit
:error
Echo %TODAY% %HH%:%MM% - Error Creating Backup >>%destpath%\MySQL_Backup.log

This is intended to be run on the server (as a scheduled task) and requires that MySQL Workbench be installed on the server to make the mysqldbexport.exe utility available.  I believe that mysqldbexport.exe is also available in some SDK? It would be cool to have it integrated into the main OpenEMR installation as it is much more useful than the older mysqldump utility.  I’ve been too busy to go back and make some enhancements and/or refinements that I have in mind, and my once-tested restore batch file is even rougher than this one, so I don’t really consider this a final product, but it appears to work.

The (Workbench/SDK) mysqldbcopy.exe utility also seems useful. I made a batch file similar to that above can clone our production database , then overlay a few files, creating an exact-copy test environment in about 30 seconds. It’s also probably not-ready-for-prime-time. 

Once I pretty-up the scripts, I’ll post them on the wiki where Brady suggested.

13

pfwilliams wrote on Monday, January 07, 2013:

Oops! I forgot to mention, I also had to install Python 2.7 on the server to get the above to work.
This was my first effort, which is working presently, but it’s certainly possible there was an easier way to skin this cat.
Someday, time permitting, I plan on doing a “version 2”.

14

fkasmani wrote on Sunday, January 13, 2013:

I’m looking at the method outlined at http://www.open-emr.org/wiki/index.php/Backing_Up_Using_the_Built_In_Tools#Via_the_CLI and have a cron to run it (on Ubuntu server) and then have another cron to rsync-over-ssh this to a remote data backup service on the net.

Is it absolutely necessary that Apache/mySQL or whatever services be shut down prior to dumping the database?

Also, if someone just depends on dumping the mySQL database (maybe daily) and does not actually backup the OpenEMR directory. Should there be a crash of the PC/Server, is it possible to get OpenEMR up & running to it’s previous state just from the mySQL database dump?

15

keithlofstrom wrote on Sunday, January 13, 2013:

Openemr newbie, backup oughta-know-better here.  I am setting up OpenEMR at my wife’s clinic;  we are using it for schedules and startiing to enter patient encounters.  Our dependence and investment in the data is increasing fast.

I also host dirvish, a scheduling wrapper for rsync.  Disk-to-disk backups with hardlinking. Dirvish is “legacy software” (not much improvement, no known remaining bugs) used by many individuals and large organizations.  The linux kernel and mozilla are backed using a customized version of dirvish.  I’ve got restorable nightly backups going back 7 years, including nightly onsite and offsite backups for the OpenEMR server.

Backups are step one.  Thinking through restores are actually the most important issue.  I’ve written some extra scripts to rebuild the drives for specific machines.  There is no fully automatic way to do this, because the reasons for restoring a hard drive can vary.  System failure?  Hard drive failure? Security breach?  Rogue program?  Major user error?  Which parts of which backups to combine to create a new disk requires situational awareness and decision making.  Staging the backup to reduce impact on operations is important.

Today I am rebuilding the OpenEMR server with a solid state drive, an Intel 530 series 120GB which some claim is the most reliable single-drive storage per dollar available.   We originally built around a WD 2GB Green hard disk, but that turns out to be way more storage than we need.  I will create a second identical drive, which will live offsite and get daily backups.   I tweaked DNS and apache so main instance of openemr is accessable on the network with the url “emr”, and the backup will be similarly accessable by a different short name.

Well and good, but if I am out of town and our backup sysadmin is busy, it could be days before we can merge the instances (how???).  Secondly, Murphy’s Law warns us that when we do have a failure, it will be after a day of entering data, some of which (like patient encounters) will be difficult to recreate.

So, my question on this topic is how we can record a log file of the day’s activities, in sufficient detail that we can review it and then play it back onto a backup image to bring it up to date?

The best outcome would be that as soon as office staff knew there was a failure, they could log into the backup instance of openemr and just keep going, perhaps redoing the page they were working on when it failed, but no more than that.

16

keithlofstrom wrote on Sunday, January 13, 2013:

A related issue to my previous post mentioning dirvish;

DIrvish uses rsync, and rsync backups are magical.  Every new backup can hardlink to the old backup, meaning that files that do not change occupy no extra room on the backup drive (beyond their directory entry).  The files are segmented and only the parts of a file that change get moved over the net.  I back up 10gB machines halfway across the country in minutes this way.

However, hardlinking can’t be used on files that have even minor changes;  the backup disk needs disk space for every file that changes daily.   By using “logrotate”, old log files are dated and over time expired on the source drive, and daily logs are incremental.  The backup drive does not fill with many nearly-identical 10MB log files every day. 

Dirvish stores a lot of mysql databases in /var/lib/mysql/openemr/, and the largest is log.MYD, which looks like it could be the playback file I need to update a backed-up instance of openemr.   This file may be key to graceful system restore.  But as it is, it keeps growing in size over time, and the amount of backup drive space it uses grows as the square of time.  It would be great if we could do a logrotate of this file with every backup, and if we could edit and playback a day’s transactions during an extended system restore.  Then we could play back a similar log generated during the use of the “emergency” emr, and we will not lose any more data than the windows active when the system crashed.

MySQL experts, please help us understand what these files do, and how we might develop really robust recovery methods.  All EMR software systems can fail, but systems that recover quickly are far more valuable in a crisis than systems that need hours or days of work to partly recover.   An open source platform with transparent code and data structures is inherently easier to design robust recovery for, and that could be our greatest asset.

17

keithlofstrom wrote on Sunday, January 13, 2013:

Gads, I wish sourceforge was editable. 

Staging the restore to reduce impact on operations is important.

We originally built around a WD 2TB Green hard disk.

18

bradymiller wrote on Tuesday, January 15, 2013:

Hi,

If you wanted a minute-by-minute back up (in addition to your daily back up), then suggest that all entries in the log table and things in the ‘sites/’ get instantaneously mirrored somewhere else. The log table should be storing all sql queries that modify the database (see the comments column) and the sites/ directories is where the patient docs end up. So with these elements should be able to figure out how to fully restore it up to the minute (would be really nice to have a script that did this; ie. could convert the log entries into sql queries). Please note this is just theoretical; don’t think this has been tested.

-brady
OpenEMR

19

bradymiller wrote on Tuesday, January 15, 2013:

In addition, I’m guessing users take advantage of the mysql replication feature for their back up needs.

20

fkasmani wrote on Tuesday, January 15, 2013:

Hi,

I’d like to share the direction we’re moving to, at our end.
With Ubuntu server minimal, we make dedicated “OpenEMR servers”. These have atleast 2 similar drives of 250GB each, based on RAID1 (strictly setup at time of Ubuntu install on a clean machine).
Then use AutoMySQLBackup http://www.howtoforge.com/creating-mysql-backups-with-automysqlbackup http://www.linux.com/learn/tutorials/659107-how-to-do-painless-mysql-server-backups-with-automysqlbackup (for more information) to do daily mid-night dumping. 2hrs after AutoMySQLBackup (that would be about 2AM daily), a cron runs an RSync-over-SSH command to (incremental) backup the latest AutoMySQLBackup dump plus the entire OpenEMR directory, to a remote data backup service (we keep 5 instances remotely).

Would appreciate views and comments on this.

Regards.