Skip to content
English
Customer portal
Contact us
  • There are no suggestions because the search field is empty.

02.05 Database Backup and Restore

Back up the SimpleRisk database with mysqldump, back up config.php and the uploads directory alongside it, restore by recreating an empty database and loading the dump. Plus the built-in cron-driven backup feature SimpleRisk ships with.

Why this matters

Every operational decision in this chapter depends on a working backup. The upgrade rollback path is the backup. The disaster-recovery path is the backup. The "we accidentally bulk-deleted production data" recovery is the backup. A SimpleRisk install without a tested backup strategy is a SimpleRisk install one bad operation away from data loss.

The honest scope to know: SimpleRisk's database holds the load-bearing data — the risk register, the control catalog, the document library, the incident history, the audit trail, the user accounts, the per-user customizations. The application code is replaceable (re-download a release tarball or re-pull a Docker image); the data isn't. Backup strategy focuses on the database and the small number of file-system artifacts that are install-specific (config.php, the uploads directory).

The other thing worth knowing: a backup you haven't tested isn't a backup. The backup is real only when you've successfully restored from it (in a test environment, on a fresh install) and confirmed the restored install works. Untested backups produce false confidence — operators believe they can recover when they actually can't. Periodic restore tests (quarterly is reasonable) are part of the backup discipline.

The third thing: SimpleRisk has a built-in backup mechanism driven by simplerisk/cron/cron_backup.php and the backup_auto, backup_path, backup_schedule, backup_remove settings. It produces zip files of the application and database at a configurable cadence. This is one option for backups; for production deployments, an external mysqldump-based pattern usually fits better with the rest of the operations team's backup tooling.

Before you start

Have these in hand:

  • A SimpleRisk install with a database the operator can reach.
  • MySQL admin credentials (or at minimum, a user with the privileges to dump and restore the SimpleRisk database).
  • A backup destination — an off-host location with appropriate access controls. The backup must NOT live only on the SimpleRisk host (a host failure that destroys the SimpleRisk data also destroys the backup). Common destinations: object storage (S3, Azure Blob, GCS), an internal backup server, a managed backup service.
  • A retention policy — how many backups to keep, for how long. Compliance requirements often dictate this (PCI DSS, HIPAA, SOC 2 all have data-retention implications).
  • A test-restore environment for exercising the backup periodically.

Step-by-step

Path A: Manual mysqldump-based backup (recommended for production)

This is the standard pattern most operations teams already know.

Take a backup

mysqldump \
    -u 
  
    \ -p
   
     \ --single-transaction \ --routines \ --triggers \ --events \ simplerisk > simplerisk-backup-$(date +%Y%m%d-%H%M%S).sql

Flag explanations:

  • --single-transaction — runs the dump inside a single transaction (for InnoDB tables, which SimpleRisk uses) so the dump is consistent without locking the application out. Use this for online backups during normal operation.
  • --routines — includes stored procedures and functions in the dump. SimpleRisk doesn't use stored procedures heavily, but include for safety.
  • --triggers — includes table triggers in the dump.
  • --events — includes scheduled events in the dump.

The output file is plain SQL (CREATE DATABASE, CREATE TABLE, INSERT INTO) and can be restored with mysql < backup.sql. Compress for storage:

gzip simplerisk-backup-*.sql

Back up the config.php and uploads directory alongside

tar czf simplerisk-files-$(date +%Y%m%d-%H%M%S).tar.gz \
    /var/www/simplerisk/includes/config.php \
    /var/www/simplerisk/uploads/

(Adjust paths to your install.)

config.php is small (a few KB) but contains the database credentials. The uploads directory can be large (depending on how much evidence the program has accumulated). Include both in the file backup; without config.php the database backup is restoreable but the application can't reach it; without the uploads directory, the restored install loses every uploaded evidence file.

Move the backups off-host

Upload the SQL backup and the file backup to your backup destination:

aws s3 cp simplerisk-backup-*.sql.gz s3://your-backup-bucket/simplerisk/
aws s3 cp simplerisk-files-*.tar.gz s3://your-backup-bucket/simplerisk/

(Substitute your cloud / on-premise backup tooling.)

The backup must leave the SimpleRisk host. A backup that stays on the same disk as the source isn't a backup; a single disk failure or volume corruption destroys both.

Schedule the backup

The recurring backup is a cron entry on the SimpleRisk host (or wherever the backup script runs from):

# Daily database backup at 2:30 AM
30 2 * * * /usr/local/bin/simplerisk-backup.sh

Where simplerisk-backup.sh is a script that does the dump, the file tarball, and the upload. Test the script manually before relying on the cron.

Restore from the backup

To restore:

  1. Provision a fresh SimpleRisk install (or recover the existing one to a clean state).
  2. Restore config.php: bash tar xzf simplerisk-files- .tar.gz -C / (The tarball's paths are absolute, so this restores the files to their original locations.)
  3. Restore the uploads directory: handled by the same tar xzf above.
  4. Restore the database: bash gunzip -c simplerisk-backup- .sql.gz | mysql -u root -p simplerisk
  5. Verify the application loads and the data is correct.

For new installs (recovering to a fresh SimpleRisk that doesn't have the database yet), create the database and user first:

mysql -u root -p <
  
   '; GRANT SELECT, INSERT, UPDATE, ALTER, DELETE, CREATE, DROP, INDEX, REFERENCES ON simplerisk.* TO 'simplerisk'@'localhost'; EOF

Then restore the database as above. The config.php restore brings the credentials forward; the database restore brings the data forward; the file restore brings the uploads forward.

Path B: SimpleRisk's built-in backup feature

SimpleRisk ships with a built-in backup mechanism driven by the cron job simplerisk/cron/cron_backup.php. When backup_auto is set to 'true' in the settings table, the cron job invokes do_backup() to produce a zip of the application and a zip of the database at the configured cadence.

Configure the built-in backup

The relevant settings (in the settings table):

  • backup_auto — boolean. Set to 'true' to enable automated backups.
  • backup_path — directory where backup files are written. Defaults to /backup-simplerisk .
  • backup_schedule — schedule string (e.g., 'daily').
  • backup_remove — number of backups to keep before pruning old ones.

These can be configured through the admin UI under Configure → Settings or directly in the settings table.

Backup files produced

Each backup run produces two files:

  • An application zip — a snapshot of the SimpleRisk application files.
  • A database zip — a mysqldump-equivalent of the database.

The files are recorded in the backups table:

SELECT id, random_id, timestamp, app_zip_file_name, db_zip_file_name FROM backups ORDER BY timestamp DESC;

Each row has a random_id (a 50-character token used as the file naming basis), a timestamp, and the file names of the two zip files.

Restore from a built-in backup

To restore from a built-in backup:

  1. Locate the backup files in backup_path.
  2. Extract the application zip over the SimpleRisk install (preserving config.php per the bare-metal upgrade pattern in Upgrading on Bare Metal).
  3. Extract the database zip and load the SQL into MySQL with the standard mysql < backup.sql pattern.

The mechanics are similar to Path A; the difference is that SimpleRisk produced the backups rather than the operator's external tooling.

Trade-offs of the built-in backup

The built-in backup is convenient but has limitations:

  • Backups land on the SimpleRisk host's local disk by default. They need to be moved off-host to satisfy backup-must-leave-host discipline.
  • The backup paths are install-specific — a backup taken on host A doesn't restore cleanly onto host B without path adjustment.
  • The retention is a simple count, not a time-window policy. backup_remove says "keep N backups"; it doesn't say "keep at least one daily backup for 30 days plus weekly backups for a year."
  • The mechanism doesn't integrate with managed-backup services out of the box.

For production deployments, the manual mysqldump-based pattern (Path A) usually fits better with the operations team's backup tooling. The built-in mechanism is appropriate for evaluation deployments and small installations where the convenience outweighs the limitations.

Common pitfalls

A handful of patterns recur with backup and restore.

  • Backups that never leave the host. The most common failure mode. The backup script writes to local disk; the backup is "happening" but the local disk is the same disk as the source. A host loss destroys both. Always upload the backup off-host as part of the same script that takes it.

  • Backups that haven't been tested. The second most common failure mode. The cron job runs nightly; the operator believes they have backups; the first restore attempt during an actual incident reveals the backups don't actually restore (they're truncated, they're encrypted with a lost key, the format is wrong). Periodic restore tests catch these failures before they matter; quarterly is reasonable.

  • Backing up the database without config.php. Restoring the database to a fresh install without config.php means the application can't reach the restored database. Both belong in the backup; both need to be restored together.

  • Backing up config.php to a different location than the database backup. The two should travel together. A scenario where the database backup is current but the config.php backup is from six months ago means the credentials in config.php don't match the credentials the database expects.

  • Forgetting to back up the uploads directory. Evidence files, document attachments, signed-off forms — all live in simplerisk/uploads/. The database has references to these files but not the files themselves. Restore the database without the uploads directory and every link to an evidence file is broken.

  • Backups with no retention policy. Backups accumulate forever; storage fills up; eventually the cron starts failing because the destination is out of space, but the failure isn't surfaced. Set a retention policy (delete backups older than N days, keep N daily / N weekly / N monthly), automate the pruning, and monitor the backup destination's free space.

  • Encrypted backups with the encryption key stored on the source host. A backup encrypted with a key that lives only on the SimpleRisk host loses the decryption capability when the host loses. Keep the encryption key elsewhere — a key-management service, a separate operator's password manager, multiple copies in geographically-distributed locations.

  • Database backups that don't include routines or triggers. A mysqldump without --routines --triggers --events dumps tables and data but not the procedural code. SimpleRisk doesn't use stored procedures heavily, but include the flags for safety in case future versions add them.

  • Locking the application during backup. A mysqldump without --single-transaction against InnoDB tables works but locks tables during the dump, blocking concurrent reads. For online backups during business hours, always use --single-transaction.

  • Treating snapshot-based backups (volume snapshots, EBS snapshots) as a substitute for database dumps. A volume snapshot taken while MySQL is running may capture an inconsistent state (mid-transaction, half-flushed buffers). For real backups, use the database's native backup tools; volume snapshots are for whole-system-state recovery, not for application-level data backup.

Related

Reference

  • Permission required: Server-side: MySQL credentials with dump/restore privileges; filesystem read on simplerisk/includes/config.php and simplerisk/uploads/. SimpleRisk-side: check_admin for changing the built-in backup settings via UI.
  • API endpoint(s): GET /api/upgrade/backup/db (orchestrated database backup, used in the upgrade workflow); GET /api/upgrade/backup/app (orchestrated application files backup).
  • Implementing files: simplerisk/cron/cron_backup.php (the built-in backup cron); the do_backup() function (in simplerisk/includes/) for the built-in backup execution; simplerisk/extras/upgrade/includes/api.php for the v2 API backup endpoints.
  • Database tables: backups (records of built-in backup runs: id, random_id, timestamp, app_zip_file_name, db_zip_file_name).
  • config_settings keys: backup_auto (boolean — enable automated backups); backup_path (filesystem path for backup files; defaults to /backup-simplerisk ); backup_schedule (cadence string like 'daily'); backup_remove (count of backups to keep before pruning).
  • External dependencies: mysqldump for the manual backup pattern; off-host backup destination (S3, Azure Blob, GCS, internal backup server, etc.); appropriate cloud-provider backup tooling for managed databases.