Automated Backups to an Alternate Server

From OpenEMR Project Wiki

Automated Backups to an Alternate Server

Introduction

While there are various backup methods suggested for OpenEMR, experience shows that the following considerations are important:

  • Backups should be automated. When there is a shortage of human resouces, backups are among the first things to fall by the wayside.
  • The restore process must be ready to go and periodically tested. Many things can go wrong with a backup procedure and an untested restore means an untested and possibly worthless backup.
  • Backups protect against events like vandalism, theft, fire, floods, equipment failure, malicious hacking and human error. The backup system must survive these events. Therefore it is important for it to be in a different physical location and to not be vulnerable to someone who hacks into the production system that is backed up.
  • Because human errors can propagate to backups before being discovered, multiple historical backups should be saved.
  • Because you cannot restore to a lost production server, it is ideal if the backup server can fill this role in an emergency.
  • Backups should take as little time and as little bandwidth as possible.

The General Approach

What we recommend is using an off-site server that is configured with the same or a similar operating system as the production server, and able to similarly support an OpenEMR installation (i.e. with Apache/PHP/MySQL and other required packages installed). It is assumed that you have installed OpenEMR before and are aware of what these requirements are.

This document will show the steps when using Linux servers, but a similar thing can be done with Windows servers if the required software is installed (details of that are not covered here).

We will use the "rsync" utility to copy over differential backups of the MySQL and web directories. "Differential" meaning that only the files or parts of files that have changed since the last backup are copied over the network.

Furthermore the backup server will initiate all connections used for backup. Thus the production server will not have direct access to the backup server.

There will also be a process in place for restoring a backup onto the backup server to produce a fully functional duplicate of the production installation. This process will allow for restoring either the latest backup or some selected previous backup.

The backup server's "cron" utility, or its equivalent, will be used to invoke a backup script at the times that are deemed sensible. Commonly this will be weekday evenings but may easily be more often.

Preparing for Automated Connections to the Production Server

Backups will be performed over an encrypted SSH connection to the production server. Public Key technology will be used so that human intervention is not required for authentication.

To support this make sure the openssh-server package installed on the production server. Then you can generate its public/private key pair for the root user with this command:

 sudo ssh-keygen -t rsa

Be sure to just hit Enter when prompted for a passphrase.

This will create a private key in "/root/.ssh/id_rsa" and a public key in "/root/.ssh/id_rsa.pub". What you want to do is copy this public key (NOT the private key!) to the production server and put it into a file there named "/root/.ssh/authorized_keys". There are various ways to do that but here's one example:

 sudo scp /root/.ssh/id_rsa.pub admin@my.production.server:/tmp/authorized_keys
 ssh admin@my.production.server
 sudo cat /tmp/authorized_keys >> /root/.ssh/authorized_keys
 exit

At this point the root user on the backup server will be able to log in seamlessly as the root user on the production server. So make very sure that the backup server is secure! Test the root login from the backup server like this:

 sudo ssh root@my.production.server

You may be prompted for your local password for sudo, but not for any password on the remote server.

Creating a Backup Script

Next, create a /root/bin directory and a file /root/bin/backup on the backup server. This is what will be executed periodically to retrieve backups. Here's an example of what should be in it:

 #!/bin/bash
 
 # TARGET is a directory that will store your backups. It should have plenty of space
 # available. Commonly this will be a separately mounted drive.
 TARGET=/root/backups
 
 # This is the number of historical backups to retain. This will be a compromise depending
 # on available storage space.
 NUMBACKUPS=14
 
 # Here is the hostname or IP address of the server to be backed up.
 FROM=my.production.server
 
 # This is a short name representing the server to be backed up.
 # It is used to name the local target directories and files.
 SHORT=myprod
 
 cd $TARGET
 
 # Delete the oldest allowed historical backup and rename the others to make room for a new one.
 rm -f  $SHORT.$NUMBACKUPS.tar.gz
 for ((I=NUMBACKUPS; I > 1; I=I-1)); do
   J=$((I-1))
   if [ -e "$SHORT.$J.tar.gz" ]; then
     mv $SHORT.$J.tar.gz $SHORT.$I.tar.gz
   fi
 done
 
 # Make the backup directory if this is the first time.
 if [ ! -d "$SHORT" ]; then
   mkdir $SHORT
 fi
 
 # Archive the previous backup with compression. This could take a while!
 tar zcpf $SHORT.1.tar.gz $SHORT
 
 # Synchromize the production server's database and web directories to the backup copies.
 rsync -axz --numeric-ids --del root@$FROM:/var/lib/mysql $TARGET/$SHORT
 rsync -axz --numeric-ids --del root@$FROM:/var/www       $TARGET/$SHORT

Once you are done creating the backup script, make it executable and then test it:

 sudo chmod a+x /root/bin/backup
 sudo /root/bin/backup

Allow plenty of time for this first run because all of the data for the databases and web directories will be copied over. Subsequent runs will take less time.

Scheduling Automated Backups

On the backup server you can edit /etc/crontab to schedule the backup runs. Append a line to the end of it something like this:

 05  21  *  *  *  root  /root/bin/backup

That will run the backup script at 9:05 pm every night.

Here's a similar example that backs up Monday thru Friday only:

 05  21  *  *  1-5  root  /root/bin/backup

This one backs up at 12:05 pm and 11:05 pm every weekday:

 05  12,23  *  *  1-5  root  /root/bin/backup

Type "man 5 crontab" for more information as to the format of these entries.

Restoring a Backup

Restoring should be done periodically for testing and verification of the backups, and to make sure you are prepared for an emergency.

To restore the last backup directly onto the backup server, do something like this:

 sudo su -
 service mysql stop
 cd /var/lib
 mv mysql mysql.original
 cp -a /root/backups/myprod/mysql .
 chown -R mysql:mysql mysql
 service mysql start
 mysql_upgrade -p
 service apache2 stop
 cd /var
 mv www www.original
 cp -a /root/backups/myprod/www .
 service apache2 start
 exit

The "mysql_upgrade" step may be unnecessary, in which case it will fail and you can ignore it.

If you want to restore a previous backup, you can do the same thing except first extract the compressed backup to a scratch directory and be sure to copy from there.

After you are done with the restored site, you can clean up the system to its original state:

 sudo su -
 service mysql stop
 cd /var/lib
 rm -rf mysql
 mv mysql.original mysql
 service mysql start
 service apache2 stop
 cd /var
 rm -rf www
 mv www.original www
 service apache2 start
 exit

Tweaking

If you are concerned about the MySQL databases being changed while their backup is in progress, the important thing to know is that you can execute commands on the remote system with ssh.

So for example you can bring down the MySQL server before running rsync with a command like this in the backup script:

 ssh root@$FROM 'service mysql stop'

and then after the rsync command:

 ssh root@$FROM 'service mysql start'

Alternatively you could run a command to dump just the OpenEMR database to a temporary file with mysqldump, and then back that up. Of course in that case the restore procedure would be different.