SingleStore Managed Service

Types of Backups

You can make full and incremental database backups. The following sections explain the differences between these backups and how they work.

Full Backups

A full backup contains a complete copy of a database.

Make a Full Backup

To make a full backup, run the BACKUP DATABASE command. Specify the name of the database to back up, followed by the directory on disk where the backup will reside. The following example makes a backup of test_db and stores it in the /test_db_backups_week_1 directory.

BACKUP DATABASE test_db to "/test_db_backups_week_1";

BACKUP DATABASE outputs one row containing the BACKUP_ID column. This column contains the id of the backup that was just made.

You can view the history all of the backups made in the cluster by querying information_schema.mv_backup_history. The output of the following query assumes that only one backup was made.

SELECT backup_id, incr_backup_id, database_name, start_timestamp
    FROM information_schema.mv_backup_history
    ORDER BY backup_id;
****
+-----------+----------------+-----------------+---------------------+
| backup_id | incr_backup_id | database_name   | start_timestamp     |
+-----------+----------------+-----------------+---------------------+
|         1 |           NULL | test_db         | 2019-08-04 00:00:00 |
+-----------+----------------+-----------------+---------------------+

You cannot save a full backup to a directory that already contains a full backup. Instead, save the full backup in a different directory or delete the contents in the original directory.

Restore a Full Backup

Notice

You can restore a full backup into 7.3 from all supported releases 5.0 and later.

In SingleStore Managed Service, creating or restoring a backup to/from a local filesystem is not supported.

To restore a full backup, use the RESTORE DATABASE command and specify the database name along with the directory where the backup is stored. The following example restores test_db from the test_db_backups_week_1 directory.

RESTORE DATABASE 'test_db' FROM "/test_db_backups_week_1";
Incremental Backups on Columnstores

Notice

This section does not apply to SingleStore Managed Service or to backups made using the SingleStore Tools sdb-admin create-backup command.create-backup

Also, this section does not apply to unlimited storage database backups.Local and Unlimited Database Storage Concepts

An incremental backup contains only the changes to columnstore data that were made since the previous backup was taken. Incremental backups on columnstore tables can only be targeted on Azure, Google Cloud Storage, NFS, S3, or S3 compatible storage. You cannot perform incremental backups to a local file system.

A backup set contains an initial full backup followed by any number of incremental backups. All of the backups in a backup set are stored in the same directory.

Notice

You can make an incremental backup on a database that has rowstore data, but a full copy of the rowstore data will be saved in the backup.

Making Incremental Backups

Suppose you want to store incremental backups for one week in the same backup set.

First, you make an initial full backup by running the BACKUP DATABASE command and include the WITH INIT clause. This is shown in the following example.

BACKUP DATABASE test_db WITH INIT to "/test_db_backups_week_1";

To make the first incremental backup, run the BACKUP DATABASE command and include the WITH DIFFERENTIAL clause. This is shown in the following example.

BACKUP DATABASE test_db WITH DIFFERENTIAL to "/test_db_backups_week_1";

To make the second incremental backup, run the same command again:

BACKUP DATABASE test_db WITH DIFFERENTIAL to "/test_db_backups_week_1";

To view the history of the incremental backups taken in the cluster, query information_schema.mv_backup_history. The output of the following query assumes that the only backups made are those in this section, Making Incremental Backups.

SELECT backup_id, incr_backup_id, database_name, start_timestamp
    FROM information_schema.mv_backup_history
    ORDER BY backup_id;
****
+-----------+----------------+-----------------+---------------------+
| backup_id | incr_backup_id | database_name   | start_timestamp     |
+-----------+----------------+-----------------+---------------------+
|        10 |             10 | test_db         | 2019-08-04 00:00:00 |
|        11 |             11 | test_db         | 2019-08-05 00:00:00 |
|        12 |             12 | test_db         | 2019-08-06 00:00:00 |
+-----------+----------------+-----------------+---------------------+
Restoring an Incremental Backup

Notice

You can restore an incremental backup into 7.1 from all supported releases 7.0 and later.

You can restore an incremental backup from one taken in 7.1 beta (i.e. 7.1.1), 7.0 RC (i.e. 7.0.7) and 7.0 beta 3 (i.e. 7.0.6).

In SingleStore Managed Service, creating or restoring a backup to/from a local filesystem is not supported.

To restore an incremental backup, run the RESTORE DATABASE command, including the WITH FILE clause. In this clause, specify the incr_backup_id of the incremental backup that you want to restore.

The following command assumes the backups in the previous section, Making Incremental Backups, have been made. The command restores the incremental backup with the incr_backup_id12. Internally, SingleStore DB uses the initial full backup with the incr_backup_id10 and applies the changes with the incr_backup_id11 and 12 to restore the backup.

RESTORE DATABASE test_db FROM "/test_db_backups_week_1" WITH FILE = 12;

You can restore any incremental backup from a backup set. You are not limited to restoring the latest incremental backup, as was shown in the previous example.

Making Frequent Incremental Backups and Occasional Full Backups

When you make an incremental backup, it may no longer contain columnstore data that was in a previous incremental backup. This data in the previous backup is not needed once the current backup is made; the data and will occupy extra disk space.

To save space, you can make incremental backups and occasional full backups. The following example explains a schedule you could use.

Example

  • Make incremental backups on every day of the week except Sunday.

  • On Sunday, take a initial full backup (using BACKUP DATABASE ... WITH INIT). This will create a new backup set.

  • Store the backups for each week in a separate directory.

  • When the full backup made on the Sunday of a new week succeeds, delete the backup directory for the previous week (if desired).

Incremental Backup Performance

Generally, making an incremental backup will be significantly faster than making a full backup. This is because an incremental backup contains only the changes that were made since the previous backup was taken.

If you run OPTIMIZE TABLE prior to making an incremental backup, the backup take significantly longer to complete than it normally would. This is because OPTIMIZE TABLE FULL sorts the entire columnstore table.