Skip navigation links
2 MySQL Backup Statements »
Section Navigation      [Toggle]

1.7.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 file names 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 1.7.1, “MySQL Backup Log Control”.

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.

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 completes. 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 obtain detailed information about the operations or as a means of creating 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.

Backup log contents can be culled with the PURGE BACKUP LOGS statement (see Section 2.3, “PURGE BACKUP LOGS Syntax”.)

The backup_history table has this structure: 

CREATE TABLE backup_history (
    backup_id           BIGINT UNSIGNED NOT NULL,
    process_id          INT UNSIGNED NOT NULL,
    binlog_start_pos    INT UNSIGNED NOT NULL,
    binlog_file         CHAR(64) NOT NULL,
    backup_state        ENUM('complete', 'starting', 'validity point',
                             'running', 'error', 'cancel') NOT NULL,
    operation           ENUM('backup', 'restore') NOT NULL,
    error_num           INT NOT NULL,
    num_objects         INT UNSIGNED NOT NULL,
    total_bytes         BIGINT UNSIGNED NOT NULL,
    validity_point_time DATETIME NOT NULL,
    start_time          DATETIME NOT NULL,
    stop_time           DATETIME NOT NULL,
    host_or_server_name CHAR (30) NOT NULL,
    username            CHAR (30) NOT NULL,
    backup_file         CHAR (100) NOT NULL,
    backup_file_path    VARCHAR (512) NOT NULL,
    user_comment        VARCHAR (200) NOT NULL,
    command             VARCHAR (512) NOT NULL,
    engines             VARCHAR (100) NOT NULL
) 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_start_pos, binlog_file

    For a backup, the starting binary log position and the file name at the time the validity point is generated (the time when all storage engines are synchronized). These coordinates indicate the point in the binary log at which database changes begin subsequent to the backup. If you perform point-in-time recovery by re-executing events in the binary log after restoring the backup image, these are the coordinates at which to start reading the binary log. Similary, if you use a backup image from a master server to set up a replication slave, these are the coordinates from which you would tell the slave to begin reading the master binary log.

    If the binary log is not enabled, the values are 0 and '' (the empty string).

    For a restore, the values of these fields are the values from the original backup operation (that is, the same values as those stored in the backup image).

  • 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

    For a backup, the number of bytes written to the backup image file (after any compression or encryption). For a restore, the number of bytes read from the backup image file.

  • validity_point_time

    For a backup, this is the time that the validity point was generated.

  • 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 basename of the backup image file.

  • backup_file_path

    The directory containing the image file.

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

The backup_progress table has this structure: 

CREATE TABLE backup_progress (
    backup_id   BIGINT UNSIGNED NOT NULL,
    object      CHAR (30) NOT NULL,
    error_num   INT NOT NULL,
    notes       CHAR(100) NOT NULL
) 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.

  • error_num

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

  • notes

    Commentary from the backup engine.