Store backup history on the server
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.
- Alexey Kopytov
- George Ormond Lorch III
- George Ormond Lorch III
- Series goal:
- Accepted for 2.2
- Milestone target:
- Started by
- George Ormond Lorch III on 2013-09-08
- Completed by
- Alexey Kopytov on 2013-11-05
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_
/* 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
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.
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_
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.
--history must be specified only on the innobackupex command line and not within a .cnf file in order to be effective.
- The backup user will need CREATE privileges in order to create the PERCONA_
- The backup user will need INSERT privileges in order to add history records to the PERCONA_
- The backup user will need SELECT privileges in order to use --incremental-
* Blueprints in grey have been implemented.