Skip navigation links

Additional languages
MySQL 6.0 Reference Manual :: 6 Backup and Recovery :: 6.3 Using BACKUP DATABASE and RESTORE :: 6.3.3 BACKUP DATABASE and RESTORE Status Reporting and Monitoring
6.4 Point-in-Time Recovery »
Section Navigation      [Toggle]

6.3.3. BACKUP DATABASE and RESTORE Status Reporting and Monitoring

MySQL provides information about the status or progress of BACKUP DATABASE or RESTORE operations in the following ways:

  • SHOW PROCESSLIST displays information while a thread performing a backup or restore is executing.

  • Upon successful completion, the BACKUP DATABASE and RESTORE statements return a result set with the backup number. (This number is the ID for the corresponding row or rows in the metadata tables described later.) Warnings produced during the operation can be displayed with SHOW WARNINGS.

    If errors occur during a backup or restore operation, they are written to the error log, recorded in the progress tables, and are available via the SHOW ERRORS and SHOW WARNINGS statements.

    If a fatal error occurs, the BACKUP DATABASE or RESTORE statement reports it to the user.

  • The server maintains online_backup and online_backup_progress tables in the mysql database that contain metadata. (If you upgrade to MySQL 6.0.5 or later from an older version, be sure to run mysql_upgrade to ensure that these tables exist.)

    • The online_backup table contains a row for each backup and restore operation. A row is created when an operation begins and is updated as the operation progresses. The rows in this table serve as a history of all backup and restore operations performed on the server. The table can be queried to obtained detailed information about the operations or as a means to create a summary of the operations. The rows are not removed from the table by the server. Any table maintenance, such as removing old rows, is intended to be performed by the database administrator.

    • The online_backup_progress table contains progress data describing the steps in the most recent backup or restore operation. There may be multiple rows for the operation. Rows are added to this table over the course of the operation and are not updated. This enables the table to be used to track the current progress of the operation. Each row in the table represents 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 database administrator should not need to perform maintenance for this data.

    Currently, there are no INFORMATION_SCHEMA tables corresponding to the online_backup and online_backup_progress tables.

The online_backup table has this structure: 

CREATE TABLE online_backup (
    backup_id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    process_id          INT UNSIGNED NOT NULL,
    binlog_pos          INT UNSIGNED DEFAULT 0,
    binlog_file         CHAR(64),
    backup_state        ENUM('complete', 'starting', 'validity point',
                             'running', 'error', 'cancel') NOT NULL,
    operation           ENUM('backup', 'restore') NOT NULL,
    error_num           INT NOT NULL DEFAULT 0,
    num_objects         INT UNSIGNED NOT NULL DEFAULT 0,
    total_bytes         BIGINT UNSIGNED,
    validity_point_time DATETIME,
    start_time          DATETIME,
    stop_time           DATETIME,
    host_or_server_name CHAR (30),
    username            CHAR (30),
    backup_file         CHAR (100),
    user_comment        VARCHAR (200) DEFAULT NULL,
    command             VARCHAR (512),
    engines             VARCHAR (100),
) ENGINE=MYISAM;

The online_backup columns are used as follows:

  • backup_id

    The ID for the table row. BACKUP DATABASE and RESTORE return a result set containing a backup ID, which is the value that tells you which row in the online_backup table corresponds to the backup or restore operation.

  • process_id

    The process ID that the operation ran as.

  • binlog_pos, binlog_file

    For a backup, the binary log position and filename at the time the validity point is generated (the time when all storage engines are synchronized). Before that time, the values are 0 and NULL.

  • backup_state

    The status of the operation.

  • operation

    The type of operation.

  • error_num

    The error from this operation (0 = no error).

  • num_objects

    The number of objects in the backup.

  • total_bytes

    The size of the backup image file in bytes.

  • validity_point_time

    For a backup, this is the time that the validity point was generated. Before that time, the value is NULL. For a restore, the value currently is always NULL.

  • start_time, stop_time

    The date and time when the operation started and stopped.

  • host_or_server_name

    The server name where the operation ran.

  • username

    The name of the user who ran the operation.

  • backup_file

    The name of the backup image file.

  • user_comment

    The comment from the user entered at the command line.

  • command

    The statement used to perform the operation.

  • engines

    The names of the storage engines used in the operation.

The online_backup_progress table has this structure: 

CREATE TABLE online_backup_progress (
    backup_id   BIGINT UNSIGNED NOT NULL
    object      CHAR (30) NOT NULL
    start_time  DATATIME
    stop_time   DATATIME
    total_bytes BIGINT
    progress    BIGINT UNSIGNED 
    error_num   INT NOT NULL DEFAULT 0
    notes       CHAR(100)
) ENGINE=MYISAM;

The online_backup_progress columns are used as follows:

  • backup_id

    The backup_id value of the online_backup table row with which the rows in the online_backup_progress table are associated.

  • object

    The object being operated on.

  • start_time, stop_time

    The date and time when the operation started and stopped.

  • total_bytes

    The size of the object in bytes.

  • progress

    The number of bytes processed.

  • error_num

    The error from this operation (0 = no error).

  • notes

    Commentary from the backup engine.