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.

• Restoring a cluster from a backup creates a new cluster with that backup’s data. If your folder lacks 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 point in time, the new cluster will reflect 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.

For clusters running an unsupported DBMS version, restoring from backups is not available.

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

Creating a backupCreating a backup

You can create backups automatically or manually; in either case, you get a full physical backup of all your 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 value 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. For this operation, the following requirements apply:

• 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. Minimum backup priority value: 0; maximum value: 100; default: 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 are not using your Managed Service for MySQL® cluster 24/7, check the settings of backup start time.

Learn about creating manual backups in Managing backups.

Storing a backupStoring a backup

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.

• Quotas and limits for cluster storage do not apply to backup storage.

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

  For more information, see the pricing policy.

Testing recovery from a backupTesting recovery from a backup

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