Skip navigation links

Additional languages
6.4 Point-in-Time Recovery »
Section Navigation      [Toggle]

6.3.3.2. MySQL Backup Log Contents

If you enable backup logging to tables, MySQL Backup uses the backup_history and backup_progress tables in the mysql database. For logging to files, MySQL Backup uses the backup_history.log and backup_progress.log files in the MySQL data directory by default. The log filenames can be changed by setting the global backup_history_log_file and backup_progress_log_file system variables. For information about selecting log destinations, see Section 6.3.3.1, “MySQL Backup Log Control”.

The contents of the log tables are discussed following. For logging to files, the server writes lines with a field for each column in the corresponding log table. The server also writes an initial line to the file at startup to indicate the names of the fields. Backup log contents can be culled with the PURGE BACKUP LOGS statement. See Section 12.5.3.2, “PURGE BACKUP LOGS Syntax”.

If the table destination is selected for backup logging, the server uses these tables:

  • The backup_history 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 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.

The backup_history table has this structure: 

CREATE TABLE backup_history (
    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),
    backup_file_path    VARCHAR (512),
    user_comment        VARCHAR (200),
    command             VARCHAR (512),
    engines             VARCHAR (100),
) ENGINE=CSV CHARSET=utf8;

The backup_history 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 backup_history 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. As of MySQL 6.0.8, this column contains the file basename.

  • backup_file_path

    The directory containing the image file. This column was added in MySQL 6.0.8.

  • user_comment

    The comment from the user entered at the command line.

  • command

    The statement used to perform the operation.

  • drivers

    The names of the drivers used in the operation. Before MySQL 6.0.7, this column was named engines.

The backup_progress table has this structure: 

CREATE TABLE 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=CSV CHARSET=utf8;

The backup_progress columns are used as follows:

  • backup_id

    The backup_id value of the backup_history table row with which the rows in the 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.