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
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).
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:
|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).
|Removes unnecessary archive logs after the backup is complete.|
|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).|
|Specifies the name of the file where operation information is to be saved.|
|Performs a backup in standalone mode. The default value is the one specified by the system parameter CUBRID_MODE.|
|Performs a backup in client/server mode. The default value is the one specified by the system parameter CUBRID_MODE.|
|Specifies the maximum number of threads allowed for a parallel backup. The default value is the number of CPUs in the system.|
|Performs a compressed backup.|
|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.|
|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.|
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
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 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:
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:
|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:
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.
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.
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.
- How to Backup a Database in CUBRID
- CUBRID Backup & Restore – Part II (Restore)
- CUBRID Tutorials
- CUBRID Manual