Restore a Backup with MariaDB Xpand

Overview

With MariaDB Xpand, a backup can be restored by executing the RESTORE statement.

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

RESTORE

Use the following syntax to restore objects previously backed up with Fast Backup.

Restore SQL Syntax

 
sql> RESTORE identifier [AS identifier] [, identifier [AS identifier]] [EXCLUDING excluded_identifier] FROM target [REPLICAS=N]

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 Restore Target

MariaDB Xpand supports multiple backup targets:

  • AWS S3

  • GCP Cloud Storage

  • SFTP

  • FTP

Restore from AWS S3

Xpand 6.1 can use AWS S3 for backup and restore.

To restore from AWS S3:

RESTORE *.* FROM
   '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 restore from AWS S3 using Transfer Acceleration:

RESTORE *.* FROM
   '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.

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.

Restore from GCP Cloud Storage

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

To restore from GCP Cloud Storage:

RESTORE *.* FROM
   'gs://GCP_BUCKET_NAME/OBJECT_NAME?credentials=GCP_CREDENTIALS_BASE64'

Restore via SFTP

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

RESTORE *.* FROM
    'sftp://USER:PASSWORD@HOST:PORT/ABSOLUTE_PATH'

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

Restore via FTP

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

RESTORE *.* FROM
    'ftp://USER:PASSWORD@HOST:PORT/RELATIVE_PATH'

Passive mode is supported. Active mode is not supported.

REPLICAS = N

The number of replicas for a table or index is recorded within the backups. Normally when RESTORE is run, tables and indexes are restored with the same number of replicas as the original. This option allows you to explicitly specify the number of replicas that should be created as part of the RESTORE operation.

For example, if a table had three replicas when backed up, the following example would create the table with only two replicas.

sql> RESTORE * FROM 'ftp://USER:PASSWORD@HOST:PORT/RELATIVE_PATH' REPLICAS = 2;

For additional information about specifying the number of replicas in a table, see "Manage Data Distribution for MariaDB Xpand".

Tuning RESTORE Performance

You may want to tune RESTORE to be slower or faster.

SET GLOBAL backup_restore_concurrency = desired_value;

The global variable backup_restore_concurrency determines the number of slices that can be restored simultaneously to each node.

Best Practices

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

MariaDB recommends running RESTORE during off-peak hours.