SET DATA_BACKUP_DEST

Purpose

The ALTER SYSTEM SET DATA_BACKUP_DEST statement is used to configure the backup destination for a user tenant, which specifies the backup path for data backups.

Limitations and considerations

• Backup and restore are not supported for the sys tenant and meta tenant. Therefore, you do not need to configure a backup destination.

• OceanBase Database supports the following backup media: NFS, Alibaba Cloud OSS, Azure Blob (supported starting from V4.3.5 BP3), AWS S3, and object storage services compatible with the S3 protocol, such as Huawei OBS, Google GCS, and Tencent Cloud COS. Some backup media may require specific requirements to be met before they can be used. For more information about the requirements for each backup media, see Backup media requirements in Overview of physical backup and restore.

Privilege requirements

The root user of the sys tenant (root@sys) or the administrator of each tenant must execute this statement. The administrator user varies depending on the mode:

• In MySQL mode, the default administrator user is root.
• In Oracle mode, the default administrator user is SYS.

Syntax

ALTER SYSTEM SET DATA_BACKUP_DEST = 'data_backup_path' [TENANT = tenant_name];

Parameters

data_backup_path

Specifies the backup path for data backup. The following describes the data_backup_path format for different types of media.

NFS

ALTER SYSTEM SET DATA_BACKUP_DEST = 'file://your-nfs-server/your-backup-path';

In this example, file://your-nfs-server/your-backup-path specifies the path of the NFS server that OceanBase Database can access for storing data backups.

Object storage

# Alibaba Cloud OSS
ALTER SYSTEM SET DATA_BACKUP_DEST = 'oss://your-bucket-name/your-archive-path?host=oss-your-region.aliyuncs.com&access_id=your-access-id&access_key=your-access-key[&delete_mode={delete | tagging}][&checksum_type={md5 | no_checksum}][&enable_worm={true | false}]';

# AWS S3
ALTER SYSTEM SET DATA_BACKUP_DEST = 's3://your-bucket-name/your-archive-path?host=s3.your-region.amazonaws.com&access_id=your-access-id&access_key=your-access-key&s3_region=your-s3-region[&delete_mode={delete | tagging}][&checksum_type={md5 | crc32}]';

# Object storage compatible with the S3 protocol
ALTER SYSTEM SET DATA_BACKUP_DEST = 's3://your-bucket-name/your-archive-path?host=your-service.com&access_id=your-access-id&access_key=your-access-key[&addressing_model={virtual_hosted_style | path_style}][&checksum_type=md5]';

# Azure Blob
ALTER SYSTEM SET DATA_BACKUP_DEST = 'oss://your-bucket-name/your-archive-path?host=oss-your-region.aliyuncs.com&access_id=your-access-id&access_key=your-access-key[&delete_mode={delete | tagging}][&checksum_type={md5 | no_checksum}][&enable_worm={true | false}]';

The following table describes the parameters.

• host: the domain name of the object storage service.

  For Azure Blob, the value of the host parameter can be obtained by concatenating the container property and the connection string of the storage account. The value of the host parameter must start with http:// or https://. By default, Azure Blob uses secure transmission, which means that it uses https:// to access the service. If you want to use http:// to access the service, you must disable secure transmission for Azure Blob. For more information, see Azure Blob documentation.

• access_id=your-access-id: the AccessKey ID of the object storage service.

• access_key=your-access-key: the AccessKey of the object storage service.

• delete_mode: specifies the cleanup mode for the backup path. This parameter is optional for OSS, AWS S3, COS (accessed by using the S3 protocol), and Azure Blob. This parameter is separated from other parameters by the & symbol. The value of this parameter can be set to one of the following:

  • delete: specifies that the system directly deletes backup files that meet the cleanup requirements.

    If you set this parameter to delete, the system directly deletes backup files that meet the cleanup requirements when you automatically clean up backup files.

  • tagging: specifies that the system sets a Tag for backup files that meet the cleanup requirements. These backup files are retained.

    If you set this parameter to tagging, the system sets a tag for backup files that meet the cleanup requirements when you automatically clean up backup files. The key of the tag is "delete_mode" and the value is "tagging". This way, you can manage the lifecycle of these files by using the tags in the object storage service.

• checksum_type: specifies the algorithm for verifying the integrity of the backup data. If you do not explicitly specify this parameter, the system uses the MD5 algorithm by default. The value of this parameter varies based on the object storage service. The following table describes the supported values.

  • md5: specifies that the system uses the MD5 algorithm to verify the integrity of the backup data.

    Notice

    The MD5 algorithm is not supported for the GetObject interface.

  • no_checksum: specifies that the system does not verify the integrity of the backup data. This parameter is supported only by OSS and Azure Blob. For object storage services that are accessed by using the S3 protocol, including AWS S3, OBS, GCS, and COS, this parameter is not supported because of the limitations of the S3 SDK.

  • crc32: specifies that the system uses the CRC32 algorithm to verify the integrity of the backup data. This parameter is supported only by AWS S3.

• enable_worm: specifies whether to enable the WORM (Write Once Read Many) retention policy for the backup destination. This parameter is optional. If the backup destination is an OSS bucket and the bucket has a WORM retention policy, set this parameter to true.

  The WORM retention policy of OSS allows you to save and use data in an unchangeable manner. After you configure and lock the retention policy for the bucket, you can upload and read objects in the bucket only before the retention period of the object expires. After the retention period of the object expires, you can modify or delete the object.

  Notice

  • Starting from V4.3.5 BP2, the enable_worm parameter is supported in V4.3.5.
  • Since OceanBase Database does not replace your configured retention policy, make sure that the OSS bucket has been correctly configured with a retention policy (WORM policy) and locked before setting the backup path.
  • If the bucket corresponding to the path to be set has a retention policy that is locked, but you do not specify enable_worm=true when configuring the backup destination, an error will be returned after the statement is executed, indicating that enable_worm=true must be set.
  • Once the enable_worm parameter is set, it cannot be changed later.
  • After the backup path is set, if the user sets and locks a WORM policy for the corresponding bucket, the backup task may fail with error code -9140 (which can be viewed in the CDB_OB_BACKUP_DELETE_JOB_HISTORY view for the sys tenant or the DBA_OB_BACKUP_DELETE_JOB_HISTORY view for a user tenant) indicating the object is locked by worm. If a backup task fails due to a WORM policy, the user must reconfigure a new backup path and specify enable_worm=true.
  • When using a bucket with a WORM policy as the backup path, it is recommended to use the tagging mode for backup cleanup.

  The following table describes the parameters supported by the enable_worm parameter.

  • true: Yes. OceanBase Database will perform write and delete operations on the path in accordance with the WORM policy. When this value is set to true, the checksum_type=md5 parameter must also be explicitly configured.

  • false: No. If this parameter is not explicitly specified, its default value is false.

• s3_region: The region of the Amazon S3 bucket. This parameter is required when the backup medium is AWS S3.

• addressing_model: The URL format for accessing the object storage service. This parameter is optional for object storage services that support S3-compatible protocols (such as OBS, GCS, and COS) and is connected with other parameters by using the & symbol. The parameter currently supports the following two values:

  • virtual_hosted_style: The default value. This value indicates that the object storage service is accessed in Virtual-hosted-style.
  • path_style: This value indicates that the object storage service is accessed in Path-style.

  For example, if a user tenant sets the backup medium to OBS and accesses OBS in Virtual-hosted-style, the statement is as follows:

  ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?host=obs.****.myhuaweicloud.com&access_id=****&access_key=****&addressing_model=virtual_hosted_style';
  

Examples

• System tenant

  • The system tenant configures the backup path for the mysql_tenant tenant, with the backup medium as NFS.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'file:///data/nfs/backup/data' TENANT = mysql_tenant;
    

  • The system tenant configures the backup path for the mysql_tenant tenant, with the backup medium as OSS, the cleanup mode for backup files as delete, and the MD5 algorithm for verifying the integrity of the backup data.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'oss://oceanbase-test-bucket/backup/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&checksum_type=md5' TENANT = mysql_tenant;
    

    • The system tenant configures the backup path for the mysql_tenant tenant, with the backup medium as OSS, the cleanup mode for backup files as delete, and no verification of the integrity of the backup data.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'oss://oceanbase-test-bucket/backup/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&checksum_type=no_checksum' TENANT = mysql_tenant;
    

  • The system tenant configures the backup path for the mysql_tenant tenant, with the backup medium as OSS, the cleanup mode for backup files as tagging, and the MD5 algorithm for verifying the integrity of the backup data. If the path is configured with enable_worm=true, the system will perform write and delete operations on the path in compliance with WORM standards.

    Notice

    OceanBase Database does not currently replace your compliance retention strategy. Before setting the backup path, ensure that the OSS bucket has the correct retention strategy (WORM strategy) and is locked.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'oss://oceanbase-test-bucket/backup/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=tagging&checksum_type=md5&enable_worm=true' TENANT = mysql_tenant;
    

  • The system tenant configures the backup path for the mysql_tenant tenant, with the backup medium as AWS S3, the cleanup mode for backup files as tagging, and the CRC32 algorithm for verifying the integrity of the backup data.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com&access_id=xxx&access_key=xxx&s3_region=xxx&delete_mode=tagging&checksum_type=crc32' TENANT = mysql_tenant;
    

  • The system tenant configures the backup path for the mysql_tenant tenant, with the backup medium as OBS, and accessed via the S3 protocol.

    obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?host=obs.****.myhuaweicloud.com&access_id=****&access_key=****' TENANT = mysql_tenant;
    

  • The system tenant configures the backup path for the mysql_tenant tenant, with the backup medium as GCS, and accessed via the S3 protocol.

    obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?host=https://storage.googleapis.com&access_id=****&access_key=****' TENANT = mysql_tenant;
    

  • The system tenant configures the backup path for the mysql_tenant tenant, with the backup medium as COS, the cleanup mode for backup files as delete, and accessed via the S3 protocol.

    Notice

    When using COS as the backup medium, you need to set the cluster-level parameter ob_storage_s3_url_encode_type first. For more information, see Preparations before backup.

    obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com&access_id=***&access_key=***&delete_mode=delete' TENANT = mysql_tenant;
    

  • The system tenant configures the backup path for the mysql_tenant tenant, with the backup medium as Azure Blob, the cleanup mode as delete, and the verification algorithm as md5.

    obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='azblob://oceanbase-test-bucket/backup/data?host=http://****.blob.core.windows.net&access_id=****&access_key=****&delete_mode=delete&checksum_type=md5' TENANT = mysql_tenant;
    

• User tenant

  • The mysql_tenant tenant configures the backup path for itself, with the backup medium as NFS.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'file:///data/nfs/backup/data';
    

  • The mysql_tenant tenant configures the backup path for itself, with the backup medium as OSS, the cleanup mode for backup files as delete, and the MD5 algorithm for verifying the integrity of the backup data.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'oss://oceanbase-test-bucket/backup/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&checksum_type=md5';
    

  • The mysql_tenant tenant configures the backup path for itself, with the backup medium as OSS, the cleanup mode for backup files as delete, and no verification of the integrity of the backup data.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'oss://oceanbase-test-bucket/backup/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=delete&checksum_type=no_checksum';
    

  • The mysql_tenant tenant configures the backup path for itself, with the backup medium as OSS, the cleanup mode for backup files as tagging, and the MD5 algorithm for verifying the integrity of the backup data. If the path is configured with enable_worm=true, the system will perform write and delete operations on the path in compliance with WORM standards.

    Notice

    OceanBase Database does not currently replace your compliance retention strategy. Before setting the backup path, ensure that the OSS bucket has the correct retention strategy (WORM strategy) and is locked.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'oss://oceanbase-test-bucket/backup/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx&delete_mode=tagging&checksum_type=md5&enable_worm=true';
    

  • The mysql_tenant tenant configures the backup path for itself, with the backup medium as AWS S3, the cleanup mode for backup files as tagging, and the CRC32 algorithm for verifying the integrity of the backup data.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com&access_id=xxx&access_key=xxx&s3_region=xxx&delete_mode=tagging&checksum_type=crc32';
    

  • The mysql_tenant tenant configures the backup path for itself, with the backup medium as OBS, and accessed via the S3 protocol.

    obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?host=obs.****.myhuaweicloud.com&access_id=****&access_key=****';
    

  • The mysql_tenant tenant configures the backup path for itself, with the backup medium as GCS, and accessed via the S3 protocol.

    obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?host=https://storage.googleapis.com&access_id=****&access_key=****';
    

  • The mysql_tenant tenant configures the backup path for itself, with the backup medium as COS, the cleanup mode for backup files as delete, and accessed via the S3 protocol.

    Notice

    When using COS as the backup medium, you need to set the cluster-level parameter ob_storage_s3_url_encode_type first. For more information, see Preparations before backup.

    obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com&access_id=***&access_key=***&delete_mode=delete';
    

  • The mysql_tenant tenant configures the backup path for itself, with the backup medium as Azure Blob, the cleanup mode as delete, and the verification algorithm as md5.

    obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='azblob://oceanbase-test-bucket/backup/data?host=http://****.blob.core.windows.net&access_id=****&access_key=****&delete_mode=delete&checksum_type=md5';
    

References

• BACKUP KEY

• BACKUP

• Preparation before backup