The purpose of this work is to improve current situation around backups by implementing a SQL command that makes a backup of the running server to a mounted directory path:

BACKUP SERVER TO '/mnt/backup';

This statement will create the requested target directory and fill it with a consistent snapshot of the database. Additionally, the directory will contain a file backup.cnf, which includes some variables that are specific to restoring the backup.

To prepare a backup (analogous to mariadb-backup --prepare), one would invoke a command like the following:

mariadbd --defaults-extra-file=/mnt/backup/backup.cnf --datadir=/mnt/backup

This step is optional. Alternatively, one could just move the backup to its final storage location and start up MariaDB Server on it.

The file backup.cnf will at the very least contain the new parameters backup_include and backup_exclude, which are regular expressions that specify which files were excluded or included in a partial backup. These parameters will be sampled at the start of BACKUP SERVER. They allow us to suppress errors about missing files that had been excluded.

We may assume that when log_bin is enabled, binlog_storage_engine=innodb (MDEV-34705) will be enabled as well. In this way, there should be no need to include a binlog position or GTID in the backup.cnf.

Storage engine interface

Most of the actual work is done by the storage engines. There will be new handlerton functions as follows:

• backup_start
  • MyISAM, RocksDB: no-op; everything is done at backup_end
  • InnoDB, Aria: create a work queue of files that must be copied
  • InnoDB: enables innodb_log_archive=ON for the duration of the backup
• backup_step
  • Return value: number of remaining work queue entries, or negative on error
  • InnoDB, Aria: process a file from the engine specific work queue
    • prevent races between copying files and writing into them
  • this may be executed from multiple threads
  • in MDEV-38362, this would be provided an output stream instead of a backup directory path
• backup_end
  • the final phase, BACKUP STAGE BLOCK_COMMIT (MDEV-5336)
  • MyISAM: copy all data files
  • Aria: finish copying the log
  • InnoDB: determine the end LSN of the backup, hard-link all ib_*.log files to backup
  • RocksDB: create hard links as in rocksdb_create_checkpoint
• backup_finalize after BACKUP STAGE END released locks
  • InnoDB:
    • copy and trim the last 0 to 2 “hot” ib_*.log files
    • if backup_start had set innodb_log_archive=ON:
      • restore innodb_log_archive, innodb_log_file_size
      • delete any ib_*.log files that had been created outside the backup directory
    • in MDEV-38362: stream the ib_*.log files
  • RocksDB:
    • in MDEV-38362, stream the files that were hard-linked in backup_end

The streaming backup (MDEV-38362) would likely make use of a working directory for the backup, to facilitate efficient copying of the innodb_log_archive=ON files as well as any ENGINE=RocksDB files.

Notes on Incremental Backup

Unlike in mariadb-backup, there is no separate BACKUP SERVER syntax for incremental backup. Incremental backup is based on copying and applying all InnoDB log records since the previous backup.

For the duration of the backup execution, the server will be configured with innodb_log_archive=ON (MDEV-37949). We will avoid copying any data files created after innodb_lsn_archived, because those files can be reconstructed based on log records.

Creating an Incremental Backup

If the server is permanently configured with innodb_log_archive=ON, then BACKUP SERVER command will skip copying any InnoDB data files, relying solely on the archived log files.

The backup.cnf file in the BACKUP SERVER output contains the following options that are related to preparing an incremental backup:

• innodb_log_recovery_target
  • the end LSN of the backup
  • when this is set, InnoDB will run in read-only mode, analogous to xtrabackup --apply-log-only
• innodb_log_recovery_start
  • the starting point of InnoDB recovery (instead of starting from the latest checkpoint)
  • this is the value of the status variable innodb_lsn_archived at the start of the backup
• a comment with a value of log_sys.end_lsn
  • this should be the innodb_log_recovery_start in the output of the next incremental backup
  • identifies the last checkpoint when the backup finishes

Because innodb_log_archive=ON only fully covers InnoDB DML operations, invoking the BACKUP SERVER command is mandatory when it is possible to execute DDL operations or DML on non-InnoDB tables. However, each invocation of BACKUP SERVER will create a new directory, and it is up to the user to merge the output from multiple backup directories for the purpose of restoring an incremental backup.

Preparing an Incremental Backup

The normal InnoDB recovery would start from the last available log checkpoint. However, in an incremental backup, we must apply all log that has accumulated after the last applied log checkpoint, which must be specified by the parameter innodb_log_recovery_start (MDEV-37949).

One could make multiple incremental backups on top of a full backup and apply them either in one go or one by one. The InnoDB log is idempotent, so one can specify the same innodb_log_recovery_start each time, and each time have all the log to be applied in one go.

It is more efficient to apply the next batch of log from where the previous run left off. That is, for applying the subsequent batch, we should set innodb_log_recovery_start to the value of the last checkpoint that was available when the previous incremental backup ended.