13.3 使用 MySQL 企业备份日志

MySQL Enterprise Backup 除了将消息输出到 stderr流和日志文件之外,每次备份的进度和历史记录也会记录到备份服务器上的 mysql.backup_progressmysql.backup_history表中(要跳过更新两个表,请使用 --no-history-loggingbackup 命令的选项).

backup_progress桌子

表中的每一行都backup_progress记录了状态更改或来自正在运行的备份作业的消息。该 backup_progress表包含以下列:

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

backup_progress表为 CSV 格式。可以用mysql.CSV客户端查询表,也可以用应用程序或脚本 解析对应的文件。

以下是一些使用 backup_progress表中信息的方法:

  • 使用该backup_id值查询单个备份操作的不同阶段的所有信息,并在表中找到同一备份的对应行 (该行 仅在备份完成后 backup_history写入表中)。backup_history

  • 使用error_codeerror_message值跟踪已发生的任何错误,并查看备份操作是否应因任何严重错误而终止。

  • 使用current_timecurrent_state值来跟踪操作的进度。它们还允许您测量备份的每个阶段需要多长时间,这有助于您规划未来的备份。

backup_history桌子

表中的每一行都记录了由mysqlbackup命令backup_history生成的一个完整备份的详细信息 。该 表包含以下列: backup_history

mysql> DESC 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                |       |
    | innodb_file_format        | varchar(100)  | NO   |     | NULL                |       |
    | start_lsn                 | bigint(20)    | NO   |     | NULL                |       |
    | end_lsn                   | bigint(20)    | NO   |     | NULL                |       |
    | incremental_base_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                |       |
    | innodb_log_files_in_group | varchar(100)  | NO   |     | NULL                |       |
    | innodb_log_file_size      | varchar(100)  | 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                |       |
    +---------------------------+---------------+------+-----+---------------------+-------+
30 rows in set (0.00 sec)
警告

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

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

  • The tool_name column records the full mysqlbackup command that triggers the backup, including all the options used.

  • You can use the end_lsn value of your latest backup as the starting LSN value for you 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).

  • The binlog_pos column gives the position of the binary log up to where log events have been covered by the backup. Because the backup_history table used to be in the CSV format, which cannot register NULL 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 of NULL values.

  • The value for backup_type is one of FULL, PARTIAL, INCREMENTAL, or TTS.

  • The value for backup_format is one of IMAGE (for single-file backups) or DIRECTORY (for directory backups).

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

  • 的 值为exit_state或 。如果is 和 is ,则备份操作成功;如果不是这种情况,请参阅 和 以获取最新的操作错误。要检索该备份操作的完整错误列表,请转至该表。 SUCCESSFAILUREexit_stateSUCCESSlast_error'NO_ERROR'last_errorlast_error_codebackup_progress