SET DATA_BACKUP_DEST

Purpose

You can use this statement to configure a backup destination for a user tenant. The backup destination is a data backup path.

The sys tenant and meta tenants do not support backup and restore. You do not need to configure backup destinations for them.

Required privileges

Only the root user of the sys tenant (root@sys) or the administrator user of a user tenant can execute this statement.

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

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 for NFS backup

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

data_backup_path consists of the following parts:

• 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 for OSS backup

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

data_backup_path consists of the following parts:

• 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 for COS backup

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

data_backup_path consists of the following parts:

• 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 for S3 backup

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

data_backup_path consists of the following parts:

• 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

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

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

  • In the sys tenant, configure a backup destination for the mysql_tenant tenant. Specifically, set the destination path to an OSS path and the cleanup mode for backup files 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;
    

  • In the sys tenant, configure a backup destination for the mysql_tenant tenant. Specifically, set the destination path to a COS path and the cleanup mode for backup files 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.

  • In the sys tenant, configure a backup destination for the mysql_tenant tenant. Specifically, set the destination path to an S3 path and the cleanup mode for backup files 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

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

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

  • In the mysql_tenant tenant, configure a backup destination for the current tenant. Specifically, set the destination path to an OSS path and the cleanup mode for backup files 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';
    

  • In the oracle_tenant tenant, configure a backup destination for the current tenant. Specifically, set the destination path to a COS path and the cleanup mode for backup files 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.

  • In the mysql_tenant tenant, configure a backup destination for the current tenant. Specifically, set the destination path to an S3 path and the cleanup mode for backup files 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