Introduction

This document presents a recommended backup strategy for the Enterprise Trial server, designed to reduce performance impact by eliminating table and filesystem locks, as well as to achieve consistent backups according to the type of data being backed up. Though the issue at hand is the ETrial server, this recommendations can be easily applied to other servers.

ProcessMaker data is divided in two sets:

Structured data: this corresponds to data stored according to ProcessMaker's relational database in MySQL.
Unstructured data: this corresponds to data stored directly in the filesystem, including the application core files (code, configuration); application extensions and complements (plugins, translation files); and user files (case uploads, logos, etc.).

In order to take a consistent backup, a "point in time snapshot" of both structured and structured data must be taken.

Structured data (Database) backup

Ensuring consistent database backup is crucial. In case of disaster, an inconsistent database backup will result in unrecoverable data. And while the ETrial server is not meant to run production workloads, such a scenario could leave a bad impression on our prospective customers, specially those considering a cloud deployment.

Therefore, the current hot, file-level backup performed by remotely rsync-ing the /var/lib/mysql directory, in its current form, is not the best approach, since the on-disk files of a database do not necessarily represent a consistent state of the database, unless the database is shutdown, or proper steps have been taken on it.

With this in mind, and in order to obtain a consistent database backup, there are two full, online backup approaches we will discuss:

Get a full MySQL database dump and backing up the resulting SQL sentences plain file.
Force the database to sync all its data to disk to a consistent state and defer any other writing until a file-level backup has been taken.

Both approaches are discussed below. An additional approach for incremental backups exists, and is described in http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html.

Logical, online database dump backup

A logical backup for all MySQL databases can be taken by using the mysqldump --all-databases utility. This approach is the simplest and easiest one, and provides a consistent, plain-text output that can be easily understood, archived and restored when required. It however, has two major drawbacks:

Being a logical backup, it is slower both to backup and restore.
For certain storage engines, it may lock tables, affecting performance. While for small tables this is not an issue, since the lock is held for a short period of time, on larger tables it can become problematic and make the application unusable.

For small-database scenarios, this approach can be the easiest one to implement and is therefore recommended. For larger databases, however, a consistent, physical backup, as discussed below, may be the best way to go.

Consistent, online physical backup

A file-level or physical backup can be made to be consistent by taking adequate steps prior to taking it. They include telling the RDBMS to flush any pending write operations to disk, and to defer any new write operations until after the backup has been taken. This approach may be faster since neither the data model nor the data itself need to be serialized to SQL DDL and MDL sentences. Restore is fast as well, since the backup does not need to be fed to the RDBMS, but simply replaced before the MySQL daemon is started.

Unfortunately, this approach has its pitfalls as well:

If the on-disk database size is considerable, this may mean that the RDBMS has to withhold write operations for far too long. If a crash happens before the backup is completed, all those write operations would not be written, thus resulting in loss of data.
As with the logical backup scenario, deferring write operations means locks can be held for too long, causing performance loss.

The previous issues, however, can be easily overcome by making use of other Linux features, namely, the Logical Volume Manager - LVM. The backup strategy would become as follows:

The RDBMS is made to sync pending writes to disc, and to defer any new/incoming write operations until the backup is taken.
The filesystem in which the database data is stored is synced to disk.
A snapshot of the logical volume backing the filesystem in which the database data is stored, is taken.
The RDBMS is told to continue normal operations, as if the backup had already been taken. Thus, performance impact due to locking the database and deferring writes is minimized
The snapshot is mounted.
A file-level backup is taken from the mounted snapshot.

Thus, a way to achieve this would be the following:

Example of consistent, online physical MySQL backup script

#!/bin/bash
LV=/dev/vg00/root
SS_NAME=backup_snapshot
SS_LV=$(dirname $LV)/$SS_NAME
SS_MDIR=/mnt/$SS_NAME
SS_SIZE=1g
BU_PATH=/backups/$SS_NAME

MY_USER=root
MY_PASS=al3ph512

mysql -u root --password=al3ph512 <<- MYSQL
FLUSH TABLES WITH READ LOCK;
\! sync
\! /sbin/lvcreate --size $SS_SIZE --snapshot --name $SS_NAME $LV
UNLOCK TABLES;
MYSQL

mount $SS_LV $SS_MDIR
rsync -av --delete $SS_MDIR/var/lib/mysql $BU_PATH/
umount $SS_MDIR
/sbin/lvremove -f $SS_LV

The above script makes the following assumption:

MySQL data is stored in logical volume root of volume group vg00.
MySQL data is stored in directory /var/lib/mysql, located on the root of the data logical volume.
Volume group vg00 has enough physical extents as to create the snapshot of logical volume root.

Unstructured data (application and files) backup

Unstructured data backup is far simpler, since risks of consistency issues is far less. However, if there is a large number of files to backup, it can happen that, by the time a certain file is backed up, its contents have changed with respect to when the backup was started. Therefore, a snapshot based backup strategy is recommended as well:

Snapshot based files backup

#!/bin/bash
SRC=/opt/processmaker
LV=/dev/vg00/root
SS_SIZE=1g
BU_PATH=/backups/$SS_NAME  
SS_NAME=file_backup_snapshot
SS_LV=$(dirname $LV)/$SS_NAME
SS_MDIR=/mnt/$SS_NAME

sync
/sbin/lvcreate --size $SS_SIZE --snapshot --name $SS_NAME $LV
mount $SS_LV $SS_MDIR
rsync -av --delete $SS_MDIR/$SRC $BU_PATH/
umount $SS_MDIR
/sbin/lvremove -f $SS_LV