SET DATA_BACKUP_DEST

Purpose

The ALTER SYSTEM SET DATA_BACKUP_DEST statement is used to configure the backup destination for a user tenant, including the backup path and source configuration.

Limitations and considerations

• Backup and restore are not supported for the sys tenant and the 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, AWS S3, and object storage services that are 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 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. Specifically:

• The default administrator user in MySQL mode is the root user.
• The default administrator user in Oracle mode is the SYS user.

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 accessible to OceanBase Database, used for storing data backups.

• zone, idc, and region: specify the source of the backup path, i.e., the range of nodes that can access this path and the access priority. These are optional parameters, separated from the backup path by a question mark (?). You can choose to specify only one of these parameters or none. By default, none of these parameters are specified.

  • When specifying the zone, idc, or region 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. It is recommended to 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. It is recommended to set the source of the backup path to the IDC level, as this allows the backup task to utilize an IDC with abundant network bandwidth resources, provides more optional nodes, and eliminates the need to monitor changes in Zones within the IDC of 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 specify multiple Zones, multiple IDCs, or multiple Regions, separate the values with commas (,) and semicolons (;). Where:

    • A comma (,) indicates that the nodes in the Zones/IDCs/Regions on both sides have the same priority when selecting nodes for 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, meaning the backup task will prioritize nodes in the Zone/IDC/Region on the left.

    For example, if you set it to zone=z1,z2;z3, it specifies z1, z2, and z3 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 Zone, IDC, or Region changes, such as name changes or deletion of Zones/IDCs/Regions, it may result in the backup task failing to execute normally, 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 related 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 properties or the connection string of the storage account. The host parameter must be prefixed with http:// or https://. By default, Azure Blob uses secure transmission, which means that it is accessed through https://. If you want to access it 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.

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

• delete_mode: specifies the cleanup mode for the backup path. This parameter is optional for OSS, AWS S3, COS (accessed through the S3 protocol), and Azure Blob. It is separated from other parameters by &. The parameter supports the following two values:

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

    When you clean up backup files in automatic mode, 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 clean up backup files in automatic mode, the system sets the Tag of the backup files that meet 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 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 storages. The following table describes the values supported by this parameter for different object storages.

  • 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 value is supported only by OSS and Azure Blob. For object storages accessed through the S3 protocol, including AWS S3, OBS, GCS, and COS, this value 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 value 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 retention policies have the WORM (Write Once Read Many) feature, which 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

  • Due to the current lack of compliance retention policy settings in OceanBase Database, ensure that the OSS Bucket has been correctly set 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 set and locked, but the enable_worm=true parameter is not specified during backup destination configuration, 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.
  • After setting the backup path, if the user sets and locks a WORM policy for the Bucket corresponding to the path, 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 that the object is locked by worm. If a backup task fails due to the WORM policy, the user needs to 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 supported values of the enable_worm parameter are as follows:

  • true: Yes. This indicates that OceanBase Database will perform write and delete operations on the path according to the WORM specification. When the value is set to true, the checksum_type=md5 parameter must also be explicitly specified.

  • 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 is a required parameter when the backup medium is AWS S3.

• addressing_model: The URL format for accessing the object storage service. This is an optional parameter for object storage services accessible via S3-compatible protocols (such as OBS, GCS, and COS), and it is connected to other parameters using the & symbol. The current supported values are:

  • virtual_hosted_style: The default value. This indicates that the object storage service is accessed in Virtual-hosted-style.
  • path_style: This 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 it in Virtual-hosted-style, the statement would be:

  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: These parameters specify the source of the backup path, indicating the node range that can access the path and the access priority. They are optional parameters connected to other parameters using the & symbol. You can select only one of the three levels (zone, idc, or region) or choose not to specify any.

  • 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 to write backup metadata to the target backup path, resulting in the failure of the backup task. It is recommended to set the backup path source 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. It is recommended to set the source configuration at the IDC level to utilize an IDC with abundant network bandwidth resources for executing 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 commas (,) and semicolons (;). Specifically:

    • A comma (,) indicates that the nodes in the left and right Zones/IDCs/Regions have the same priority when selecting nodes for executing 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 selecting nodes from the left Zone/IDC/Region.

    For example, if the setting is 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, it may result in the failure of the backup task.

Examples

• System tenant

  • The system tenant configures the backup path for the mysql_tenant tenant, with the backup medium being 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 being NFS. It specifies the zones z1, z2, and z3 as the zones that can access the current backup path. 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 being OSS. The cleanup mode for backup files is set to delete, and the MD5 algorithm is used to verify 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 being OSS. The cleanup mode for backup files 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 being OSS. The cleanup mode for backup files is set to tagging, and the MD5 algorithm is used to verify the integrity of the archive files. If the path is configured with enable_worm=true, the system will perform write and delete operations on the path in accordance with WORM specifications.

    Notice

    Currently, OceanBase Database does not replace your compliance retention strategy. Before setting the backup path, make sure 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 being OSS. The cleanup mode for backup files is set to delete, and the zones z1, z2, and z3 are specified as the zones that can access the current backup path. 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 being AWS S3. The cleanup mode for backup files is set to tagging, and the CRC32 algorithm is used to verify 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 being 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 being 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 being COS. The cleanup mode for backup files is set to delete, and the S3 protocol is used for access.

    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 being 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 being 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 being NFS. The region region1 is specified as the region that can access the current backup path.

    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 being OSS. The cleanup mode for backup files is set to delete, and the MD5 algorithm is used to verify 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 being OSS. The cleanup mode for backup files 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 being OSS. The cleanup mode for backup files is set to tagging, and the MD5 algorithm is used to verify the integrity of the archive files. If the path is configured with enable_worm=true, the system will perform write and delete operations on the path in accordance with WORM specifications.

    Notice

    Currently, OceanBase Database does not replace your compliance retention strategy. Before setting the backup path, make sure 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 being OSS. The cleanup mode for backup files is set to delete, and the IDC idc2 is specified as the IDC that can access the current backup path.

    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 being AWS S3. The cleanup mode for backup files is set to tagging, and the CRC32 algorithm is used to verify 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 being 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 being 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=****';
    

  • User tenant mysql_tenant configures the backup path for this tenant, with Azure Blob as the backup medium, cleanup mode set to delete, and the verification algorithm specified as md5.

    Notice

    When using Azure Blob as the backup medium, you must 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';
    

  • User tenant mysql_tenant configures the backup path for this tenant, with Azure Blob as the backup medium, cleanup mode set to delete, and the verification algorithm 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';
    

References

• Backup path and archive path source configuration case

• BACKUP KEY

• BACKUP

• Preparation before backup