Backups 12.x

Owned by Marc Dumontier
Feb 21, 2017
9 min read

A backup strategy is essential to protect your data from loss. This is set up automatically with a deb install of Oscar 12_1 but can also be installed manually for older installations. There is also a script to mirror the server.



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 Oscar12_1 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. Second is 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.  This can take some time and should be run off 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 – May 7, 2010
  • v1.1 – added logging to backup script, restoring backup, and tomcat 5.x instructions. May 9, 2010
  • v1.2 – ported to new.oscarmanual.org. June 26, 2010
  • v1.3 – altered file name in script to reflect order of operations. Jan 16, 2012
  • v1.3 – added commented out option for an incremental document backup. April 14, 2012
  • v1.4 – Updated to reflect that the script is now installed by deb automatically. Nov 4th, 2012
  • v1.5 – Added script to mirror the server. May 12, 2013

This document is copyright by Peter Hutten-Czapski 2010 -2013 © 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. Restoring the backup
  5. Non Standard Installations
  6. Backup Servers


Prerequisites

It is assumed that 

  1. A default Ubuntu/Tomcat6 based installation of OSCAR older than Oscar 12_1 build 29. 
  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

First form the backup directories and ensure that OSCAR can access it

sudo mkdir /usr/share/OscarMcmaster/backup
sudo mkdir /usr/share/OscarMcmaster/backup/archive
sudo chown -R tomcat6:nogroup /usr/share/OscarMcmaster/backup
sudo chmod -R 755 /usr/share/OscarMcmaster/backup

Using a text editor open a blank file to contain the provided script.

A note about the following command – ‘vi’ (for visual editor) is a classic unix editor. First time users usually find other Linux editors easier to use particularly ‘nano’, if you are running in a terminal environment, and ‘gedit’, if you are running with a GUI. If you elect to use an alternative editor, replace ‘vi’ in the commands that follow with your editor of choice.

sudo vi $CATALINA_BASE/backup.sh



Copy and paste the text below into backup.sh remembering to replace ****** with your actual MySQL database password :

#!/bin/sh
# backup.sh
# a script file for OSCAR that copies compressed archives
# that have been date stamped for easy sorting
# to the destination folder usually where a browser can access
# (it should match backup_path in your oscar.properties)
# and once a month to an archive folder
# from a given MySQL database and documents folder

SRC=/var/lib/tomcat6/webapps
DEST=/usr/share/OscarMcmaster/backup
ARCH=/usr/share/OscarMcmaster/backup/archive
DBSE=oscar_mcmaster
DBPWD=***********
DOCS=OscarDocument
LOG_FILE=/usr/share/OscarMcmaster/backup/daily.log
LOG_ERR=/usr/share/OscarMcmaster/backup/daily.err

# --- log the running of the script
echo "#########" `date` "#########" 1>> $LOG_FILE
echo "#########" `date` "#########" 1>> $LOG_ERR

# --- set local variables to today's date and time
YR=$(date +%Y)
MN=$(date +%m)
DY=$(date +%d)
NOW=$(date +%H%M%S)
cd $SRC

# --- check if we are in the correct directory
if [ "$(pwd)" != "${SRC}" ] ; then
  echo "$0: couldn't change directory to ${SRC}" 1>> $LOG_ERR
  echo "No backup made !" 1>> $LOG_ERR
  exit 100
fi

# --- create a sql file of the database
mysqldump ${DBSE} -uroot -p${DBPWD} > OscarBackup.sql

# --- for incremental backups delete the metadata file to force a complete backup on the first of the month
# if [ "01" = "${DY}" ] ; then
#	rm usr.snar 2>> $LOG_ERR
# fi

# --- compress up the output and the document directory
tar -czf OscarBackup.tar.gz OscarBackup.sql  2>>$LOG_ERR
tar -pczf OscarDocumentBackup.tar.gz ${DOCS}/  2>>$LOG_ERR
#tar -pczg usr.snar -f OscarDocumentBackup.tar.gz ${DOCS}/  2>>$LOG_ERR  #incremental backups

# --- encrypt the files
openssl enc -aes-256-cbc -salt -in OscarBackup.tar.gz -out OscarBackup.tar.gz.enc -pass pass:${DBPWD}  2>>$LOG_ERR
openssl enc -aes-256-cbc -salt -in OscarDocumentBackup.tar.gz -out OscarDocumentBackup.tar.gz.enc -pass pass:${DBPWD}  2>>$LOG_ERR
rm -f OscarDocumentBackup.tar.gz  2>>$LOG_ERR
rm -f OscarBackup.tar.gz  2>>$LOG_ERR

# --- every month archive one day worth of the backups
# (this is set for the first day of the month, but you can change this)
if [ "01" = "${DY}" ] ; then
      if [ -d "${ARCH}" ] ; then
       echo "preparing last month's backup for the archival directory" 1>> $LOG_FILE
      else
       echo "the archival directory doesn't exist so we create ${ARCH}" 1>> $LOG_ERR
       mkdir "${ARCH}"  2>>$LOG_ERR
      fi
      mv -f ${DEST}/????-??-${DY}* ${ARCH}/ 2>>$LOG_ERR
fi

# --- remove last months backup from this date
# (you can comment out the following if you need more than a months backups)
for FILE in ${DEST}/????-??-${DY}* ; do
      rm -f ${FILE}  2>>$LOG_ERR
done

# --- copy today's tar gziped files to the backup directory
# --- options for "cp"
# -p preserves ownership, permissions, time etc
OPTIONS="-p"
for FILE in *gz ; do
  cp ${OPTIONS} ${FILE} ${DEST}/${YR}-${MN}-${DY}-${NOW}-${FILE}  1>> $LOG_FILE 2>>$LOG_ERR
done
echo "....done" 1>> $LOG_FILE

 

Save and exit the editor and make the file executable by root and not readable by anyone else (it has a plain text password!)

sudo chmod 700 $CATALINA_BASE/backup.sh

We’ll configure Linux to run the command every day. This is done by creating a cron job for the script we just made. Run the following command to open an editor for the crontab file:

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 * * * /var/lib/tomcat6/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 created in the /home/mysql/ directory and 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 /home/mysql/

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

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

Assuming you have not deviated from the above script, de-encrypt the backup with the following command.  Eg for the backup files created on May 5th, 2012 the parameters will be

openssl enc -d -aes-256-cbc -salt -in 2013-06-03-230102-OscarBackup.tar.gz.enc -out OscarBackup.tar.gz 
openssl enc -d -aes-256-cbc -salt -in 2013-06-03-230102-OscarDocumentBackup.tar.gz.enc -out OscarDocumentBackup.tar.gz 


 

The program will ask for your password which will be the same as the one you supplied for ********** in the backup script.  You will then want to decompress the files

tar -xzf OscarBackup.tar.gz
tar -pxzf OscarDocumentBackup.tar.gz


 

Then restore the database (this step will overwrite the data and CANNOT be undone)

mysql -uroot -p******* oscar_mcmaster < OscarBackup.sql

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.  One approach to this 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 500 of them with one table each) can be run sequentially, not any faster, but at least you can follow the progress.
(again assuming oscar_mcmaster 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_mcmaster <$f &
	wait $!; 
done

Non Standard Installations

The above instructions assume you are running Tomcat 6 on Ubuntu installed with apt-get.  If you are not, you can still use the script but you may need to change a few things.

First determine who (which user) is running Tomcat on your system

ps aux | grep tomcat


It won't be root (it shouldn't in any case or you have a security problem).  Adjust the chown command above to this user.  For example if you get tomcat55

sudo chown -R tomcat55:nogroup /home/mysql


Then check your installation for the location of the webapps

echo $CATALINA_HOME


Use this information to correct the SRC in the script for your installation.  For example if you get /usr/local/tomcat then you should adjust the SRC in the script to be

SRC=/usr/local/tomcat/webapps

Backup Servers (Optional)

While the default deb installation will give you the material to rebuild a OSCAR server, 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 MySQL 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.

First install another OSCAR server (the backup) using the standard instructions

Second open a text editor on your backup server and type (or paste) in the following, adjusting the values at the beginning of the script to match the IP user  and MySQL passwords for the REMOTE (ie main OSCAR server) and the LOCAL (ie backup server)

#!/bin/sh
# synchdb.sh
# a script file for OSCAR to episodically mirror data to a backup server
# this presupposes that you have setup key pair to run ssh without a password
# and this key pair is installed in the roots .ssh directory
# NOTE: this file must be run by ROOT to preserve file ownerships during rsync

#==============================================================
# Copyright Peter Hutten-Czapski 2013 released under the GPL v2
#==============================================================

REMOTE_DOC_DIR=/var/lib/tomcat6/webapps/OscarDocument/oscar_mcmaster/
LOCAL_DOC_DIR=${REMOTE_DOC_DIR}
REMOTE_IP=nnn.nnn.nnn.nnn
REMOTE_USER=user
REMOTE_DBSE=oscar_12_1
LOCAL_DBSE=${REMOTE_DBSE}
REMOTE_DB_PASS=*************
LOCAL_DB_PASS=${REMOTE_DB_PASS}
LOCAL_PORT=3310
REMOTE_SSH_PORT=22
SRC=/usr/share/OscarMcmaster/backup
LOG_FILE=${SRC}/daily.log
LOG_ERR=${SRC}/daily.err

# --- log the running of the script
echo "#########" `date` "#########" 1>> $LOG_FILE
echo "#########" `date` "#########" 1>> $LOG_ERR

# --- set local variables to today's date and time
YR=$(date +%Y)
MN=$(date +%m)
DY=$(date +%d)
NOW=$(date +%H%M%S)

cd $SRC

# --- check if we are in the correct directory
if [ "$(pwd)" != "${SRC}" ] ; then
  echo "$0: couldn't change directory to ${SRC}" 1>> $LOG_ERR
  echo "No backup made !" 1>> $LOG_ERR
  exit 100
fi

# --- delete any errent running ssh
killall ssh 2>>$LOG_ERR

# --- remove the previous database backup if exists
if [ -f ${LOCAL_DBSE}.sql.enc ]; then
	rm ${LOCAL_DBSE}.sql.enc 2>>$LOG_ERR
fi

# --- synchronise the documents directory
rsync -avz --progress --rsh='ssh -p69' $REMOTE_USER@$REMOTE_IP:${REMOTE_DOC_DIR} ${LOCAL_DOC_DIR} 2>>$LOG_ERR
echo "Documents rsync-ed" 1>> $LOG_FILE

# --- create a forked ssh process that runs in the background to connect to the remote OSCAR server
ssh -f -p $REMOTE_SSH_PORT $REMOTE_USER@$REMOTE_IP -L $LOCAL_PORT:localhost:3306 -N -S /tmp/ssh-control 2>>$LOG_ERR

# --- create a sql file of the remote database
mysqldump -P $LOCAL_PORT -h 127.0.0.1 -uroot -p${REMOTE_DB_PASS} ${REMOTE_DBSE} -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B > ${LOCAL_DBSE}.sql 2>>$LOG_ERR

# --- load it to the local database
mysql -uroot -p${LOCAL_DB_PASS} < ${LOCAL_DBSE}.sql 2>>$LOG_ERR
echo "Database synched" 1>> $LOG_FILE

# --- encrypt the files
openssl enc -aes-256-cbc -salt -in ${LOCAL_DBSE}.sql -out ${LOCAL_DBSE}.sql.enc -pass pass:${LOCAL_DB_PASS}  2>>$LOG_ERR
rm -f ${LOCAL_DBSE}.sql  2>>$LOG_ERR

# --- drop the forked ssh process that runs in the background by reference to its socket
ssh -S /tmp/ssh-control -O exit $REMOTE_IP 2>>$LOG_ERR

echo "....done" 1>> $LOG_FILE

Save and exit the editor and make the file executable by root and not readable by anyone else (it has a plain text password!)

sudo chmod 700 synchdb.sh

We’ll configure Linux to run the command every day. This is done by creating a cron job for the script we just made. Run the following command to open an editor for the crontab file:

sudo crontab -e

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

# m h dom mon dow command
55 23 * * * /var/lib/tomcat6/synchdb.sh