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 for data backups.

Limitations and considerations

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

• OceanBase Database currently supports the following backup media: NFS, Alibaba Cloud OSS, Azure Blob (supported from V4.2.5 BP6), 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

The root user of the sys tenant (root@sys) or the administrator of each tenant must execute this statement. Specifically:

• In MySQL mode, the default administrator is the root user.
• In Oracle mode, the default administrator 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 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[?{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 for storing data backups.

• 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 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 not set any of them. By default, none of them are set.

  Note

  For OceanBase Database V4.2.5, the source of the backup path can be specified starting from V4.2.5 BP2.

  • When you specify the zone, idc, or region configuration of 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 want 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 that you set the source of the backup path to the IDC level. This way, the backup task can be executed in an IDC with abundant network bandwidth resources, and the backup task has 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 message indicating that the source information does not exist (error code -9133) will be returned.

  • If you want 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 zones/IDCs/regions on the left have a higher priority than those on the right. In other words, the backup task will prioritize nodes in the zones/IDCs/regions 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 their priority is higher than that of z3.

  • After setting the source of 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 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}][&enable_worm={true | false}][&{zone=zone_name | idc=idc_name | region=region_name}]';

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

The following table describes the parameters in the preceding statements.

• 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 attributes or 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 accesses data by using https://. If you want to access data by using 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 backup files in the backup path. This parameter is optional for Alibaba Cloud OSS, Amazon S3, object storage services compatible with the S3 protocol, and Azure Blob. The parameter is separated from other parameters by the & symbol. The parameter can be set to one of the following values:

  • 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 clean up backup files automatically.

  • tagging: specifies that the system sets a tag for backup files that meet the cleanup requirements. The 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 clean up backup files automatically. The tag has the key set to "delete_mode" and the value set to "tagging". This way, you can manage the lifecycle of these files by using the tags in the object storage.

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

  • 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 Alibaba Cloud OSS and Azure Blob. For object storage services that support the S3 protocol, such as Amazon S3, OBS, GCS, and COS, this parameter is not supported. This is because the S3 SDK does not support this parameter.

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

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

  The retention policy of Alibaba Cloud OSS has the WORM (Write Once Read Many) feature. This feature allows you to save and use data in an unchangeable and non-deletable 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

  • Starting from V4.2.5 BP7, the enable_worm parameter is supported in the V4.2.5 version.
  • Before you set a backup path, make sure that the retention strategy (WORM strategy) of the OSS bucket is properly set and locked. OceanBase Database will not replace your settings for the retention strategy.
  • If the retention strategy of the bucket corresponding to the path to be set is set and locked, and the enable_worm=true option is not specified when you configure the backup destination, an error is returned after the statement is executed, indicating that you must set enable_worm=true.
  • Once the enable_worm parameter is set, it cannot be changed.
  • After a backup path is set, if the WORM strategy is set and locked for the bucket corresponding to the path, the backup task may fail because it cannot modify or delete files in the target path. The error code is -9140 (you can view the error code in the CDB_OB_BACKUP_DELETE_JOB_HISTORY view (sys tenant) or DBA_OB_BACKUP_DELETE_JOB_HISTORY view (user tenant)), and the error message is the object is locked by worm. If a backup task fails due to the WORM strategy, you must reconfigure a new backup path and specify enable_worm=true.
  • When you use a bucket with a WORM strategy as a backup path, we recommend that you use the tagging mode for backup cleanup.

  The enable_worm parameter supports the following values:

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

  • false: No. The default value is false if this option is not explicitly specified.

• s3_region: the region of the Amazon S3 bucket. This parameter must be specified when the backup media 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 to other parameters with the & symbol. The current supported values are as follows:

  • 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 OBS as the backup media 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';
  

• zone, idc, and region: 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 with the & symbol. You can select only one of the three levels (zone, idc, or region) or not set any level. By default, no level is set.

  Note

  Starting from V4.2.5 BP2, the V4.2.5 version supports setting the source of the backup path.

  • When you specify the zone, idc, or region parameter, 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 you set the source of the backup path to include the Primary Zone.

  • If you want 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 that you set the source of the backup path to the IDC level. This way, you can use an IDC with abundant network bandwidth resources to perform the backup task, and the backup task has more optional nodes. In addition, you do not need to pay attention to the changes in zones within the IDC in the cluster.

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

  • If you want to set multiple zones, multiple IDCs, or multiple regions, separate the values with commas (,) and semicolons (;). The following describes the meanings of these symbols:

    • A comma (,) indicates that nodes in the zones/IDCs/regions on both sides have the same priority when you select nodes to execute the backup task.
    • A semicolon (;) indicates that nodes in the zones/IDCs/regions on the left have a higher priority than those on the right. In other words, nodes in the zones/IDCs/regions on the left are prioritized for the backup task.

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

  • After you set the source of the backup path, if the zone, IDC, or region of the cluster changes, such as the name change or deletion of a zone, IDC, or region, the backup task may fail.

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, and specifies the zones z1, z2, and z3 that can access the current backup path. The Root Service will prioritize nodes in zones z1 and z2 when selecting nodes for backup tasks.

    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 cleanup mode for backup files as delete, and uses the MD5 algorithm to verify the integrity of the backup data.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'oss://oceanbase-test-bucket/backup/?host=***.aliyun-inc.com&access_id=***&access_key=***&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 does not 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=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 delete, and specifies the zones z1, z2, and z3 that can access the current backup path. The Root Service will prioritize nodes in zones z1 and z2 when selecting nodes for backup tasks.

    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 OSS, the cleanup mode for backup files as tagging, and uses the MD5 algorithm to verify the integrity of the archive files. If enable_worm=true is configured for this path, the system will perform write and delete operations on this path according to the WORM specifications.

    Notice

    Currently, OceanBase Database does not replace your settings for compliance retention strategies. 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 as AWS S3, the cleanup mode for backup files as tagging, and uses the CRC32 algorithm to verify the integrity of the backup data.

    obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com&access_id=****&access_key=****&s3_region=****&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 accesses it 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 accesses it 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 accesses it 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-***.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 specifies 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 NFS, and specifies the region region1 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 as OSS, the cleanup mode for backup files as delete, and uses the MD5 algorithm to verify the integrity of the backup data.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'oss://oceanbase-test-bucket/backup/?host=***.aliyun-inc.com&access_id=***&access_key=***&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 does not 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=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 delete, and specifies the IDC idc2 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 as OSS, the cleanup mode for backup files as tagging, and uses the MD5 algorithm to verify the integrity of the archive files. If enable_worm=true is configured for this path, the system will perform write and delete operations on this path according to the WORM specifications.

    Notice

    Currently, OceanBase Database does not replace your settings for compliance retention strategies. 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 as AWS S3, the cleanup mode for backup files as tagging, and uses the CRC32 algorithm to verify the integrity of the backup data.

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

  • The mysql_tenant tenant configures the backup path for itself, with the backup medium as OBS, and accesses it 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 accesses it 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 user tenant configures the backup path for the tenant, specifies Azure Blob as the backup medium, sets the cleanup mode to delete, and specifies the verification algorithm as md5.

    Notice

    Before using Azure Blob as the backup medium, 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-***.myqcloud.com&access_id=***&access_key=***&delete_mode=delete';
    

  • The mysql_tenant user tenant configures the backup path for the tenant, specifies Azure Blob as the backup medium, sets the cleanup mode to delete, and specifies 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