CUBRID Backup & Restore – Part I (Backup)

The scope of this tutorial is to present the database backup & restore capabilities in CUBRID. The tutorial is split into two parts:

• Part I: Backup
• Part II: Restore

Overview

One of the main responsibilities of a CUBRID DBA is to perform regular backups of the databases, so that it can be restored successfully to a previous state in case of system failure.

A database backup is the procedure of storing:

• Database volumes
• Control files
• Log files

... and it is executed by using the cubrid backupdb utility or the CUBRID Manager.

CUBRID backupdb utility

backupdb is a CUBRID utility used for creating database backups, which is able to perform either:

• Online Backup
• Offline Backup
• Compressed Backup
• Parallel Backup

The backupdb utility can only be executed by a user who has the backup authorization (e.g. dba).

Syntax:

cubrid backupdb [ options ] database_name
[ options ]
-D | -r | -l | -o | -S | -C | -t | -z | -e |
--destination-path | --remove-archive | --level | --output-file | --SA-mode | --CS-mode | --thread-count | --compress | --except-active-log | --no-check

The following table shows options that can be used with backupdb utility. Remember - these options are case sensitive:

Usage examples

In this section we will present various examples of using the backupdb utility.

Specifying where to sore the backup files

The following is an example that uses the -D option to store backup files in the specified folder. The backup file folder must be specified before performing this job.

Note: If the -D option is not specified, backup files are stored in the folder specified in the databases.txt file.

>cubrid backupdb -D /home/cubrid/backup demodb

The following example stores backup files in the current directory by using the -D option. If you enter a period (.) following the -D option as an argument, the current directory is specified.

>cubrid backupdb -D . demodb

Removing archive logs after a backup (-r or --remove-archive)

If the database parameter media_failure_support is set to 1, when the active logs are full, they are written to a new archive log file. If a backup is performed in such a situation and backup volumes are created, backup logs created before the backup will not be used in subsequent backups. The -r option is used to remove archive log files that will not be used any more in subsequent backups after the current one is complete.

>cubrid backupdb -r demodb

It is recommended that the -r option only be used when a full backup is performed.

Storing page information necessary for replication (-sp or --safe-page-id)

The -sp option is mandatory when the -r option is used and replication is configured. Only archive logs that have smaller IDs than the ID of the latest log page are safely cleared and not the latest ID log. The -sp option makes the -r option limitedly performed, in order to reserve the log page information necessary for replication.

Also, you should specify the repl_safe_page database name as an argument.

>cubrid backupdb -r -sp 'repl_safe_page demodb' demodb

Performing a backup with the backup level specified (-l or --level)

The following example performs an incremental backup of the level specified by using the -l option. If the -l option is not specified, a full backup is performed.

>cubrid backupdb -l 1 demodb

Saving backup progress information in the specified file (-o or --output-file)

The following example writes the progress of the database backup to the info_backup file by using the -o option.

>cubrid backupdb -o info_backup demodb

The following is an example the info_backup file. You can see the information about the number of threads, compression method, backup start time, the number of permanent volumes, backup progress and backup end time:

[ Database(demodb) Full Backup start ]
- num-threads: 1
- compression method: NONE
- backup start time: Mon Jul 21 16:51:51 2008
- number of permanent volumes: 1
- backup progress status
-----------------------------------------------------------------------------
 volume name  | # of pages | backup progress status  | done
-----------------------------------------------------------------------------
 demodb_vinf  |  1 | ######################### | done
 demodb   |  25000 | ######################### | done
 demodb_lginf   |  1 | ######################### | done
 demodb_lgat  |  25000 | ######################### | done
-----------------------------------------------------------------------------
# backup end time: Mon Jul 21 16:51:53 2008
[Database(demodb) Full Backup end]

Performing a backup in standalone mode (-S or --SA-mode)

>cubrid backupdb -S demodb

Performing a backup in client/server mode (-C or --CS-mode)

>cubrid backupdb -C demodb

Parallel backup (-t or --thread-count)

The following example performs a parallel backup, with the number of threads specified by using the -t option. If the argument of the -t option is not specified, a parallel backup is performed by automatically assigning as many threads as CPUs in the system.

>cubrid backupdb -t 4 demodb

Compressed backup (-z or --compress)

The following example compresses the database and stores it in the backup file, by using the -z option.

Note: The size of the backup file and the time required for backup can be reduced by using the -z option.

>cubrid backupdb -z demodb

Enabling to exclude active log volumes (-e or --except-active-log)

The following example performs a backup, excluding active logs of the database by using the -e option. You can reduce the time required for backup by using the -e option. However, extra caution is required because active logs needed for completing a restore to the state of a certain point from the backup point are not included in the backup file, which may lead to an unsuccessful restore.

>cubrid backupdb -e demodb

Disabling a database consistency check (--no-check)

The following example performs a backup without checking the consistency of the database.

>cubrid backupdb --no-check demodb

Backup database using CUBRID Manager Client

To backup a database backup from the CUBRID Manager client, perform one of the following after login to the database:

• Click Backup Database from the toolbar.
• Right-click the database and then select Backup Database.
• Select Action -> Backup Database in the menu.

The backup operation can be performed even when the database server is running.

The following dialog is displayed, where you specify the backup options:

If a backup has been executed for the database, you can check the backup history from the Backup History Information tab. You can see information such as the backup level, last backup date, size of the backup file, backup file path as well as free space of the disk where the current database volume exists:

Backup Automation from CUBRID Manager Client

When you login to the database, you have access to Backup plan, under Job automation in the left navigation tree. The Add Backup Plan and Auto Backup Logs menus are available when you right-click on Backup plan:

If you want to activate a backup periodically with the CUBRID Manager client, you need to configure the values, using Add Backup Plan. The DBA can configure the backup automation while the Manager server is running - it is not affected by whether the database is running or not.

A backup automation is executed only when the Manager server is running.

Add Backup Plan

These are the options you can configure:

Auto Backup Log

This menu provides information about the error logs created during backup automation execution:

Edit/Delete Backup Plan

Right-click a desired backup automation in the left navigation tree and then select Edit Backup Plan, or Delete Backup Plan:

They are used for editing the backup automation jobs in the same way as for adding backup automation, or for deleting backup automation jobs.

Backup Strategy

The followings must be considered before planning and executing a database backup:

• Selecting the data to be backed up
• Determine whether it is valid data worth being preserved.
• Determine whether to back up the entire database or only part of it.
• Check whether there are other files to be backed up along with the database.
• Choosing a backup method
• Choose the backup method from one of incremental and online backups. Also, specify whether to use compression backup, parallel backup, and mode.
• Prepare backup tools and devices available.
• Determining backup time
• Identify the time when the least usage in the database occur.
• Check the size of the archive logs.
• Check the number of clients using the database to be backed up.

Example

The following is an example of a suggested CUBRID backup strategy/plan:

• In the example, a Full backup (Level 0) was performed on December 31st and January 5th.
• First incremental backups (Level 1):
• Backup level 1 is an incremental backup that only saves changes since the level 0 full backup.
• Note that the first incremental backups are attempted sequentially such as <1-1>, <1-2> and <1-3> in the example, but they are always performed relative to the level 0 full backup.
• Let’s suppose that backup files are created in the same directory. Then, if the first incremental backup <1-1> is performed on January 1st and then the first incremental backup <1-2> is attempted again on January 2nd, the incremental backup file created in <1-1> is overwritten. The final incremental backup file is created on January 3rd because the first incremental backup is performed again on that day.
• Since there can be a possibility that the database needs to be restored the state of January 1st or January 2nd, it is recommended to save the incremental backup files <1-1> and <1-2> separately in a storage media, before overwriting with the final incremental file.
• Second incremental backup (Level 2):
• Backup level 2 is an incremental backup that only saves data that have changed since the first incremental backup.
• A second incremental backup can be performed only after the first incremental backup. Therefore, the second incremental backup attempted on January 4th will succeed; the one attempted on January 6th fails.

Backup files created for backup levels 0, 1 and 2 may all be required for database restore. To restore the database to its state on January 4th, for example, you need the second incremental backup generated at <2-1>, the first incremental backup file generated at <1-3>, and the full backup file generated at <0-1>.

For a full restore, backup files from the most recent incremental backup file to the earliest created full backup file are required.

This concludes the 1st part of the CUBRID Backup & Restore tutorial, focused on presenting the CUBRID backup features.

Please access the 2nd part of the tutorial to learn about the restore capabilities built into CUBRID.

See also

• How to Backup a Database in CUBRID
• CUBRID Backup & Restore – Part II (Restore)
• CUBRID Tutorials
• CUBRID Manual

Getting Help

If you have any questions related to CUBRID Backup, you can tweet to @cubrid or post your message on the CUBRID Forum.