Backup Optimization

Scheduling of Restore Preparation

Technical challenge: restore time

Trade-off: increased ongoing overhead for backup processing

Backup data can be prepared for restore any time after it is produced and before it is used for restore. To expedite recovery, incremental backups can be pre-applied to the prior full backup to enable faster recovery. This may be done at the expense of recovery points, or at the expense of storage by maintaining copies of unmerged full and incremental backup directories.

Moving Restored Data

Technical challenge: disk space limitations

Trade-off: modification of backup directory contents

Suggested method for moving restored data is to use --copy-back as this method provides added safety. Where you might instead optimize for disk space savings, system resources, and time you may choose to instead use MariaDB Enterprise Backup's --move-back option. Speed benefits are only present when backup files are on the same disk partition as the destination data directory.

The --move-back option will result in the removal of all data files from the backup directory, so it is best to use this option only when you have an additional copy of your backup data in another location.

To restore from a backup by moving files, use the --move-back option:

# mariabackup --move-back --target-dir=/data/backups/full

Multi-threading

Technical challenge: CPU bottlenecks

Trade-off: Increased workload during backups

MariaDB Enterprise Backup is a multi-threaded application that by default runs on a single thread. In cases where you have a host with multiple cores available, you can specify the number of threads you want it to use for parallel data file transfers using the --parallel option:

# mariabackup --backup \
      --target-dir=/data/backups/full \
      --user=mariabackup \
      --password=mbu_passwd \
      --parallel=12

Incrementing an Incremental Backup

Technical challenge: Backup resource overhead, backup duration

Trade-off: Increased restore complexity, restore process duration

Under normal operation an incremental backup is taken against an existing full backup. This allows you to further shorten the amount of time MariaDB Enterprise Backup locks MariaDB Enterprise Server while copying tablespaces. You can then apply the changes in the increment to the full backup with a --prepare operation at leisure, without disrupting database operations.

MariaDB Enterprise Backup also supports incrementing from an incremental backup. In this operation, the --incremental-basedir option points not to the full backup directory but rather to the previous incremental backup.

# mariabackup --backup \
      --incremental-basedir=/data/backups/inc1 \
      --target-dir=/data/backups/inc2 \
      --user=mariabackup \
      --password=mbu_passwd

In preparing a backup to restore the data directory, apply the chain of incremental backups to the full backup in order. That is, first inc1/, then inc2/, and so on:

# mariabackup --prepare \
      --target-dir=/data/backups/full \
      --incremental-dir=/data/backups/inc1

# mariabackup --prepare \
      --target-dir=/data/backups/full \
      --incremental-dir=/data/backups/inc2

Continue to apply all the incremental changes until you have applied all available to the backup. Then restore as usual:

# mariabackup --copy-back --target-dir=/data/backups/full
# chown -R mysql:mysql /var/lib/mysql

Start MariaDB Enterprise Server on the restored data directory.

Storage Snapshots

Technical challenge: Backup resource overhead, backup duration

Trade-off: Limited to platforms with volume-level snapshots, may require crash recovery

While MariaDB Enterprise Backups produces file-level backups, users on storage solutions may prefer to instead perform volume-level snapshots to minimize resource impact. This storage capability exists with some SAN, NAS, and volume manager platforms.

Snapshots occur point-in-time, so no preparation step is needed to ensure data is internally consistent. Snapshots occur while tablespaces are open, and a restored snapshot may need to undergo crash recovery.

Just as traditional full, incremental, and partial backups should be tested, so too should recovery from snapshots be tested on an ongoing basis.