Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

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:

Option Description
-D
--destination-path
Specifies the directory path name (or device name) where backup volumes are to be created.
The default value is the location of log_path parameter specified in the databases location file (databases.txt).
-r
--remove-archive
Removes unnecessary archive logs after the backup is complete.
-l
--level
Configures the backup level to 0, 1 or 2 – we will present later the options available for this parameter. The default value is a full backup (0).
-o
--output-file
Specifies the name of the file where operation information is to be saved.
-S
--SA-mode
Performs a backup in standalone mode. The default value is the one specified by the system parameter CUBRID_MODE.
-C
--CS-mode
Performs a backup in client/server mode. The default value is the one specified by the system parameter CUBRID_MODE.
-t
--thread-count
Specifies the maximum number of threads allowed for a parallel backup. The default value is the number of CPUs in the system.
-z
--compress
Performs a compressed backup.
-e
--except-active-log
Configures that active log volumes are not included in the backup.
--no-check Does not perform a consistency check on a database before making a backup.
-sp
--safe-page-id
This option is used with the -r option where replication is configured. The -r option must be used with the -sp option so that unnecessary archive logs can be cleared, while the necessary information for replication is stored.

Usage examples

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

Specifying where to store 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

CUBRID supports the following types of backups:

  • Full backup (backup level 0). Backup level 0 is a full backup that includes all database pages.
  • First incremental backup (backup level 1). Backup level 1 is an incremental backup that only saves changes since the level 0 full backup, and is called a "first incremental backup."
  • Second incremental backup (backup level 2). Backup level 2 is an incremental backup that only saves data that have changed since the first incremental backup, and is called a "second incremental backup."

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:

database-backup-options.png

Option Description
Database name The name of the database to backup.
Volume name Specifies the name of the backup volume.
Backup level Specifies the backup level.
If there is a backup volume displayed as Level 0, the administrator can choose only Level 0 or Level 1. If there are no previously performed backups, the administrator can choose only Level 0.
Backup directory Specifies the directory where backup files are to be saved.
Checking consistency of the database Checks consistency of the database to backup before the backup process. It is recommended to select this option.
Delete unnecessary log-archives Deletes unnecessary archive log files while restoring a database. Note that archive log files might be required if database restore does not go well due to backup file errors. When this option is selected while the database is set to the master server in the replication environment, the Maintain replication log option is checked automatically.
Number thread Specifies the number of threads to be used concurrently during the database backup. It is recommended to set the maximum number of threads to be the same as the number of CPUs. The default value is 0, in which case the number of threads is automatically determined.
Compress backup volumes Compresses the database backup. It is recommended to select this option.

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:

database-backup-history-info.png

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:

add-backup-plan.png

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

add-backup-plan-window.png

These are the options you can configure:

Option Description
Backup ID The name of the backup job. The backup ID must be unique in the database because multiple backup plans may exist in the same database.
Backup level You can choose from 0, 1, and 2.
Backup path Specifies the folder of the backup volume.
Period type You can select a backup period from options of Monthly, Weekly, Daily and a specific day.
Period detail You can set details for the period type you selected.
Backup hour Enter the time when the automated backup is to be executed. You must enter the time in hour and minute.
Store old backup file This option saves the current original backup volume file of the database in database_directory/backupold directory.
Delete archive volumes An option that deletes archive log volumes after backup. When this option is selected while the database is set to master server in the replication environment, volumes do not affect the replication will be deleted automatically.
Update statistics information Updates statistics information after backup.
Checking database consistency Checks database consistency during backup.
Use compress Uses compression during backup.
Number thread Specifies the number of threads to be used concurrently during backup.
Online backup Automated backup is executed only when the database is running. If the database is stopped, only error logs are recorded without backing up the database.
Offline backup Automated backup is executed only when the database stopped running. Forces the database to shut down if it is currently running, performs an automated backup, and then restarts the database.

Auto Backup Log

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

auto-backup-logs.png

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:

edit-delete-backup-plan.png

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:

backup-strategy.png

  • 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

Getting Help

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




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: