Create a Backup with MariaDB Xpand

Overview

With MariaDB Xpand, a backup can be created by executing the BACKUP statement.

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

BACKUP

Backup runs as a single transaction and will pin BigC (Xpand's garbage collection) for the duration of the operation. This may require operational consideration if your cluster does not have enough free space for the undo log created from this long running transaction.

System tables are provided to monitor backups created with Xpand Fast Backup and Restore. For additional information, see "Monitor Backups with MariaDB Xpand".

Backup SQL Syntax

sql> BACKUP identifier [, identifier] [EXCLUDING excluded_identifier] TO 'target' [COMPRESSED]

Include and Exclude Objects

MariaDB Xpand allows objects to be included or excluded from the backup and restore process.

In the BACKUP and RESTORE statements, both the identifier and excluded_identifer should refer to valid system objects. This can be a fully qualified name, or include wildcards (*) for the database, object, or both. The following formats are supported:

  • For backup and restore, DATABASE_NAME.OBJECT_NAME references a specific object in the given database (such as a table or view name):

    BACKUP db_name.tbl_name1, db_name2.tbl_name2
       TO 'ftp://USER:PASSWORD@HOST:PORT/RELATIVE_PATH';
    
    RESTORE db_name.tbl_name1, db_name2.tbl_name2
       FROM 'ftp://USER:PASSWORD@HOST:PORT/RELATIVE_PATH';
    

    For restore, this syntax can also be used to restore a table with a new name by specifying AS new_name:

    RESTORE db_name.tbl_name AS db_name_backup.tbl_name
       FROM 'ftp://USER:PASSWORD@HOST:PORT/RELATIVE_PATH';
    
  • For backup and restore, DATABASE_NAME.* references all accessible objects in that database:

    BACKUP db_name1.*
       TO 'ftp://USER:PASSWORD@HOST:PORT/RELATIVE_PATH';
    
    RESTORE db_name1.*
       FROM 'ftp://USER:PASSWORD@HOST:PORT/RELATIVE_PATH';
    
  • For backup and restore, *.* references all accessible object in all databases that are not otherwise excluded:

    BACKUP *.* EXCLUDING db_name2.tbl_name1
       TO 'ftp://USER:PASSWORD@HOST:PORT/RELATIVE_PATH';
    
    RESTORE *.* EXCLUDING db_name2.tbl_name1
       FROM 'ftp://USER:PASSWORD@HOST:PORT/RELATIVE_PATH';
    
  • For restore, *.TABLE_NAME references a given table name in any database that is to be excluded from the restore.

Dependencies

Care should be taken when excluding objects from a backup or restore.

When a RESTORE is performed, errors can occur if the restored objects depend on other objects that were excluded from the backup or restore. For example, if a trigger or view refers to an excluded object that does not already exist in the restored environment, an error will occur during the RESTORE.

For backups, triggers, views, and foreign keys that reference an excluded object will be backed up, unless they are specifically excluded. When the backup is restored, it will only be successful if the excluded objects exist in the restored environment.

Specify a Backup Target

MariaDB Xpand supports multiple backup targets:

  • AWS S3

  • GCP Cloud Storage

  • SFTP

  • FTP

Backup to AWS S3

Xpand 6.1 can use AWS S3 for backup and restore.

To backup to AWS S3:

BACKUP *.* TO
   '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';

To backup to AWS S3 using Transfer Acceleration:

BACKUP *.* TO
   's3://AWS_BUCKET_NAME?endpoint_url=https://AWS_BUCKET_NAME.s3-accelerate.amazonaws.com/OBJECT_NAME&access_key_id=AWS_ACCESS_KEY_ID&secret_access_key=AWS_SECRET_ACCESS_KEY&region=AWS_REGION&session_token=AWS_SESSION_TOKEN';

Starting with Xpand 6.1.1, the following environment variables are supported as alternatives to the corresponding parameters in the backup URL:

Parameter

Environment Variable

access_key_id

AWS_ACCESS_KEY_ID

endpoint_url

AWS_ENDPOINT_URL

region

AWS_REGION

secret_access_key

AWS_SECRET_ACCESS_KEY

Additionally, the AWS_BUCKET environment variable can be used to set the bucket.

When a bucket or parameter is specified in the URL, the URL value takes precedence over the environment variable.

Prior to Xpand 6.1.1, you can still use the environment variables if you run the query in the following way using MariaDB Client:

$ 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="BACKUP *.* TO \'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\'"

Many cloud-based object storage services are compatible with AWS S3. MariaDB cannot make guarantees about all S3-compatible object storage services, because different services provide different functionality.

If you have any questions about using specific S3-compatible object storage with Xpand, contact MariaDB Support.

Backup to GCP Cloud Storage

Xpand 6.1 can use GCP Cloud Storage for backup and restore.

To backup to GCP Cloud Storage:

BACKUP *.* TO
   'gs://GCP_BUCKET_NAME/OBJECT_NAME?credentials=GCP_CREDENTIALS_BASE64';

Backup via SFTP

To backup via SFTP, specify the absolute path of the file, and provide a username and password for authentication:

BACKUP *.* TO
    'sftp://USER:PASSWORD@HOST:PORT/ABSOLUTE_PATH';

Password authentication is supported. Public key authentication is not supported.

Backup via FTP

To backup via FTP, specify the relative path of the file from the FTP root directory, and provide a username and password for authentication:

BACKUP *.* TO
    'ftp://USER:PASSWORD@HOST:PORT/RELATIVE_PATH';

Passive mode is supported. Active mode is not supported.

Compression

Backup compression is supported when the backup target is FTP or SFTP. This functionality reduces bandwidth requirements for backup to remote locations and reduces the amount of storage utilized, but adds additional latency to complete a backup.

To create a compressed backup, specify the COMPRESSED option:

BACKUP *.* TO
    'sftp://USER:PASSWORD@HOST:PORT/ABSOLUTE_PATH'
    COMPRESSED;

The COMPRESSED option is not supported for cloud-based backups.

Tuning BACKUP Performance

You may want to tune BACKUP to be slower or faster:

SET GLOBAL backup_backup_concurrency = desired_value;

The global variable backup_backup_concurrency determines the number of tables that can be backed up simultaneously. With the default value of 1, the backup process is limited to one table at a time.

Best Practices

MariaDB recommends running BACKUP in a screen session or using tmux to avoid connection failures.

MariaDB recommends running BACKUP during off-peak hours.