Backup by using commands

This topic describes how to use the backup feature of OceanBase Database.

Procedure

1. Log on to the database as the root user of the sys tenant.

2. Configure the backup destination.

   OceanBase Database allows you to use Network File System (NFS), Object Storage Service (OSS), and Cloud Object Storage (COS) of Tencent Cloud as the backup destination.

   Sample commands:

   • NFS

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

   • OSS

     obclient> ALTER SYSTEM SET backup_dest='oss://antsys-oceanbasebackup/backup_rd/?host=cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access_id=xxx&access_key=xxx';
     

   • Tencent Cloud COS

     obclient> ALTER SYSTEM SET backup_dest='cos://backup-1304745170/backup_rd/20210127?host=cos.ap-nanjing.myqcloud.com&access_id=xxx&access_key=xxx&appid=xxx';
     

     Notice

     When you use Tencent Cloud COS as the backup media, you must disable the list cache of the bucket to prevent backup errors.

3. Configure the backup mode and enable compression of archived logs. This step is optional.

   In this step:

   • You can set the backup mode to optional or mandatory.

     • The optional mode prioritizes user business. In this mode, logs may be recycled before they are archived, interrupting the backup.

     • The mandatory mode prioritizes backup. In this mode, you may be unable to write more data before existing data is backed up.

   • You can use the zstd_1.3.8 and lz4_1.0 algorithms to compress archived logs. The default compression algorithm is lz4_1.0.

   The following example selects the optional mode and enables the compression of archived logs:

   obclient> ALTER SYSTEM SET backup_log_archive_option = 'optional compression= enable';
   

   You can dynamically switch the backup mode and compression algorithm as needed. To switch the compression algorithm, run the following command:

   obclient> ALTER SYSTEM SET backup_log_archive_option='mandatory compression= zstd_1.3.8';
   
   obclient> ALTER SYSTEM SET backup_log_archive_option='mandatory compression= lz4_1.0';
   

   Note

   To disable compression of archived logs, run the following command:

   • In optional mode, you can either run the ALTER SYSTEM SET backup_log_archive_option = 'compression= disable'; command to disable compression, or run the ALTER SYSTEM SET backup_log_archive_option = 'mandatory compression= disable'; command to configure the backup mode and disable log compression.

   • In mandatory mode, you can only run the ALTER SYSTEM SET backup_log_archive_option = 'mandatory compression= disable'; command to configure the backup mode and disable log compression.

4. Run the following command to enable log backup:

   Note

   To save some time, we recommend that you trigger a minor compaction before enabling log backup. This is because log backup starts from the last minor compaction. For more information about how to trigger a minor compaction, see Manually trigger a minor compaction.

   obclient> ALTER SYSTEM ARCHIVELOG;
   

   After the archiving feature is enabled, OceanBase Database automatically backs up the transaction logs generated by the clusters to the specified destination on a regular basis.

5. Run the following command to check whether the log backup task has started.

   obclient> SELECT * FROM CDB_OB_BACKUP_ARCHIVELOG_SUMMARY;
   

   When STATUS is DOING, the log backup task has started.

6. You need to initiate a daily compaction and wait for it to complete before you can start data backup.

   1. Run the following command to initiate the daily compaction.

      obclient> ALTER SYSTEM MAJOR FREEZE;
      

   2. Run the following command to view the major compaction progress.

      obclient> SELECT * FROM __all_zone WHERE name='merge_status';
      

      The command output is:

      +----------------------------+----------------------------+-------+--------------+-------+------+
      | gmt_create                 | gmt_modified               | zone  | name         | value | info |
      +----------------------------+----------------------------+-------+--------------+-------+------+
      | 2020-05-26 17:50:11.107352 |  2020-05-26 17:50:45.871523 |       | merge_status |     0 | IDLE |
      | 2020-05-26 17:50::11.109678 |  2020-05-26 17:50:55.780264 | zone1 | merge_status |     0 | IDLE |
      +----------------------------+----------------------------+-------+--------------+-------+------+
      

      When info is IDLE, the major compaction is completed.

7. Run the following command to set a password for the backup. This step is optional.

   obclient> SET ENCRYPTION ON IDENTIFIED BY 'password' ONLY;
   

8. Run the following command to initiate a data backup task.

   obclient> ALTER SYSTEM BACKUP DATABASE;
   

   During the backup, you can run the following commands to view the status and details of the backup task:

   • View the ongoing backup task

     obclient> SELECT * FROM CDB_OB_BACKUP_PROGRESS; 
     

   • View the history of backup tasks

     obclient> SELECT * FROM CDB_OB_BACKUP_SET_DETAILS;
     

     The following table describes some fields in CDB_OB_BACKUP_SET_DETAILS.

     Field	Description
     OUTPUT_BYTES	The amount in bytes of data written to the external storage media.
     OUTPUT_RATE_BYTES	The total amount in bytes of data uploaded within one second.
     COMPRESSION_RATIO	The compression ratio, which is calculated by using the following formula: COMPRESSION_RATIO = OUTPUT_BYTES / INPUT_BYTES. INPUT_BYTES indicates the size of the current data to be backed up on the OBServer, which can be calculated based on the value of COMPRESSION_RATIO`` by using the following formula: INPUT_BYTES = OUTPUT_BYTES / COMPRESSION_RATIO.
     OUTPUT_BYTES_DISPLAY	The unit of OUTPUT_BYTES after conversion, which can be MB, GB, TB, or PB.
     OUTPUT_RATE_BYTES_DISPLAY	The unit of OUTPUT_RATE_BYTES after conversion, which can be MB, GB, TB, or PB.
     TIME_TAKEN_DISPLAY	The TIMESTAMP representation of (COMPLETION_TIME - START_TIME).