Monitor Backups with MariaDB Xpand

Overview

With MariaDB Xpand, system tables are provided to monitor backups created with Xpand Fast Backup and Restore.

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

View Metadata About All Backups

To view metadata about all backups, query the system.backups table and specify the source column as a filter.

Empty Source

When the system.backups system table is queried, Xpand communicates with the system where the backup is stored. Therefore, you must explicitly specify a value for the source column when querying the system.backups table. If the source column is omitted, the result set will be empty:

SELECT * FROM system.backups;

Empty set (0.001 sec)

Source is an FTP Server

If the source is an FTP server:

SELECT * FROM system.backups
   WHERE source = 'ftp://FTP_USER_NAME:FTP_PASSWORD@FTP_SERVER/XPAND_BACKUP_DIRECTORY'\G

*************************** 1. row ***************************
        source: ftp://FTP_USER_NAME:<redacted>@FTP_SERVER/XPAND_BACKUP_DIRECTORY/db1-backup
        backup: db1-backup
  cluster_name: cl37d21153ea2ddc82
       version: Xpand-6.1
        status: COMPLETED
    start_time: 2023-05-01 06:17:44.414875
completed_time: 2023-05-01 06:17:46.426927
         bytes: 11899368

Source is AWS S3

If the source is an AWS S3 bucket:

SELECT * FROM system.backups
   WHERE source = 's3://AWS_BUCKET_NAME/OBJECT_NAME?access_key_id=AWS_ACCESS_KEY_ID&secret_access_key=AWS_SECRET_ACCESS_KEY&region=AWS_REGION&session_token=AWS_SESSION_TOKEN'\G

If you execute the query in your shell with MariaDB Client, the query can use the AWS S3 environment variables:

$ export AWS_ACCESS_KEY_ID='..'
$ export AWS_ENDPOINT_URL='..'
$ export AWS_REGION='..'
$ export AWS_SECRET_ACCESS_KEY='..'
$ export AWS_BUCKET='..'
$ mysql --host='DB_HOST' --user='DB_USER' --execute="SELECT * FROM system.backups WHERE source = \'s3://$AWS_BUCKET/OBJECT_NAME?access_key_id=$AWS_ACCESS_KEY_ID&secret_access_key=$AWS_SECRET_ACCESS_KEY&region=$AWS_REGION&session_token=$AWS_SESSION_TOKEN\'"

View the Status of In-progress Backups

To view the status of in-progress backups, query the system.backup_status table:

SELECT * FROM system.backup_status\G

*************************** 1. row ***************************
        nodeid: 1
            id: 7228094667407824897
          type: BACKUP
           url: ftp://FTP_USER_NAME:<redacted>@FTP_SERVER/XPAND_BACKUP_DIRECTORY/db1-backup
          objs: (("db1" "t1"))
    start_time: 2023-05-01 06:18:53.803666
expected_bytes: 0
      replicas: 0
          rows: 0
         bytes: 0

View Details About Tables

To view details about tables in a backup, query the system.backup_tables table:

SELECT * FROM system.backup_tables
   WHERE source = 'ftp://FTP_USER_NAME:FTP_PASSWORD@FTP_SERVER/XPAND_BACKUP_DIRECTORY'\G

*************************** 1. row ***************************
source: ftp://FTP_USER_NAME:<redacted>@FTP_SERVER/XPAND_BACKUP_DIRECTORY/db1-backup
backup: db1-backup
    db: db1
 table: t1
 bytes: 11899368