Create a Backup with MariaDB Xpand
This page is part of MariaDB's Documentation.
The parent of this page is: Fast Backup and Restore with MariaDB Xpand
Topics on this page:
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®ion=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®ion=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 |
---|---|
|
|
|
|
|
|
|
|
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®ion=$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.