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 includes the backup path and source configuration.

Limitations and considerations

• Backup and restore are not supported for the sys tenant and meta tenant, and no backup destination needs to be configured.

• Currently, OceanBase Database supports the following backup media: NFS, Alibaba Cloud OSS, Azure Blob, 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 meeting certain basic requirements before they can be used. For more information about the specific requirements for each backup media, see Backup media requirements in Overview of physical backup and restore.

Privilege requirements

This procedure must be executed by the root user of the sys tenant (root@sys) or the administrator user of each tenant. Specifically:

• In MySQL-compatible mode, the default administrator user is root.
• In Oracle-compatible 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 and source configuration for data backup. The following sections describe the data_backup_path format for different types of media.

NFS

ALTER SYSTEM SET DATA_BACKUP_DEST = 'file://your-nfs-server/your-backup-path[?{zone=zone_name | idc=idc_name | region=region_name}]';

Where:

• file://your-nfs-server/your-backup-path: the path of the NFS server that OceanBase Database can access, used to store data backups.

• zone, idc, and region: specify the source of the backup path, i.e., the node range that can access the path and the access priority. These are optional parameters, and they are separated from the backup path by a question mark (?). You can select only one of the three levels (zone, idc, or region) or leave them unset by default.

  • When you specify the zone, idc, or region configuration for the backup path, the leader node of the Root Service must be located in the specified zone, idc, or region. Otherwise, the backup task will fail to write backup metadata to the target backup path, resulting in the failure of the backup task. We recommend that you set the source of the backup path to include the primary zone.

  • If you need to set the region or IDC level, you must first use the ALTER SYSTEM MODIFY ZONE zone_name SET REGION=region_name; or ALTER SYSTEM MODIFY ZONE zone_name SET IDC=idc_name; statement to set the region or IDC information. We recommend setting the source of the backup path at the IDC level. This way, you can use an IDC with abundant network bandwidth resources to execute the backup task, and the backup task will have more optional node ranges. Additionally, you do not need to pay attention to changes in zones within the IDC in the cluster.

  • If the specified zone, idc, or region does not exist, an error will be returned indicating that the source information does not exist (error code -9133).

  • If you need to set multiple zones, multiple IDCs, or multiple regions, separate the values with commas (,) and semicolons (;). Specifically:

    • A comma (,) indicates that the nodes in the zones/IDCs/regions on both sides have the same priority when selecting nodes to execute the backup task.
    • A semicolon (;) indicates that the nodes in the zone/IDC/region on the left have higher priority than those on the right. In other words, the backup task will prioritize selecting nodes from the zone/IDC/region on the left.

    For example, if you set it to zone=z1,z2;z3, it means that z1, z2, and z3 are specified as the sources of the backup. z1 and z2 have the same priority, and both have higher priority than z3.

  • After setting the backup source on the backup path, if the cluster's zones, IDCs, or regions change, such as name changes or deletion of zones/IDCs/regions, it may result in the backup operation failing, ultimately leading to the failure of the backup task.

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}][&{zone=zone_name | idc=idc_name | region=region_name}]';

# 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}][&{zone=zone_name | idc=idc_name | region=region_name}]';

# 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][&{zone=zone_name | idc=idc_name | region=region_name}]';

# 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}][&{zone=zone_name | idc=idc_name | region=region_name}]';

The following table describes the parameters.

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

  For Azure Blob, the value of the host parameter can be obtained by concatenating the container properties or the storage account connection string. The value of the host parameter must start with http:// or https://. By default, Azure Blob uses secure transmission, which means that it accesses the service through https://. If you want to access the service through http://, 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 value of this parameter can be set to one of the following options:

  • delete: specifies that the backup files that meet the cleanup requirements are directly deleted.

    When you use this mode to automatically clean up backup files, the system directly deletes the backup files that meet the cleanup requirements.

  • tagging: specifies that the backup files that meet the cleanup requirements are tagged. The backup files are retained.

    When you use this mode to automatically clean up backup files, the system sets a tag for each backup file that meets the cleanup requirements. The key of the tag is "delete_mode" and the value is "tagging". This way, you can manage the lifecycle of these files in the object storage service by using the tags.

• checksum_type: specifies the algorithm for verifying the integrity of the backup data. If you do not explicitly specify this parameter, the MD5 algorithm is used by default for all object storage services. The supported values of this parameter vary with the object storage service. The following table describes the supported values.

  • md5: specifies that the MD5 algorithm is used to verify the integrity of the backup data.

    Notice

    The MD5 algorithm is not supported for the GetObject interface.

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

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

• enable_worm: optional. If the backup destination is an OSS bucket that has a compliance retention policy (WORM), set this parameter to true.

  OSS supports the WORM (Write Once Read Many) feature for retention policies. This feature allows you to save and use data in an immutable manner. After you configure a retention policy for a bucket and lock the policy, you can upload and read objects in the bucket only before the retention period of the objects expires. After the retention period expires, you can modify or delete the objects.

  Notice

  • Since OceanBase Database does not replace your compliance retention strategy, ensure that the OSS bucket has a retention strategy (WORM) set and locked before setting the backup path.
  • If the bucket corresponding to the path to be set has a retention strategy set and locked, but the enable_worm=true parameter is not specified when configuring the backup destination, an error will be returned after the statement is executed, indicating that enable_worm=true needs to be set.
  • Once the enable_worm parameter is set, it cannot be changed later.
  • If the WORM strategy is set and locked for the bucket corresponding to the backup path, the backup task may fail with error code -9140 (indicating that the object is locked by WORM) because it cannot modify or delete files in the target path. This error can be viewed in the CDB_OB_BACKUP_DELETE_JOB_HISTORY (sys tenant) or DBA_OB_BACKUP_DELETE_JOB_HISTORY (user tenant) view. If the backup task fails due to the WORM strategy, you need to reconfigure a new backup path and specify enable_worm=true.
  • When using a bucket with a WORM strategy as the backup path, we recommend using the tagging mode for backup cleanup.

  The enable_worm parameter supports the following values:

  • true: Yes. Indicates that OceanBase Database will perform write and delete operations on the path according to the WORM specification. 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 where the Amazon S3 bucket is located. This parameter is required when the backup medium is AWS S3.

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

  • virtual_hosted_style: The default value. Indicates that the object storage is accessed using the Virtual-hosted-style format.
  • path_style: Indicates that the object storage is accessed using the Path-style format.

  For example, if a user tenant sets OBS as the backup medium and accesses OBS using the Virtual-hosted-style format, 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';
  

• zone, idc, and region: Specify the source of the backup path, that is, the node range that can access the path and the access priority. These parameters are optional and are connected to other parameters using the & symbol. You can select only one of the three levels (zone, idc, or region) or not set any of them. By default, none of them are set.

  • When specifying the zone, idc, or region configuration for the backup path, the leader node of the Root Service must be located in the specified zone, idc, or region. Otherwise, the backup task will fail because it cannot write backup metadata to the target backup path. We recommend that the source of the backup path include the Primary Zone.

  • If you need to set the Region or IDC level, you must first use the ALTER SYSTEM MODIFY ZONE zone_name SET REGION=region_name; or ALTER SYSTEM MODIFY ZONE zone_name SET IDC=idc_name; statement to set the Region or IDC information. We recommend setting the source configuration at the IDC level to utilize an IDC with abundant network bandwidth resources for the backup task, providing more optional node ranges and eliminating the need to monitor changes in Zones within the IDC.

  • If the specified zone, idc, or region does not exist, an error will be returned indicating that the source information does not exist (error code -9133).

  • If you need to set multiple Zones, multiple IDCs, or multiple Regions, separate the values with English commas (,) and semicolons (;). Specifically:

    • An English comma (,) indicates that the nodes in the left and right Zones/IDCs/Regions have the same priority when selecting nodes for the backup task.
    • A semicolon (;) indicates that the nodes in the left Zone/IDC/Region have higher priority than those in the right Zone/IDC/Region. In other words, the backup task will prioritize nodes in the left Zone/IDC/Region.

    For example, if the configuration is set to zone=z1,z2;z3, it indicates that z1, z2, and z3 are specified as the backup sources. z1 and z2 have the same priority, and their priority is higher than that of z3.

  • After setting the backup source on the backup path, if the cluster's Zone, IDC, or Region changes, such as a name change or deletion of a Zone/IDC/Region, the backup operation may fail, ultimately resulting in the backup task failure.

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 NFS. The zones that can access the current backup path are specified as z1, z2, and z3. When the Root Service selects nodes to execute the backup task, it prioritizes nodes in zones z1 and z2 with higher priority.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'file:///data/nfs/backup/data?zone=z1,z2;z3' TENANT = mysql_tenant;
    

  • The system tenant configures the backup path for the mysql_tenant tenant, with the backup medium as OSS. The backup file cleanup mode is set to delete, and the integrity of the backup data is verified using the MD5 algorithm.

    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 backup file cleanup mode is set to delete, and the integrity of the backup data is not verified.

    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 backup file cleanup mode is set to tagging, and the integrity of the archive file is verified using the MD5 algorithm. When the enable_worm=true parameter is configured for this path, the system will perform write and delete operations on this path according to the WORM specification.

    Notice

    Currently, OceanBase Database does not replace your compliance retention strategy. Before setting up the backup path, ensure that the OSS Bucket has been correctly configured with a retention strategy (WORM strategy) and 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 OSS. The backup file cleanup mode is set to delete, and the zones that can access the current backup path are specified as z1, z2, and z3. When the Root Service selects nodes to execute the backup task, it prioritizes nodes in zones z1 and z2 with higher priority.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'oss://oceanbase-test-bucket/backup/?host=***.aliyun-inc.com&access_id=***&access_key=***&delete_mode=delete&zone=z1,z2;z3' TENANT = mysql_tenant;
    

  • The system tenant configures the backup path for the mysql_tenant tenant, with the backup medium as AWS S3. The backup file cleanup mode is set to tagging, and the integrity of the backup data is verified using the CRC32 algorithm.

    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, 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, 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 backup file cleanup mode is set to delete, and the path is 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 is set to delete, and the verification algorithm is specified 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 NFS. The region that can access the current backup path is specified as region1.

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

  • The mysql_tenant tenant configures the backup path for itself, with the backup medium as OSS. The backup file cleanup mode is set to delete, and the integrity of the backup data is verified using the MD5 algorithm.

    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 backup file cleanup mode is set to delete, and the integrity of the backup data is not verified.

    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 backup file cleanup mode is set to tagging, and the integrity of the archive file is verified using the MD5 algorithm. When the enable_worm=true parameter is configured for this path, the system will perform write and delete operations on this path according to the WORM specification.

    Notice

    Currently, OceanBase Database does not replace your compliance retention strategy. Before setting up the backup path, ensure that the OSS Bucket has been correctly configured with a retention strategy (WORM strategy) and 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 OSS. The backup file cleanup mode is set to delete, and the IDC that can access the current backup path is specified as idc2.

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

  • The mysql_tenant tenant configures the backup path for itself, with the backup medium as AWS S3. The backup file cleanup mode is set to tagging, and the integrity of the backup data is verified using the CRC32 algorithm.

    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, 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, 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 the current tenant, with Azure Blob as the backup medium, cleanup mode set to delete, and the verification algorithm set to md5.

    Notice

    When using Azure Blob as the backup medium, you need to set the cluster-level parameter ob_storage_s3_url_encode_type. 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 the current tenant, with Azure Blob as the backup medium, cleanup mode set to delete, and the verification algorithm set to 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 path and archive path source configuration case

• BACKUP KEY

• BACKUP

• Preparation before backup