Backups

Preface

Any production use of OSCAR should be accompanied by a backup strategy that protects the data.  Here we suggest a two pronged approach.

  1. First, as an essential minimum, is simply a rotating compressed encrypted backup delivered to the network share of your choice accessible through OSCAR Admin.  The following describes the backup strategy automatically installed with Oscar15 when installed by deb.  Dynamically changing files will be backed up including the database and all downloaded files.  This ensures a complete backup of the “chart” in case of system failure.  The archive file of the uploaded files will become quite large over time, so we’ll only keep a month of copies on hand at any given time.  As the files generated are sitting on a network share or the server itself you will still need to arrange to have the backups periodically downloaded to removable media (eg disc tape or USB) and taken off site for storage.
  2. Network shares are at risk of ransom ware and physical disasters such a fire.  Storage of an encrypted backup created as above onto one or several USB keys that the staff take off site is an inexpensive way to limit those risks.
  3. Consider the option of having a mirrored server.  This level of complexity may be overkill for a small office, but its not difficult to arrange.  The documents are rsync-ed across to the new server which yields an efficient and functional backup of the part of OSCAR’s data that quickly becomes the largest aspect of the regular backup as only newer files are copied over.  The database is also overwritten with a new copy in the middle of the night.  Loading the authors backup takes 3 hours.  The script can also serve as a basis for a test server where new configurations or versions of OSCAR meet the hard realities of real production data.

Document Version History

  • v1.0 – initial public release on oscarmanual.org – Feb 18, 2017
  • v1.1 – some corrections and updates – Sept 24, 2017
  • v2.0 – initial WorldOscar.org release for OSCAR 19 – Jan 28. 2020
  • v2.1 – advice about openSSL versions – Sept 16, 2020

This document is copyright by Peter Hutten-Czapski 2017-20 © under the Creative Commons Attribution-Share Alike 3.0 Unported License.

Contents

  1. Preface
    1. Document Version History
  2. Prerequisites
  3. The Backup Script
  4. The Backup files
  5. Restoring the backup
  6. Non Standard Installations
  7. Backup Servers

Prerequisites

It is assumed that

  1. A default Ubuntu/Tomcat based installation of OSCAR 15 or 19.
  2. You have a basic level of Linux knowledge
  3. You can open a Linux terminal
  4. You can use a Linux text editor
  5. You can cut and paste EXACTLY the following instructions

NOTE: Firefox will copy with Control+C while a linux terminal requires Shift+Control+V for paste

The Backup Script

The DEB installs a backup script to /usr/share/oscar-emr and sets up a cron job for root to run it.  You can check the jobs running for root as:

sudo crontab -e

And add to the existing file an entry so that the command looks like below

# m h dom mon dow command
01 23 * * * /usr/share/oscar-emr/backup.sh
You’ve now configured a daily backup of the mySQL OSCAR database and the documents folders. This backup will run at 1 minute after 11 pm (2301h), each evening. Each backup file will be time stamped based on the date and time it was created.

You will be able to see the contents of the backup directory with the command:

sudo ls /usr/share/oscar-emr/backup/

These backup files can also be viewed and downloaded via the Admin->oscarDatabase/Document Download tab from within OSCAR.

Backed up Files

By default the backup will be loaded on the server in an encrypted and compressed fashion.

You can access the backup files at Admin > Data Management > Database/Document Download

There will be four types of file

  1. the backup.log is a plain text file that logs running of the backup.  If things are not running check this file first for errors.
  2. the Full backups such as Full.OscarBackup.oscar_15.2020-02-01-23:01:01.tar.gz.enc
    which are a complete backup of the oscar database,oscar.properties file, ministry files, HRM, eform images, documents…
  3. the document backup that has the documents to date since the full backup OscarBackup.oscar_15.2020-02-18-23:01:01.Document.tar.gz.enc.
  4. the database backup such as OscarBackup.oscar_15.2020-02-18-23:01:01.Database.tar.gz.enc that has the complete current backup as of that date

 

Restoring the backup

In order to restore the backup you need to know all the encoding details that were selected during the encryption process otherwise you get an unreadable binary file.  The relevant details are

  • the cipher used
  • the password used (plus the initial vector if applicable)
  • if the file was salted (and the salt string if no key was provided)
  • the format (eg encoded with -base64) and the padding
  • if the documents were stored in a incremental backup
  • the version of OpenSSL used to encrypt (older openSSL may not be able to decrypt a file made with a newer version)

Assuming you have not deviated from the default, de-encrypt the backup with the following command.  Eg for the backup files created on Feb 18th, 2020 the parameters will be

openssl enc -d -aes-256-cbc -salt -in Full.OscarBackup.oscar_15.2020-02-01-23:01:01.tar.gz.enc -out FullOscarBackup.tar.gz 
openssl enc -d -aes-256-cbc -salt -in OscarBackup.oscar_15.2020-02-18-23:01:01.Document.tar.gz.enc -out OscarDocumentIncrementalBackup.tar.gz
openssl enc -d -aes-256-cbc -salt -in OscarBackup.oscar_15.2020-02-18-23:01:01.Database.tar.gz.enc -out OscarDatabaseBackup.tar.gz
The program will ask for your password which will be the same as the one you supplied for ********** for MySQL .  You will then want to decompress the latest database file
gunzip OscarDatabaseBackup.tar.gz 
 Then restore the database (this step will overwrite the data and CANNOT be undone)
mysql -uroot -p******* oscar_15 < OscarBackup.sql

For documents you need to decompress the remaining files.

tar -pxzf FullOscarBackup.tar.gz
tar -pxzf OscarDocumentIncrementalBackup.tar.gz

Test that you can open the backup and read the contents before you depend on this script. Windows™ and OS X™ equivalents to openssl and tar are available to test the integrity of the backup on those systems with details available elsewhere.

http://gnuwin32.sourceforge.net/packages/gtar.htm

http://www.slproweb.com/products/Win32OpenSSL.html

Large Backup

Over time the OscarBackup.sql grows to multi gigabyte proportions.  The above mysql load is very slow and may even stall or appear to stall while waiting several hours for the command prompt to reappear.  One can be patient but if your system is resource challenged it is possible that the backup fails.  One approach in these circumstances is to split the backup into multiple files with something like

awk '/^DROP/{close("file"f);f++}{print $0 > "file"f}' OscarBackup.sql

The files : file file1 file2 and so on (there will be about 600 of them with one table each) can be run sequentially, not any faster, but at least you can follow the progress.
(again assuming oscar_15 is the name of the schema and that ***** is the password for mysql)

#!/bin/bash

# loadMysql.sh
# loads a sequence of SQl files named file, file1 etc into the Oscar Database 

# you can split OscarBackup.sql into individual tables with
# awk '/^DROP/{close("file"f);f++}{print $0 > "file"f}' OscarBackup.sql

for f in file*; 
do 
	echo "processing $f";
	mysql -uroot -p******** oscar_15 <$f &
	wait $!; 
done

Synching to Remote Server (Optional)

While the default deb installation will give you the material to rebuild a OSCAR server, the backup will reside on the server you are trying to protect.  You need to take this data off the server to allow for recovery from catastrophic server failure (fire, flood, hard-drive failure, theft, ransomware).  This can be automated with the backup script supplied (direction on how to set it up to do this lies within the backup script)

Backup Servers (Optional)

It is good form to have a backup server ready to go in case of a failure on your “usual” machine.  Here we offer the following script that will episodically mirror the server.  Alternately a continuously mirrored database server can be setup using instructions provided by either MariaDB or MySQL (they differ in replication strategy) but will still need synchronisation of downloaded files which are stored outside the database.

NOTE: A backup server strategy supplements and does not replace the first method that gives you backups that you can download to disc or tape.  To whit if the problem is data corruption, you will still need an historic pre-corrupt backup to restore function.

#!/usr/bin/env bash
# run as root
# Script loads documents and schema from last days OSCAR work
# Need to load keys once to allow for this
# edited March 10, 2017 to add ; to ensure that the commands are executed sequentially
# edited Aug 2017 to add more information to backup and to exclude the backup script itself
# edited Sept 2017 when rsync compression was failing to compress in ssh -C instead

DB=oscar_15
DBPWD=************
SCRIPT=/usr/share/oscar-emr/
REMOTE_USER=peter
REMOTE_IP=<numerical lan ip>
LOG_ERR=/usr/share/oscar-emr/backup/load_backup_err.log

function getTimeStamp() {
        date '+%F-%T' 2>> $LOG_ERR
}
	echo "Backup Loading Script Running at `getTimeStamp` ...." 1>> $LOG_ERR
	cd ${SCRIPT}
	echo "In ${SCRIPT} now at `getTimeStamp`"
	sudo rsync -aW --exclude 'oscar/oscar_backup.sh' --progress 
--chown=tomcat7:tomcat7 --rsh='ssh -C -p22' 
${REMOTE_USER}@${REMOTE_IP}:/usr/share/oscar-emr/OscarDocument /usr/share/oscar-emr/ 2>> $LOG_ERR ;
	echo "Synched at `getTimeStamp`" 1>> $LOG_ERR
	# remove but don't error if not found
	rm -f /usr/share/oscar-emr/OscarDocument/oscar/OscarBackup.sql 2>> $LOG_ERR ;
	echo "Cleaned up at `getTimeStamp`"
DATE_TIME="`getTimeStamp`"
	gzip -d /usr/share/oscar-emr/OscarDocument/oscar/OscarBackup.sql.gz 2>> $LOG_ERR ;
cp /usr/share/oscar-emr/OscarDocument/oscar/OscarBackup.sql.gz /usr/share/oscar-emr/backup/OscarBackup${DATE_TIME}.Database.tar.gz.enc.sql.gz
	echo "decompressed it at` getTimeStamp`"
	sudo service tomcat7 stop  2>> $LOG_ERR ;
	echo "Stopped Tomcat at `getTimeStamp`" 1>> $LOG_ERR
	mysql -uroot -p${DBPWD} ${DB} < /usr/share/oscar-emr/OscarDocument/oscar/OscarBackup.sql 2>> $LOG_ERR;
	echo "Loaded it at `getTimeStamp`" 1>> $LOG_ERR
	# mysql -uroot -p${DBPWD} ${DB} < /usr/share/oscar-emr/patch1.sql 2>> $LOG_ERR ;
	mysql -uroot -p${DBPWD} ${DB} < /usr/share/oscar-emr/patch.sql 2>> $LOG_ERR ;
	echo "Patched it at `getTimeStamp`"
	mysql -uroot -p${DBPWD} ${DB} < /usr/share/oscar-emr/disableHRM.sql 2>> $LOG_ERR ;
	echo "Disabled HRM at `getTimeStamp`"
	sudo service tomcat7 start  2>> $LOG_ERR ;
	echo "Restarted Tomcat at` getTimeStamp`"
	echo "....Backup Loading Script Finished at `getTimeStamp`" 1>> $LOG_ERR