Documentation Home
MySQL Enterprise Backup 9.1 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.3Mb
PDF (A4) - 1.3Mb


17.3 Using the MySQL Enterprise Backup Logs

Besides the message output of MySQL Enterprise Backup to thestderr stream and the log file, progress and history of each backup are also logged into themysql.backup_progress andmysql.backup_history tables on the backed-up servers (to skip updating the two tables, use the--no-history-logging option with the backup command).

backup_progress Table

Each row in thebackup_progress table records a state change or message from a running backup job. Thebackup_progress table has the following columns:

mysql> DESCRIBE mysql.backup_progress;+---------------+---------------+------+-----+----------------------+--------------------------------------------------+| Field         | Type          | Null | Key | Default              | Extra                                            |+---------------+---------------+------+-----+----------------------+--------------------------------------------------+| id            | int           | NO   | PRI | NULL                 | auto_increment                                   || backup_id     | bigint        | NO   | MUL | NULL                 |                                                  || tool_name     | varchar(4096) | NO   |     | NULL                 |                                                  || error_code    | int           | NO   |     | NULL                 |                                                  || error_message | varchar(4096) | NO   |     | NULL                 |                                                  || current_time  | timestamp(3)  | NO   |     | CURRENT_TIMESTAMP(3) | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) || current_state | varchar(200)  | NO   |     | NULL                 |                                                  |+---------------+---------------+------+-----+----------------------+--------------------------------------------------+7 rows in set (0.00 sec)

Thebackup_progress table is in InnoDB format.

Here are some ways to make use of the information in thebackup_progress table:

  • Use thebackup_id value to query all the information for different stages of a single backup operation, and to find the corresponding row in thebackup_history table for the same backup (the row is written to thebackup_history table only after the backup is finished).

  • Check thetool_name column for the fullmysqlbackup command that triggered the backup, including all the options used.

  • Use theerror_code anderror_message values to track any errors that have occurred, and to see if the backup operation should be terminated because of any serious errors.

  • Use thecurrent_time andcurrent_state values to track the progress of the operation. They also allow you to measure how long each stage of the backup takes, which helps you plan for your future backups.

backup_history Table

Each row in thebackup_history table records the details of one completed backup produced by amysqlbackup command. Thebackup_history table has the following columns:

mysql> mysql> DESCRIBE mysql.backup_history;+---------------------------+---------------+------+-----+---------------------+-------+| Field                     | Type          | Null | Key | Default             | Extra |+---------------------------+---------------+------+-----+---------------------+-------+| backup_id                 | bigint(20)    | NO   | PRI | NULL                |       || tool_name                 | varchar(4096) | NO   |     | NULL                |       || start_time                | timestamp     | NO   |     | 0000-00-00 00:00:00 |       || end_time                  | timestamp     | NO   |     | 0000-00-00 00:00:00 |       || binlog_pos                | bigint(20)    | NO   |     | NULL                |       || binlog_file               | varchar(255)  | NO   |     | NULL                |       || compression_level         | int(11)       | NO   |     | NULL                |       || engines                   | varchar(100)  | NO   |     | NULL                |       || innodb_data_file_path     | varchar(2048) | NO   |     | NULL                |       || start_lsn                 | bigint(20)    | NO   |     | NULL                |       || end_lsn                   | bigint(20)    | NO   |     | NULL                |       || backup_type               | varchar(50)   | NO   |     | NULL                |       || backup_format             | varchar(50)   | NO   |     | NULL                |       || mysql_data_dir            | varchar(2048) | NO   |     | NULL                |       || innodb_data_home_dir      | varchar(2048) | NO   |     | NULL                |       || innodb_log_group_home_dir | varchar(2048) | NO   |     | NULL                |       || backup_destination        | varchar(4096) | NO   |     | NULL                |       || lock_time                 | double(7,3)   | NO   |     | NULL                |       || exit_state                | varchar(10)   | NO   |     | NULL                |       || last_error                | varchar(4096) | NO   |     | NULL                |       || last_error_code           | int(11)       | NO   |     | NULL                |       || start_time_utc            | bigint(20)    | NO   |     | NULL                |       || end_time_utc              | bigint(20)    | NO   |     | NULL                |       || consistency_time_utc      | bigint(20)    | NO   |     | NULL                |       || meb_version               | varchar(20)   | NO   |     | 0.0.0               |       || server_uuid               | varchar(36)   | NO   |     | NULL                |       |+---------------------------+---------------+------+-----+---------------------+-------+26 rows in set (0.01 sec)
Warning

Because a successful backup is always recorded as such in thebackup_history table, a failure in theapply-log phase of abackup-and-apply-log command is not reflected in thebackup_history table. It is always important to check the output ofmysqlbackup to see if an operation is completed fully without an error.

Here is information on some columns of thebackup_history table, and some ways to make use of the information:

  • Thetool_name column records the fullmysqlbackup command that triggered the backup, including all the options used.

  • You can use theend_lsn value of the latest backup as the starting LSN value for the next incremental backup by specifying it with the--start-lsn option. (An alternative to specifying the start LSN value for an incremental backup is to use the--incremental-base option).

  • Thebinlog_pos column gives the position of the binary log up to where log events have been covered by the backup. Because thebackup_history table used to be in the CSV format, which cannot registerNULL values directly, if binary logging is not enabled, a value of-1 is entered into the column; the same applies to other columns for the logging ofNULL values.

  • The value forbackup_type is one ofFULL,PARTIAL,DIFFERENTIAL,INCREMENTAL orTTS.

  • The value forbackup_format is one ofIMAGE (for single-file backups) orDIRECTORY (for directory backups).

  • Use the values that show the backup's settings such asmysql_data_dir,innodb_data_home_dir, andbackup_destination to confirm that the backups are using the right source and destination directories.

  • The value forexit_state is eitherSUCCESS orFAILURE. If theexit_state isSUCCESS andlast_error is'NO_ERROR', the backup operation has been successful; when it is not the case, seelast_error andlast_error_code for the latest error of the operation. To retrieve the full list of errors for that backup operation, go to thebackup_progress table.