SET DATA_BACKUP_DEST

Purpose

You can use the ALTER SYSTEM SET DATA_BACKUP_DEST statement to configure a backup destination, namely, a data backup path, for a user tenant.

Limitations and considerations

• The backup and restore feature is not supported for the sys tenant and meta tenants. Therefore, you do not need to configure a backup destination for them.

• OceanBase Database allows you to use Network File System (NFS), Alibaba Cloud Object Storage Service (OSS), Tencent Cloud Object Storage (COS), Amazon Simple Storage Service (S3), and other object storage services that are compatible with the S3 protocol, such as Huawei Object Storage Service (OBS) and Google Cloud Storage (GCS), as the backup media. Some backup media can be used only after certain basic requirements are met. For more information about backup media, see Introduction to physical backup and restore.

Required privileges

You must execute this statement as the root user of the sys tenant (namely, root@sys) or as the administrator of a user tenant.

• 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

The following examples show how to configure data backup destinations in OceanBase Database when NFS, OSS, COS, and S3 are used as the backup media.

Parameter examples

Example of data_backup_path setting when NFS is used as the backup media

ALTER SYSTEM SET DATA_BACKUP_DEST = 'nfs://your-nfs-server/your-backup-path' [TENANT = your-tenant-name];

The data_backup_path setting is as follows:

• nfs://your-nfs-server/your-backup-path: the path of the NFS server accessible to OceanBase Database, which stores data backups.

Example of data_backup_path setting when OSS is used as the backup media

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' [TENANT = your-tenant-name];

The data_backup_path setting is as follows:

• oss://your-oss-bucket-name/your-backup-path: the name of the OSS bucket and the backup path, which stores data backups.
• host=oss-your-region.aliyuncs.com: the domain name used to access OSS. your-region must be replaced with the actual OSS region code.
• access_id=your-access-id and access_key=your-access-key: the AccessKey ID and AccessKey secret used for authentication.

Example of data_backup_path setting when COS is used as the backup media

ALTER SYSTEM SET DATA_BACKUP_DEST = 'cos://your-bucket-name/your-archive-path?host=cos.your-region.myqcloud.com&access_id=your-access-id&access_key=your-access-key&appid=your-appid' [TENANT = your-tenant-name];

The data_backup_path setting is as follows:

• cos://your-cos-bucket-name/your-backup-path: the name of the COS bucket and the backup path, which stores data backups.
• host=cos.your-region.myqcloud.com: the domain name used to access COS. your-region must be replaced with the actual COS region code.
• access_id=your-access-id and access_key=your-access-key: the AccessKey ID and AccessKey secret used for authentication.
• appid=your-appid: the AppID of your Tencent Cloud account.

Example of data_backup_path setting when S3 is used as the backup media

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' [TENANT = your-tenant-name];

The data_backup_path setting is as follows:

• s3://your-s3-bucket-name/your-backup-path: the name of the S3 bucket and the backup path, which stores data backups.
• host=s3.your-region.amazonaws.com: the domain name used to access S3. your-region must be replaced with the actual AWS region code.
• access_id=your-access-id and access_key=your-access-key: the AccessKey ID and AccessKey secret for AWS.
• s3_region=your-s3-region: the region where the S3 bucket is deployed.

Examples

• sys tenant

  • Configure a backup destination for the mysql_tenant tenant from the sys tenant. Specifically, set the backup destination to an NFS path.

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

  • Configure a backup destination for the mysql_tenant tenant from the sys tenant. Specifically, set the backup destination to an OSS path and set the backup file cleanup mode to delete.

    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' TENANT = mysql_tenant;
    

  • Configure a backup destination for the mysql_tenant tenant from the sys tenant. Specifically, set the backup destination to a COS path and set the backup file cleanup mode to delete.

    obclient [oceanbase]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'cos://oceanbase-test-bucket/backup/?host=cos.ap-xxx.myqcloud.com/&access_id=xxx&access_key=xxx&appid=xxx&delete_mode=delete' TENANT = mysql_tenant;
    

    Here, appid indicates the AppID of your Tencent Cloud account.

  • Configure a backup destination for the mysql_tenant tenant from the sys tenant. Specifically, set the backup destination to an S3 path and set the backup file cleanup mode to tagging.

    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' TENANT = mysql_tenant;
    

    Here, s3_region indicates the region where the S3 bucket is deployed.

• User tenant

  • Configure a backup destination for the mysql_tenant tenant from the current tenant. Specifically, set the backup destination to an NFS path.

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

  • Configure a backup destination for the mysql_tenant tenant from the current tenant. Specifically, set the backup destination to an OSS path and set the backup file cleanup mode to delete.

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

  • Configure a backup destination for the oracle_tenant tenant from the current tenant. Specifically, set the backup destination to a COS path and set the backup file cleanup mode to delete.

    obclient [SYS]> ALTER SYSTEM SET DATA_BACKUP_DEST = 'cos://oceanbase-test-bucket/backup/?host=cos.ap-xxx.myqcloud.com/&access_id=xxx&access_key=xxx&appid=xxx&delete_mode=delete';
    

    Here, appid indicates the AppID of your Tencent Cloud account.

  • Configure a backup destination for the mysql_tenant tenant from the current tenant. Specifically, set the backup destination to an S3 path and the backup file cleanup mode to tagging.

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

    Here, s3_region indicates the region where the S3 bucket is deployed.

References

• BACKUP KEY

• BACKUP

• Preparations