Backups in Managed Service for MySQL®

Managed Service for MySQL® supports automatic and manual database backups.

Managed Service for MySQL® allows you to restore the cluster state to any point in time (Point-in-Time-Recovery, PITR) after the creation of the oldest full backup. This is achieved by supplementing the backup selected as the starting recovery point with entries from the binary logs of later cluster backups.

For example, if the backup operation ended on August 10, 2020 at 12:00:00 UTC, the current date is August 15, 2020, 19:00:00 UTC, and the most recent binary log was saved on August 15, 2020, 18:50:00 UTC, the cluster can be restored to any state between August 10, 2020, 12:00:01 UTC and August 15, 2020, 18:50:00 UTC, inclusive.

PITR is enabled by default.

When creating backups and restoring data from them to a given point in time, keep the following in mind:

• A binary log consists of files with a size of 100 MB, which are archived in a running cluster as soon as the desired size is reached, and then uploaded to the object storage. Transactions are only logged to the binary log entirely, so sometimes the file size exceeds the specified one and it takes more time to archive the files.

• It takes some time to create and upload a binary log archive to the object storage. This is why the cluster state stored in the object storage may differ from the actual one.

• When you restore a cluster from a backup, you create a new cluster with the backup data. If the folder has insufficient resources to create such a cluster, you will not be able to restore from the backup. The average backup recovery speed is 10 MBps per database core.

  When restored to the current state, the new cluster will match the state of:

  • Existing cluster at the time of recovery.
  • Deleted cluster at the time of archiving the last binary log.

You can learn more about PITR in the MySQL® documentation.

To restore a cluster from a backup, follow this guide.

Creating backupsCreating backups

You can create backups both automatically and manually; in both cases, you get a full physical backup of all databases.

You cannot disable automatic backups. However, for such backups, you can specify a time interval during which the backup will start when you create or update a cluster. The default time is 22:00 - 23:00 UTC (Coordinated Universal Time).

After a backup is created, it is compressed for storage.

In single-host clusters, you create a backup by reading data from the master host, whereas in multi-host clusters — from one of the replicas, since it is a resource-heavy operation. It assumes that:

• The replica with the highest backup priority is selected. You can set the priority when creating a cluster, adding new hosts, or modifying the settings of the existing ones. This defines which replica to use for backups. The minimum backup priority value is 0, while the maximum one is 100 and the default one is 0.
• If there are multiple replicas with the highest priority, a backup source is selected randomly out of them.

If the service is unable to create a backup using the selected replica, the backup operation will proceed with the master host.

Backups are only created on running clusters. If you do not use a Managed Service for MySQL® cluster 24/7, check the backup start time settings.

For more information about creating a backup manually, see Managing backups.

Storing backupsStoring backups

Storing backups in Managed Service for MySQL®:

• Backups are kept in Yandex Cloud internal storage as binaries and encrypted using GPG. Each cluster has its own encryption keys.

• In an existing cluster, you can set up the retention period for automatic backups ranging from 7 (default) to 60 days. Manual backups are stored with no time limit.

• After you delete a cluster, all its backups are kept for seven days.

• Backup storage is not subject to quotas or limits for cluster storage space.

• Backups are stored in object storage and do not take up space in cluster storage. If there are N free GB of space in the cluster, the storage of the first N GB of backups is free of charge.

  For more information, see Pricing policy.

Checking backup recoveryChecking backup recovery

To test how backup works, restore a cluster from a backup and check the integrity of your data.