ALTER/CHANGE/MODIFY EXTERNAL_STORAGE_DEST

Purpose

This statement is used to change the source configuration of the backup or archive path for the current tenant, including the zone, idc, or region configuration, I/O throttling parameters (max_iops and max_bandwidth), and key information such as access_id and access_key. After the modification, the new configuration will overwrite the original configuration.

Limitations and considerations

• Only user tenants can execute this statement. System tenants (sys tenants) cannot execute this statement.

• Before modifying the zone, idc, or region configurations of a path, 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 IDC level source configuration. This allows the backup task to be executed in an IDC with more network bandwidth resources, providing a wider range of optional nodes. Additionally, users do not need to monitor changes in zones within the IDC of the cluster.

• When modifying the source configuration of the backup path, if a backup task is currently running, the running task will take at least a few minutes to detect the configuration changes. Therefore, during the configuration modification period, nodes outside the configured scope may still access the backup path, leading to cross-data center or cross-region traffic.

• Only object storage paths support changing the I/O throttling parameters (max_iops and max_bandwidth) as well as the access_id and access_key information. If you set I/O throttling parameters (max_iops and max_bandwidth) or access_id and access_key information for an NFS path, the system returns an unsupported error with the error code 1235.

• When setting the ACCESS_INFO attribute (access_id and access_key information) for an object storage path, if the parameter format is incorrect (for example, there are extra = signs, or access_id or access_key is missing), the system returns the OB_OBJECT_STORAGE_PERMISSION_DENIED error (error code -9116).

• When accessing an object path using the Assume Role method for the backup path or archive path:

  • If you set ACCESS_INFO = 'access_id=new_access_id&access_key=new_access_key', the system returns an error.

  • If you only set the I/O throttling parameters in ATTRIBUTE without setting the ACCESS_INFO attribute, the system may prompt that it is unsupported or that the settings do not take effect.

• You can set the same ATTRIBUTE + ACCESS_INFO attributes multiple times for the same path.

Privilege requirements

The user who executes this statement must have the ALTER SYSTEM privilege. For more information about the privileges of OceanBase Database, see Privilege classification in MySQL mode.

Syntax

ALTER SYSTEM {ALTER|CHANGE|MODIFY} EXTERNAL_STORAGE_DEST 
  PATH [=] 'path' 
  SET [ATTRIBUTE = '[{zone=zone_name | idc=idc_name | region=region_name}][&max_iops=iops_num][&max_bandwidth=bandwidth_num]']
  [ACCESS_INFO = 'access_id=new_access_id&access_key=new_access_key'];

Parameters

Examples

Modify the source configuration of an archive path

Assume that the source configuration of an archive path is zone=z1,z2,z3. You can change the source configuration to region=region1. After the configuration is changed, only nodes in region1 can access the path.

• OSS

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'region=region1';
  

• NFS

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='file:///data/nfs/backup/archive' SET ATTRIBUTE = 'region=region1';
  

• S3

  obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com' SET ATTRIBUTE = 'region=region1';
  

• Object storage services that support the S3 protocol (OBS, GCS, and COS)

  obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com' SET ATTRIBUTE = 'region=region1';
  

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com' SET ATTRIBUTE = 'region=region1';
  

  obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=cos.ap-xxxx.myqcloud.com' SET ATTRIBUTE = 'region=region1';
  

Modify the source configuration of a backup path

Assume that the source configuration of a backup path is zone=z1,z2;z3. You can change the source configuration to idc=idc1;idc2. After the configuration is changed, nodes in idc1 and idc2 can access the path. In addition, nodes in idc1 have higher priority than those in idc2.

• OSS

  obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'idc=idc1;idc2';
  

• NFS

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='file:///data/nfs/backup/data' SET ATTRIBUTE = 'idc=idc1;idc2';
  

• S3

  obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com' SET ATTRIBUTE = 'idc=idc1;idc2';
  

• Object storage services that support the S3 protocol (OBS, GCS, and COS)

  obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=obs.****.myhuaweicloud.com' SET ATTRIBUTE = 'idc=idc1;idc2';
  

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=https://storage.googleapis.com' SET ATTRIBUTE = 'idc=idc1;idc2';
  

  obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com' SET ATTRIBUTE = 'idc=idc1;idc2';
  

Change the access_id and access_key of an archive path

• OSS

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
  

  • S3

    obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

  • Object storage services that support the S3 protocol (OBS, GCS, and COS)

    obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

    obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

    obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=cos.ap-xxxx.myqcloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

Change the access_id and access_key of a backup path

• OSS

  obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
  

  • S3

    obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

  • Object storage services that support the S3 protocol (OBS, GCS, and COS)

    obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=obs.****.myhuaweicloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

    obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=https://storage.googleapis.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

    obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com' SET ACCESS_INFO = 'access_id=******&access_key=******';
    

Set the I/O throttling parameters (max_iops and max_bandwidth) of an archive path

You can set both the max_iops and max_bandwidth parameters, or set only the max_iops or max_bandwidth parameter. Only object storage paths support I/O throttling parameters. NFS paths do not support I/O throttling parameters.

• OSS

  Set both the max_iops and max_bandwidth parameters. Example:

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

  Set only the max_iops parameter. Example:

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000';
  

  Set only the max_bandwidth parameter. Example:

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_bandwidth=1000mb';
  

• S3

  Set both the max_iops and max_bandwidth parameters. Example:

  obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=s3.<region>.amazonaws.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

• Object storage services that support the S3 protocol (OBS, GCS, and COS)

  Set both the max_iops and max_bandwidth parameters. Example:

  obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=obs.****.myhuaweicloud.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=https://storage.googleapis.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

  obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/archive?host=cos.ap-xxxx.myqcloud.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

Set I/O throttling parameters for backup paths (max_iops and max_bandwidth)

You can set both the max_iops and max_bandwidth parameters, or set either one of them. I/O throttling parameters can be set only for object storage paths, not for NFS paths.

• OSS

  You can set both the max_iops and max_bandwidth parameters, as shown in the following example.

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

  You can set the max_iops parameter, as shown in the following example.

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_iops=1000';
  

  You can set the max_bandwidth parameter, as shown in the following example.

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' SET ATTRIBUTE = 'max_bandwidth=1000mb';
  

• S3

  You can set both the max_iops and max_bandwidth parameters, as shown in the following example.

  obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=s3.<region>.amazonaws.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

• Object storage services compatible with the S3 protocol (OBS, GCS, and COS)

  You can set both the max_iops and max_bandwidth parameters, as shown in the following example.

  obclient(root@mysql001)[(none)]> ALTER SYSTEM CHANGE EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=obs.****.myhuaweicloud.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

  obclient(root@mysql001)[(none)]> ALTER SYSTEM ALTER EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test-bucket/backup/data?host=https://storage.googleapis.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

  obclient(root@mysql001)[(none)]> ALTER SYSTEM MODIFY EXTERNAL_STORAGE_DEST PATH='s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com' SET ATTRIBUTE = 'max_iops=1000&max_bandwidth=1000mb';
  

References

• Modify the source backup path

• Backup path and archive path source configuration examples

• Update the key information of an object storage service