WL#4116: Online Backup: Record Online Backup Progress

The system shall support storing of data describing all backup and restore 
operations in tables inside the MySQL database. This data will provide 
metadata about each operation. 

Overview
--------
There shall be two tables. One to hold information about each backup and 
restore and one to hold progress information. The first table is named 
online_backup and contains a single row for each backup or restore. The second 
table is named online_backup_progress and can contain multiple rows for each 
backup and restore operation.

Table online_backup
-------------------
One row for each operation is created at the start of backup or restore and 
updated as the operation progresses. The data in this table can be used to 
record a history of all backup and restore jobs run on the server. It can 
queried to provide detailed information about the operations or as a means to 
create a summary of the operations. The data is not removed from the table. The 
user would perform any table maintenance should she desire. 

CREATE TABLE IF NOT EXISTS online_backup (
 backup_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
    COMMENT 'Surrogate key for (near) duplicate entries', 
  process_id INT UNSIGNED NOT NULL
    COMMENT 'The process id that operation ran as',
  binlog_pos INT UNSIGNED DEFAULT 0
    COMMENT 'The recorded binlog position of backup/restore',
  binlog_file CHAR(64)
    COMMENT 'The recorded binlog filename at time of backup/restore',
  backup_state ENUM('complete', 'starting', 'validity 
point', 'running', 'error', 'cancel') NOT NULL
    COMMENT 'Status of current operation',
  operation ENUM('backup', 'restore') NOT NULL
    COMMENT 'Type of operation',
  error_num INT NOT NULL DEFAULT 0
    COMMENT 'The error from this run 0 == none',
  num_objects INT UNSIGNED NOT NULL DEFAULT 0
    COMMENT 'The number of objects in the backup',
  total_bytes BIGINT UNSIGNED 
    COMMENT 'The size of the backup repository in bytes',
  validity_point_time datetime
    COMMENT 'The time of the validity point.',
  start_time datetime
    COMMENT 'The date/time of start of operation',
  stop_time datetime
    COMMENT 'The date/time of end of operation',
  host_or_server_name CHAR (30)
    COMMENT 'The server name where operation ran',
  username CHAR (30)
    COMMENT 'The user name who ran the operation',
  backup_file CHAR (100)
    COMMENT 'The name of the file',
  user_comment VARCHAR (200) DEFAULT NULL
    COMMENT 'The comment from user entered at command line',
  command VARCHAR (512)
    COMMENT 'The command used to issue operation',
  engines VARCHAR (100)
    COMMENT 'The name of the storage engines used in the operation'
) ENGINE=MYISAM;


Table online_backup_progress
----------------------------
The system shall also support storing of progress data describing the steps in 
each operation. This data shall also be stored in a table within the MySQL 
database. Rows are added to this table over the course of the operation and are 
not updated. This allows the data to be used to track the progress of the 
current operation. The rows in the table represent a step in the operation and 
may contain informational statements, errors, and other pertinent information. 
The data in this table has a limited lifetime. At the start of each operation, 
the table is truncated and new data is added. The user should not need to 
maintain this data.

CREATE TABLE IF NOT EXISTS online_backup_progress (
  backup_id BIGINT UNSIGNED NOT NULL
    COMMENT 'Key for online_backup table entries',
  object CHAR (30) NOT NULL
    COMMENT 'The object being operated on',
  start_time datetime
    COMMENT 'The date/time of start of operation',
  stop_time datetime
    COMMENT 'The date/time of end of operation',
  total_bytes BIGINT
    COMMENT 'The size of the object in bytes',
  progress BIGINT UNSIGNED 
    COMMENT 'The number of bytes processed',
  error_num INT NOT NULL DEFAULT 0
    COMMENT 'The error from this run 0 == none',
  notes CHAR(100)
    COMMENT 'Commentary from the backup engine'
) ENGINE=MYISAM;


Limitations of this release
---------------------------
These tables can be extended by creating INFORMATION_SCHEMA views should they 
be required by the Merlin team and others. It has been decided that the 
information_schema views shall not be included in the first release and shall 
be added in a later release.

The access layer for these tables shall be implemented so that the burden of 
writing the data to the mysql tables is hidden from the caller. The primary 
caller shall be the online backup kernel for creating and updating entries in 
the online_backup table and the kernel and drivers for inserting rows in the 
online_backup_progress tables.

Table online_backup
-------------------
Access to the online_backup table shall include several methods. A special 
method is used to create a new row in the table. This is only called once at 
the start of every backup or restore operation. This method also returns the 
backup_id for the new row. The other methods may be called at any time. These 
methods result in an update to the existing row corresponding to the backup_id.

ulonglong report_ob_init(int process_id,
                    enum_backup_state state,
                    enum_backup_op operation,
                    int error_num,
                    char *user_comment,
                    char *backup_file,
                    char *command);

Note: Some values passed into this method are only set once at the beginning of 
the operation and may not include methods to update the values (they're not 
updated once set).

int report_ob_binlog_info(ulonglong backup_id,
                          int binlog_pos,
                          char *binlog_file);

Note: This method is typically only called once by may be called after the 
start of the operation.


int report_ob_error(ulonglong backup_id,
                    int error_num);

int report_ob_state(ulonglong backup_id,
                    enum_backup_state state);

int report_ob_num_objects(ulonglong backup_id,
                          int num_objects);

int report_ob_size(ulonglong backup_id,
                   longlong size);

int report_ob_time(ulonglong backup_id,
                   my_time_t start,
                   my_time_t stop);

int report_ob_vp_time(ulonglong backup_id,
                      my_time_t vp_time);

int report_ob_engines(ulonglong backup_id,
                      const char *engine_name);


Note: This method ignores values for start or stop that are NULL (only saves 
values provided so that it can be called once for start and once again later 
for stop).

These methods return 0 on success or non-zero on error.

The above methods (except for report_ob_init) are used throughout the backup or 
restore operation to update and existing row in the table.

Note: The enum types above may be replaced with the appropriately defined 
enumerations.

Table online_backup_progress
----------------------------
Access to the online_backup_progress table is via the single method 
report_ob_progress(). This method inserts a new row in the 
online_backup_progress table using the data passed as parameters. A primitive 
for this method is shown below.

int report_ob_progress(ulonglong backup_id,
                       char *object,
                       my_time_t start,
                       my_time_t stop,
                       longlong size,
                       longlong progress,
                       int error_num,
                       char *notes);

This method returns 0 on success or non-zero on error.


Additional Methods
------------------
The following methods are additional utility methods planned.

/*
  This method sums the size entries from the online backup progress rows
  for the backup operation identified by backup_id.
*/
ulonglong sum_progress_rows(ulonglong backup_id);

/*
  This method prints the summary information for the backup operation
  identified by backup_id.
*/
int print_backup_summary(THD *thd, ulonglong backup_id);


Implementation Planning
-----------------------
This functionality can be implemented in a similar manner as the mysql.event 
table. See event_db_repository.h/event_db_repository.h for details. This is a 
class that has examples of all operations needed. 

The plan is to use the event_db_repository class as a template for a new class 
named backup_progess and tailor the class to the needs of online backup. 

The parameters correspond to the columns in this table. 

The backup_id is a value that is returned by the initial call to create a row 
in the online_backup table. 

Note: The type for start and stop may be altered to conform to convention.

Implementation Details
----------------------
In order to overcome the problem of not being able to lock the tables, this 
worklog must make use of the be_thread code from an earlier work. However, it 
has been determined that this code is inadequate because it is designed for use 
in backup drivers. Some refactoring is needed to ensure the threading code is 
sufficiently separated from the driver code. This will require issuing two 
patches for this worklog. The first shall correct the design defects in the 
be_thread code and make any necessary alterations to the be_default and 
be_snapshot files. The second shall encompass the original intent of this 
worklog.