Preparations

Before you back up data, you must configure the backup destination and backup key.

Backup architecture

Followers are preferentially used for backup. The following figure shows the backup architecture.

Configure the backup destination

Before you back up data, you must configure the DATA_BACKUP_DEST parameter. The sys tenant is used to manage the cluster and does not contain user data or support backup and restore. Therefore, you do not need to configure the DATA_BACKUP_DEST parameter for the sys tenant.

Considerations

Make sure that the backup path for each tenant is a separate empty directory. You cannot configure the same backup path for different tenants.

Procedure

1. Log in to the database as an administrator of the sys tenant or a user tenant.

   Note

   The administrator user is the root user in MySQL mode and the SYS user in Oracle mode.

2. Configure the backup destination.

   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 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.

   • Execute the following statement in the sys tenant to configure the backup destination for a specified tenant:

     ALTER SYSTEM SET DATA_BACKUP_DEST= 'data_backup_path' TENANT = mysql_tenant;
     

   • Execute the following statement in a user tenant to configure its backup destination:

     ALTER SYSTEM SET DATA_BACKUP_DEST= 'data_backup_path';
     

   Notice

   After you upgrade OceanBase Database from V4.0.x to V4.1.0, you must change the backup path. However, when you upgrade OceanBase Database from V4.1.x to V4.2.0, you do not need to change the backup path.

   Here are some examples:

   OSS

   NFS

   COS

   S3

   Object storage services compatible with the S3 protocol

   When you use OSS as the backup destination, you can set the backup destination and the delete_mode parameter to configure the backup file cleanup mode. You can set the delete_mode parameter to delete or tagging. If you do not specify delete_mode, the default cleanup mode delete takes effect. For more information about the delete_mode parameter, see View data backup parameter settings.

   • To set an OSS bucket as the backup destination and configure the backup file cleanup mode for the mysql_tenant tenant from the sys tenant, execute the following statement:

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='oss://oceanbase-test-bucket/backup/data?
     host=****.aliyun-inc.com
     &access_id=****
     &access_key=****
     &delete_mode=delete' TENANT = mysql_tenant;
     

   • To set an OSS bucket as the backup destination and configure the backup file cleanup mode for the current user tenant, execute the following statement:

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='oss://oceanbase-test-bucket/backup/data?
     host=****.aliyun-inc.com
     &access_id=****
     &access_key=****
     &delete_mode=delete';
     

   Here, oss:// indicates that OSS is used as the backup destination type, the bucket name is oceanbase-test-bucket, the storage path in the bucket is /backup/data, and ? is used to separate other parameters of the path. The host parameter specifies the host address of the bucket. The access_id and access_key parameters specify the access key pair of the account to access OSS. The cleanup mode is set to delete.

   For more information about the automatic cleanup of backup data in delete or tagging mode, see Automatically clean up expired backup data.

   Notice

   When you use NFS as the backup destination, note that:

   • The value of data_backup_dest cannot be a string with a question mark (?).
   • data_backup_dest must be set to an absolute path to ensure that the OBServer node has the write privilege on the backup destination.
   • Make sure that all OBServer nodes are mounted to NFS of the same server and use the parameter settings recommended in this topic. For more information about the procedure of deploying NFS, see Deploy the NFS client.

   • To set an NFS bucket as the backup destination for the mysql_tenant tenant from the sys tenant, execute the following statement:

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

   • To set an NFS bucket as the backup destination for the current user tenant, execute the following statement:

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

   Here, file:// indicates that NFS is used as the backup destination, and the backup path is file:///data/nfs/backup/data.

   COS allows you to configure the cleanup mode of backup files by using the delete_mode parameter in the same way as with OSS.

   • To set a COS bucket as the backup destination for the mysql_tenant tenant from the sys tenant, execute the following statement:

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='cos://oceanbase-test-appid/backup/data?
     host=cos.ap-****x.myqcloud.com
     &access_id=****
     &access_key=****
     &appid=****' TENANT = mysql_tenant;
     

   • To set a COS bucket as the backup destination for the current user tenant, execute the following statement:

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='cos://oceanbase-test-appid/backup/data?
     host=cos.ap-****x.myqcloud.com
     &access_id=****
     &access_key=****
     &appid=****';
     

   Here, cos:// indicates that COS is used as the backup destination type, the bucket name is oceanbase-test-appid, the storage path in the bucket is /backup/data, and ? is used to separate other parameters of the path. The host parameter specifies the host address of the bucket, namely the endpoint (without the bucket name) of the bucket. The access_id and access_key parameters specify the access key pair of COS. The appid parameter is required, and it specifies the APPID of the Tencent Cloud account. The cleanup mode is set to delete.

   S3 allows you to configure the cleanup mode of backup files by using the delete_mode parameter in the same way as with OSS.

   Here are some examples:

   • To set an S3 bucket as the backup destination and specify the delete cleanup mode for the mysql_tenant tenant from the sys tenant, execute the following statement:

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

   • To set an S3 bucket as the backup destination and specify the delete cleanup mode for the current user tenant, execute the following statement:

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

   Here, s3:// indicates that S3 is used as the backup destination type, the bucket name is oceanbase-test-bucket, the storage path in the bucket is /backup/data, and ? is used to separate other parameters of the path. The host parameter specifies the domain name of S3. The access_id and access_key parameters specify the access key pair of the account to access S3. The s3_region parameter is required, and it specifies the region where the S3 bucket is located. The cleanup mode is set to delete.

   If you use OBS, GCS, COS, or another object storage service that is compatible with the S3 protocol and the behaviors of S3 APIs of OceanBase Database, OceanBase Database allows you to use the object storage service as the backup destination type and access the object storage in the same way as you access an S3 bucket.

   The following examples describe how to set an OBS bucket as the backup destination:

   • To set an OBS bucket as the backup destination for the mysql_tenant tenant from the sys tenant, execute the following statement:

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?
     host=obs.****.myhuaweicloud.com
     &access_id=****
     &access_key=****' TENANT = mysql_tenant;
     

   • To set an OBS bucket as the backup destination for the current user tenant, execute the following statement:

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?
     host=obs.****.myhuaweicloud.com
     &access_id=****
     &access_key=****';
     

   Here, s3:// indicates that an object storage service compatible with the S3 protocol is used as the backup destination type, the bucket name is oceanbase-test-bucket, the storage path in the bucket is /backup/data, and ? is used to separate other parameters of the path. The host parameter specifies the domain name of OBS. The access_id and access_key parameters specify the access key pair of the account to access OBS.

   The following examples describe how to set a GCS bucket as the backup destination:

   • To set a GCS bucket as the backup destination for the mysql_tenant tenant from the sys tenant, execute the following statement:

     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;
     

   • To set a GCS bucket as the backup destination for the current user tenant, execute the following statement:

     obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='s3://oceanbase-test-bucket/backup/data?
     host=https://storage.googleapis.com
     &access_id=****
     &access_key=****';
     

   Here, s3:// indicates that an object storage service compatible with the S3 protocol is used as the backup destination type, the bucket name is oceanbase-test-bucket, the storage path in the bucket is /backup/data, and ? is used to separate other parameters of the path. The host parameter specifies the domain name of GCS. The access_id and access_key parameters specify the access key pair of the account to access GCS.

   To set COS as the backup destination, perform the following steps:

   1. Query the value of the cluster-level parameter ob_storage_s3_url_encode_type.

      obclient> SHOW PARAMETERS LIKE '%ob_storage_s3_url_encode_type%';
      

      The query result is as follows:

      +-------+----------+----------------+----------+-------------------------------+-----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
      | zone  | svr_type | svr_ip         | svr_port | name                          | data_type | value   | info                                                                                                                                                                                        | section  | scope   | source  | edit_level        |
      +-------+----------+----------------+----------+-------------------------------+-----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
      | zone1 | observer | 172.xx.xxx.xxx |     2882 | ob_storage_s3_url_encode_type | NULL      | default | Determines the URL encoding method for S3 requests."default": Uses the S3 standard URL encoding method."compliantRfc3986Encoding": Uses URL encoding that adheres to the RFC 3986 standard. | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
      +-------+----------+----------------+----------+-------------------------------+-----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
      1 row in set
      

   2. If the value is default, change it to compliantRfc3986Encoding.

      obclient>ALTER SYSTEM SET ob_storage_s3_url_encode_type='compliantRfc3986Encoding';
      

      For more information about this parameter, see ob_storage_s3_url_encode_type.

   3. Specify the backup destination and use the delete_mode parameter to configure the cleanup mode for backup files. Here are some examples:

      To set a COS bucket as the backup destination and specify the delete cleanup mode for the mysql_tenant tenant from the sys tenant, execute the following statement:

      obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='LOCATION=s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com&access_id=***&access_key=***&delete_mode=delete' TENANT = mysql_tenant;
      

      To set a COS bucket as the backup destination and specify the delete cleanup mode for the current user tenant, execute the following statement:

      obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='LOCATION=s3://oceanbase-test/backup/data?host=cos.ap-xxxx.myqcloud.com&access_id=***&access_key=***&delete_mode=delete';
      

      Here, s3:// indicates that an object storage service compatible with the S3 protocol is used as the backup destination type, the bucket name is oceanbase-test, the storage path in the bucket is /backup/archive, and ? is used to separate other parameters of the path. The host parameter specifies the host address of the COS bucket, namely the endpoint (without the bucket name) of the bucket. The access_id and access_key parameters specify the access key pair of COS. The cleanup mode is set to delete.

3. Query the CDB_OB_BACKUP_PARAMETER view from the sys tenant for the backup paths of all tenants in the current cluster. For more information, see View data backup parameter settings.

Considerations

After you configure the DATA_BACKUP_DEST parameter, by default, the system creates a file named format in the directory to which the specified destination belongs. This file is used to verify the validity information of the backup destination and ensure the integrity of data in the destination. Therefore, observe the following considerations when you configure the DATA_BACKUP_DEST parameter:

• If the format file does not exist, the directory where the configured destination is located must be empty so that the DATA_BACKUP_DEST parameter can be set. * If this directory is not empty, the system returns the -9080 error to indicate that the format file does not exist.

• If the format file exists, its content must be verified so that the parameter can be set. Otherwise, the system returns the -9081 error, indicating that the types of the cluster, tenant, and backup destination in the format file do not match those of the current cluster, tenant, and backup destination.

• When you execute a backup job, the job fails to be initiated if the format file does not exist or fails the verification.

Back up a key

Before you back up data, you must check the encryption status of the source tenant. If transparent encryption is configured for the source tenant, you must back up the key for the source tenant.

1. Log in to the database as an administrator of the sys tenant or a user tenant.

2. Back up a key.

   • Execute the following statement to back up the key for a specified tenant from the sys tenant:

     ALTER SYSTEM BACKUP KEY TENANT = tenant_name TO 'backup_key_path' ENCRYPTED BY 'password';
     

   • Execute the following statement to back up the key for the current user tenant:

     ALTER SYSTEM BACKUP KEY TO 'backup_key_path' ENCRYPTED BY 'password';
     

   where

   • backup_key_path specifies the backup path of the key. This path is user-defined and cannot be the same as the data backup path or log archive path.

   • password specifies the encryption password for the backup path of the key.

   The following example backs up the key of the mysql_tenant tenant to the file:///data_backup_dest/key path from the sys tenant:

   obclient [(none)]> ALTER SYSTEM BACKUP KEY TENANT = mysql_tenant TO 'file:///data_backup_dest/key' ENCRYPTED BY '******';
   

3. Check the back path of the key in views.

   • Execute the following statement in the sys tenant to query the CDB_OB_BACKUP_STORAGE_INFO view for the backup path of the key:

     SELECT * FROM oceanbase.CDB_OB_BACKUP_STORAGE_INFO;
     

   • Execute the following statement in a user tenant, to query the DBA_OB_BACKUP_STORAGE_INFO view for the backup path of the key:

     MySQL mode

     Oracle mode

     The syntax is as follows:

     SELECT * FROM oceanbase.DBA_OB_BACKUP_STORAGE_INFO;
     

     The syntax is as follows:

     SELECT * FROM SYS.DBA_OB_BACKUP_STORAGE_INFO;
     

     For more information about the columns in the CDB_OB_BACKUP_STORAGE_INFO and DBA_OB_BACKUP_STORAGE_INFO views, see CDB_OB_BACKUP_STORAGE_INFO and DBA_OB_BACKUP_STORAGE_INFO.