SET DATA_BACKUP_DEST

Description

The ALTER SYSTEM SET DATA_BACKUP_DEST statement configures the backup destination for a user tenant, including the data backup path and source-side configuration.

Limitations and considerations

• The sys tenant and Meta tenant do not support backup and restore, so you do not need to configure a backup destination for them.

• OceanBase Database currently supports NFS, Alibaba Cloud OSS, Azure Blob (supported starting from V4.2.5 BP6 in V4.2.5), AWS S3, and S3-compatible object storage such as Huawei OBS, Google GCS, and Tencent Cloud COS as backup media. Some backup media may need to meet basic requirements before use. For the specific requirements of each backup medium, see Backup media requirements in Overview of physical backup and restore.

Privilege requirements

The statement must be executed by the root user (root@sys) of the sys tenant or the administrator user of each tenant:

• 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];

Parameter description

data_backup_path

Specifies the data backup path and source-side configuration. The following describes the data_backup_path format for different media types.

NFS

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

Parameter descriptions are as follows:

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

• zone, idc, and region: specify the source side of the backup path, that is, the range of nodes that can access this path and their access priority. These parameters are optional and are separated from the backup path by a question mark (?). You can configure zone, idc, or region at only one level, or leave it unset. By default, it is not set.

  Note

  In OceanBase Database V4.2.5, starting from V4.2.5 BP2, you can configure the source side of a backup path.

  • When you configure zone, idc, or region for a backup path, the Root Service leader node must be in the specified zone, idc, or region. Otherwise, the backup task cannot write backup metadata to the target backup path, and the backup task fails. We recommend that the backup path source-side configuration include the Primary Zone range.

  • If you need to configure the Region or IDC level, run ALTER SYSTEM MODIFY ZONE zone_name SET REGION=region_name; or ALTER SYSTEM MODIFY ZONE zone_name SET IDC=idc_name; first to set the Region or IDC information. We recommend configuring the source side at the IDC level. This allows backup tasks to use an IDC with abundant network bandwidth, gives backup tasks a wider range of candidate nodes, and frees you from tracking zone changes within an IDC in the cluster.

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

  • To specify multiple zones, IDCs, or regions, separate values with commas (,) and semicolons (;):

    • A comma (,) means that when Root Service selects nodes to execute backup tasks, nodes in the zones/IDCs/regions on both sides have the same priority.
    • A semicolon (;) means that nodes in the zones/IDCs/regions on the left have higher priority than those on the right. Root Service preferentially selects nodes in the zones/IDCs/regions on the left to execute backup tasks.

    For example, zone=z1,z2;z3 specifies z1, z2, and z3 as the source side for backup. z1 and z2 have the same priority, and both have higher priority than z3.

  • After you configure the source side on a backup path, if the zone, IDC, or region in the cluster changes, such as a name change or deletion of a zone, IDC, or region, backup operations may fail and the backup task may fail.

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}]';

# S3-compatible object storage
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}]';

Parameter descriptions are as follows:

• host: the domain name used to access the object storage service.

  For Azure Blob, the value of host can be obtained from the container properties or the storage account connection string. The prefix of host must be http:// or https://. Because Azure Blob enables secure transfer by default, it is accessed through https:// by default. To access it through http://, you must disable the secure transfer option for Azure Blob. For more information, see Azure Blob documentation.

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

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

• delete_mode: configures the cleanup mode of backup files in the backup path. This optional parameter is supported for OSS, AWS S3, COS (accessed through the S3 protocol), and Azure Blob. It is connected to other parameters with &. This parameter supports the following values:

  • delete: in this mode, the system directly deletes backup files that meet the cleanup requirements.

    After this mode is configured, when backup files are cleaned up automatically, the system directly deletes backup files that meet the cleanup requirements.

  • tagging: in this mode, the system sets tags on backup files that meet the cleanup requirements. The backup files are retained.

    After this mode is configured, when backup files are cleaned up automatically, the system sets tags on backup files that meet the cleanup requirements. The tag key is "delete_mode" and the value is "tagging", so that you can manage the lifecycle of these files on object storage based on the tags.

• checksum_type: specifies the algorithm used to verify the integrity of backup data. If this parameter is not explicitly specified, all object storage services use the MD5 algorithm by default. Supported values vary by object storage service:

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

    Notice

    MD5 verification does not take effect for the GetObject interface.

  • no_checksum: does not verify the integrity of backup data. Only OSS and Azure Blob support this value. For object storage accessed through the S3 protocol, including AWS S3, OBS, GCS, and COS, this value is not supported because of S3 SDK limitations.

  • crc32: uses the CRC32 algorithm to verify the integrity of backup data. Only AWS S3 supports this value.

• enable_worm: optional. When the backup destination is an Alibaba Cloud OSS bucket with a compliance retention policy (WORM) configured, set this parameter to true.

  OSS retention policies provide WORM (Write Once Read Many) behavior, allowing data to be stored and used in a non-deletable and non-tamperable manner. After you configure and lock a retention policy for a bucket, you can upload and read objects in that bucket only before the retention period expires. After the retention period expires, the objects can be modified or deleted.

  Notice

  • In OceanBase Database V4.2.5, starting from V4.2.5 BP7, you can specify the enable_worm parameter.
  • OceanBase Database does not configure a compliance retention policy for you. Before you set the backup path, make sure that the OSS bucket retention policy (WORM policy) has been properly configured and locked.
  • If the bucket corresponding to the path to be configured has a retention policy configured and locked, but enable_worm=true is not specified when you configure the backup destination, the statement fails and prompts you to set enable_worm=true.
  • After the enable_worm parameter is set, it cannot be changed.
  • After the backup path is set, if a WORM policy is configured and locked for the bucket corresponding to the path, the backup task may fail with error -9140 because files under the target path cannot be modified or deleted. You can view the error code in the CDB_OB_BACKUP_DELETE_JOB_HISTORY view (sys tenant) or the DBA_OB_BACKUP_DELETE_JOB_HISTORY view (user tenant). The error message is the object is locked by worm. If a backup task fails because of the WORM policy, you must configure a new backup path and specify enable_worm=true.
  • When you use a bucket with a WORM policy as the backup path, we recommend that you use the tagging cleanup mode.

  The enable_worm parameter supports the following values:

  • true: yes. OceanBase Database performs write and delete operations on this path according to WORM rules. When you set this value to true, you must also explicitly set checksum_type=md5.

  • false: no. If this parameter is not explicitly specified, the default value is false.

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

• addressing_model: sets the URL format used to access object storage. This optional parameter applies to object storage accessed through the S3 protocol, such as OBS, GCS, and COS. It is connected to other parameters with &. This parameter supports the following values:

  • virtual_hosted_style: the default value. Object storage is accessed in virtual-hosted style.
  • path_style: object storage is accessed in path style.

  For example, the following statement shows how a user tenant configures OBS as the backup medium and accesses OBS in virtual-hosted style:

  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 side of the backup path, that is, the range of nodes that can access this path and their access priority. These parameters are optional and are connected to other parameters with &. You can configure zone, idc, or region at only one level, or leave it unset. By default, it is not set.

  Note

  In OceanBase Database V4.2.5, starting from V4.2.5 BP2, you can configure the source side of a backup path.

  • When you configure zone, idc, or region for a backup path, the Root Service leader node must be in the specified zone, idc, or region. Otherwise, the backup task cannot write backup metadata to the target backup path, and the backup task fails. We recommend that the backup path source-side configuration include the Primary Zone range.

  • If you need to configure the Region or IDC level, run ALTER SYSTEM MODIFY ZONE zone_name SET REGION=region_name; or ALTER SYSTEM MODIFY ZONE zone_name SET IDC=idc_name; first to set the Region or IDC information. We recommend configuring the source side at the IDC level. This allows backup tasks to use an IDC with abundant network bandwidth, gives backup tasks a wider range of candidate nodes, and frees you from tracking zone changes within an IDC in the cluster.

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

  • To specify multiple zones, IDCs, or regions, separate values with commas (,) and semicolons (;):

    • A comma (,) means that when Root Service selects nodes to execute backup tasks, nodes in the zones/IDCs/regions on both sides have the same priority.
    • A semicolon (;) means that nodes in the zones/IDCs/regions on the left have higher priority than those on the right. Root Service preferentially selects nodes in the zones/IDCs/regions on the left to execute backup tasks.

    For example, zone=z1,z2;z3 specifies z1, z2, and z3 as the source side for backup. z1 and z2 have the same priority, and both have higher priority than z3.

  • After you configure the source side on a backup path, if the zone, IDC, or region in the cluster changes, such as a name change or deletion of a zone, IDC, or region, backup operations may fail and the backup task may fail.

Examples

• Sys tenant

  • The sys tenant configures the backup path for tenant mysql_tenant. The backup medium is NFS.

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

  • The sys tenant configures the backup path for tenant mysql_tenant. The backup medium is NFS. Zones z1, z2, and z3 can access this backup path. When Root Service selects nodes to execute backup tasks, it preferentially selects nodes in the higher-priority zones z1 and z2.

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

  • The sys tenant configures the backup path for tenant mysql_tenant. The backup medium is OSS. The backup file cleanup mode is delete, and the MD5 algorithm is used to verify the integrity of 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 sys tenant configures the backup path for tenant mysql_tenant. The backup medium is OSS. The backup file cleanup mode is delete, and the integrity of 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 sys tenant configures the backup path for tenant mysql_tenant. The backup medium is OSS. The backup file cleanup mode is delete. Zones z1, z2, and z3 can access this backup path. When Root Service selects nodes to execute backup tasks, it preferentially selects nodes in the higher-priority zones z1 and z2.

    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 sys tenant configures the backup path for tenant mysql_tenant. The backup medium is OSS. The backup file cleanup mode is tagging, and the MD5 algorithm is used to verify the integrity of backup data. When enable_worm=true is configured for this path, the system performs write and delete operations on this path according to WORM rules.

    Notice

    OceanBase Database does not configure a compliance retention policy for you. Before you set the backup path, make sure that the OSS bucket retention policy (WORM policy) has been properly configured 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 sys tenant configures the backup path for tenant mysql_tenant. The backup medium is AWS S3. The backup file cleanup mode is tagging, and the CRC32 algorithm is used to verify the integrity of 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 sys tenant configures the backup path for tenant mysql_tenant. The backup medium is OBS and is accessed through 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 sys tenant configures the backup path for tenant mysql_tenant. The backup medium is GCS and is accessed through 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 sys tenant configures the backup path for tenant mysql_tenant. The backup medium is COS. The backup file cleanup mode is delete, and COS is accessed through the S3 protocol.

    Notice

    When you use COS as the backup medium, you must first set the cluster-level parameter ob_storage_s3_url_encode_type. For detailed steps, see Preparation 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 sys tenant configures the backup path for tenant mysql_tenant. The backup medium is Azure Blob. The cleanup mode is delete, and the verification algorithm is 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

  • Tenant mysql_tenant configures the backup path for the current tenant. The backup medium is NFS.

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

  • Tenant mysql_tenant configures the backup path for the current tenant. The backup medium is NFS. Region region1 can access this backup path.

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

  • Tenant mysql_tenant configures the backup path for the current tenant. The backup medium is OSS. The backup file cleanup mode is delete, and the MD5 algorithm is used to verify the integrity of 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 configures the backup path for the current tenant. The backup medium is OSS. The backup file cleanup mode is delete, and the integrity of 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 configures the backup path for the current tenant. The backup medium is OSS. The backup file cleanup mode is delete. IDC idc2 can access this 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';
    

  • Tenant mysql_tenant configures the backup path for the current tenant. The backup medium is OSS. The backup file cleanup mode is tagging, and the MD5 algorithm is used to verify the integrity of backup data. When enable_worm=true is configured for this path, the system performs write and delete operations on this path according to WORM rules.

    Notice

    OceanBase Database does not configure a compliance retention policy for you. Before you set the backup path, make sure that the OSS bucket retention policy (WORM policy) has been properly configured 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 configures the backup path for the current tenant. The backup medium is AWS S3. The backup file cleanup mode is tagging, and the CRC32 algorithm is used to verify the integrity of 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 configures the backup path for the current tenant. The backup medium is OBS and is accessed through 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 configures the backup path for the current tenant. The backup medium is GCS and is accessed through 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 configures the backup path for the current tenant. The backup medium is COS. The backup file cleanup mode is delete, and COS is accessed through the S3 protocol.

    Notice

    When you use COS as the backup medium, you must first set the cluster-level parameter ob_storage_s3_url_encode_type. For detailed steps, see Preparation 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';
    

  • User tenant mysql_tenant configures the backup path for the current tenant. The backup medium is Azure Blob. The cleanup mode is delete, and the verification algorithm is 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