Store backup history on the server

Registered by Alexey Kopytov on 2012-10-24

It may be useful in many circumstances to store backup history on the server itself in a special table created by XtraBackup. It may contain timestamps, backup type, LSNs and possibly other useful backup metadata.

Blueprint information

Status:
Complete
Approver:
Alexey Kopytov
Priority:
High
Drafter:
George Ormond Lorch III
Direction:
Approved
Assignee:
George Ormond Lorch III
Definition:
Approved
Series goal:
Accepted for 2.2
Implementation:
Implemented
Milestone target:
milestone icon 2.2.1-alpha1
Started by
George Ormond Lorch III on 2013-09-08
Completed by
Alexey Kopytov on 2013-11-05

Whiteboard

In order to store XtraBackup history on the server, we need to create an InnoDB table with some useful fields. MEB uses the CSV storage engine but we could find no compelling reason to follow this choice.

CREATE TABLE PERCONA_SCHEMA.xtrabackup_history (
 /* Unique backup id */
 uuid VARCHAR(40) NOT NULL PRIMARY KEY,

 /* User provided name of backup series. There may be multiple entries with the same name used to identify related backups in a series. */
 name VARCHAR(255) DEFAULT NULL,

 /* Name of tool used to take backup */
 tool_name VARCHAR(255) DEFAULT NULL,

 /* Version of tool used to take backup */
 tool_version VARCHAR(255) DEFAULT NULL,

/* Version of the xtrabackup binary used to take backup */
ibbackup_version VARCHAR(255) DEFAULT NULL,

/* Server version */
mysql_version VARCHAR(255) DEFAULT NULL,

 /* Exact command line given to the tool with --password and --encryption_key obfuscated */
 tool_command TEXT DEFAULT NULL,

 /* Time at the start of the backup */
 start_time TIMESTAMP NULL DEFAULT NULL,

 /* Time at the end of the backup */
 end_time TIMESTAMP NULL DEFAULT NULL,

 /* Amount of time, in seconds, spent calling and holding locks for FTWRL */
 lock_time BIGINT UNSIGNED DEFAULT NULL,

 /* Binlog file and position at end of FTWRL */
 binlog_pos VARCHAR(128) DEFAULT NULL,

 /* lsn at beginning of backup which can be used to determine prior backups */
 innodb_from_lsn BIGINT UNSIGNED DEFAULT NULL,

 /* lsn at end of backup which can be used as the starting lsn for the next incremental */
 innodb_to_lsn BIGINT UNSIGNED DEFAULT NULL,

 /* Full or partial backup */
 partial ENUM('Y', 'N') DEFAULT NULL,

 /* Incremental */
 incremental ENUM('Y', 'N') DEFAULT NULL,

 /* Description of result format */
 format ENUM('file', 'tar', 'xbstream') DEFAULT NULL,

 /* Compact backup */
 compact ENUM('Y', 'N') DEFAULT NULL,

 /* Compressed */
 compressed ENUM('Y', 'N') DEFAULT NULL,

 /* Encrypted */
 encrypted ENUM('Y', 'N') DEFAULT NULL
) ENGINE=innodb;

There will be four new options added to the innobackupex script:
 --history=<name> : Enables the history feature and allows the user to optionally specify a backup series name that will be placed within the history record.
 --incremental-history-name=<name> : Allows an incremental backup to be made based on a specific history series by name. innobackupex will search the history table looking for the most recent (highest to_lsn) backup in the series and take the to_lsn value to use as it's starting lsn. This will be mutually exclusive with --incremental-history-uuid, --incremental-basedir and --incremental-lsn. If no valid lsn can be found (no series by that name) innobackupex will return with an error.
--incremental-history-uuid=<uuid> : Allows an incremental backup to be made based on a specific history record identified by uuid. innobackupex will search the history table looking for the record matching uuid and take the to_lsn value to use as it's starting lsn. This will be mutually exclusive with --incremental-basedir, --incremental-lsn and --incremental-history-name. If no valid lsn can be found (no record by that uuid or missing to_lsn), innobackupex will return with an error.

The xtrabackup_history table will be manipulated exclusively from within the innobackupex script.

One new sub will be added to innobackupex: 'sub update_history'. Upon backup completion, this sub will check for the existence of a PERCONA_SCHEMA and an xtrabackup history table and create them if necessary. It will then add a new record to the xtrabackup_history table. This sub will also create a file within the backup set called xtrabackup_history that will contain all of the same information as a backup record in the form of '<column> = <value>.' This file will be streamed/stored exactly like xtrabackup_checkpoints.

Once a history record has been added, the record uuid will be written to stderr in a line that reads similar to:
  innobackupex: Backup history record uuid ### successfully written
Where ### will be the new history record uuid.

It should be noted that the backup record for the _current_ backup being performed will NOT exist in the xtrabackup_history table within the resulting backup set as the record will not be added to that table until after the InnoDB backup has been taken.

As part of the feature documentation, we should remind users that if they want access to this history outside of their backup set in the case of some catastrophic event, they will need to either perform a mysqldump, partial backup or SELECT * on the history table _after_ innobackupex completes and store the results with their backup set.

KAlexey: this is closely related to bug #1133017, i.e. users may want to include some of the fields from the history table (e.g. server and xtrabackup versions) into the backup itself in the form of a metadata file. I think it is reasonable implement such a metadata file and merge the contributed patch from bug #1133017 as a part of this task.

Limitations:
 --history must be specified only on the innobackupex command line and not within a .cnf file in order to be effective.
 --incremental-history must be specified only on the innobackupex command line and not within a .cnf file in order to be effective.

Doc Notes:
- The backup user will need CREATE privileges in order to create the PERCONA_SCHEMA.xtrabackup_history schema.table.
- The backup user will need INSERT privileges in order to add history records to the PERCONA_SCHEMA.xtrabackup_history table or otherwise must specify --no-history.
- The backup user will need SELECT privileges in order to use --incremental-history-name or --incremental-history-uuid in order for the feature to look up the PERCONA_SCHEMA.xtrabackup_history.innodb_to_lsn.

(?)

Work Items

Dependency tree

* Blueprints in grey have been implemented.

This blueprint contains Public information 
Everyone can see this information.