MySQL Database Service: Snapshot your data – backups

On Oracle Cloud Infrastructure, aka OCI, MySQL Database Service, aka MDS uses the snapshot technology to backup your data.

I will explain how it works in the multiple part blog series. In part 1, we will deal with taking snapshots as backup. In part 2, we will see how to restore data and for which usage.

Snapshots

The Oracle Cloud Infrastructure Block Volume service provides you with the capability to group together multiple volumes in a volume group.

This is exactly what MDS uses to store the data.

We can easily see this from some global variables:

+-----------------------------+--------------------------------+
| Variable_name               | Value                          |
+-----------------------------+--------------------------------+
| datadir                     | /db/data/                      |
| innodb_data_home_dir        | /db/data/                      |
| innodb_log_group_home_dir   | /db/redo                       |
| innodb_tmpdir               | /db/tmp                        |
| log_bin_basename            | /db/binlogs/binary-log         |
| log_error                   | /db/log/error.log              |
| tmpdir                      | /db/tmp                        |
| ...
+-----------------------------+--------------------------------+

/db is the Block Volume Group containing all volumes.

This technique simplifies the process to create time-consistent backups. You can then restore an entire group of volumes from a volume group backup.

The snapshot is pushed to Object Storage (encrypted).

Backup

In MDS, it’s very easy to take a backup. You have the possibility to configure automatic backups (default) or do create such backup manually.

Those backups are used to restore the data on a new instance or to migrate to another shape.

You can only migrate to an equivalent or more powerful shape.

Backup Plan (automatic)

A backup plan can be configured during the instance’s creation or added at anytime after.

Automatic backups are retained for between 1 and 35 days. The default retention value is 7 days. Once defined, it is not possible to edit the retention period of an automatic backup.

If you delete a MDS System, automatic backups are also deleted. Not the manual ones.

This is how it looks like during the MDS instance creation:

It seems that the Backup Window can be set only at the instance’s creation, after it always stays 00:00 UTC.

When a Backup Plan is defined for a MDS System, you will see it in the Backups section with a blue information frame like this:

If no Backup Plan is configured, you will see an orange frame like this:

Manual Backups

In MDS, you also have the possibility to manually start a backup/snapshot of your database. This backup cab be initiated by an action in the console, or by a request made through the API. Manual backups can be retained for a minimum of 1 day and a maximum of 365 days.

Currently, there is a limit of 100 manual backups per tenancy. This limit can be increased per customer request.

Creating a backup from the OCI Dashboard is easy. You need to select the MySQL DB System you want to backup and ion the left, click on Backups, then just click on the “Create Manual Backup” button:

If you don’t have any backup of the DB System already, the only option is to create a Full Backup:

As you can see, you can specify the retention period and add tags if you want.

In case you already have a Full Backup, you also have the possibility to create Incremental Backups that will use less storage of course:

Manual Backup from CLI

It’s also possible to start a manual backup directly form the command line. Let’s use the Cloud Shell to illustrate this:

This will create a new backup and we can see it in the dashboard:

Backup Details

Every backup has interesting details:

You can see (blue lines) that the snapshot was very fast: 100G in 20secs. This is an online snapshot, the MySQL DB System doesn’t need to be stopped.

It’s also possible to change the name, the description and the retention days by clicking on Edit:

Conclusion

Backups on MDS are performed using snapshots. They are very quick and doesn’t require downtime.

Those backups can be also used to migrate to other shapes. On the next post, we will see how to Restore such backups.

Thank you for using MySQL and MySQL Database Service !