Understand MySQL backups in Aiven

Aiven for MySQL databases are automatically backed-up, with full backups daily, and binary logs recorded continuously. The number of stored backups and backup retention time depends on your Aiven service plan. Full backups are version-specific binary backups, which when combined with binlog allow for consistent recovery to a specific point in time (PITR).

important

One thing to consider is that you may modify the backup time configuration option in Advanced configuration in Aiven Console which will begin shifting the backup schedule to the new time. If there was a recent backup taken, it may take another backup cycle before it starts applying new backup time.

note

To be able to safely make backups, MySQL INSTANT ALTER TABLE always use the INPLACE or COPY algorithm instead of INSTANT. Specifying ALGORITHM=INSTANT does not fail but automatically falls back to INPLACE or COPY as needed.

MySQL backups and encryption

All Aiven for MySQL backups use the myhoard software to perform encryption. Myhoard utilizes Percona XtraBackup internally for taking a full (or incremental) snapshot of MySQL.

Since Percona XtraBackup 8.0.23 the --lock-ddl option is enabled by default. This ensures that DDL changes cannot be performed while a full backup process is ongoing. This is important to guarantee that the backup service is consistent and can be reliably used for restoration.

With this feature enabled, if you try to run CREATE, ALTER, DROP, TRUNCATE or another command during backup, you may receive the message Waiting for backup lock. In this case, wait till the backup is complete for running such operations.

Configure the backup schedule

Set the time of day when the daily backup is taken.

To edit the backup schedule for your service:

Console

1. In your service, click Backups.
2. Click Actions > Configure backup settings.
3. Click Add configuration options.
4. Add backup_hour and backup_minute, and set their values.
5. Click Save configuration.

Aiven API

Call the ServiceUpdate endpoint, and add the following properties to the user_config object:

curl --request PUT                                                        \
  --url https://api.aiven.io/v1/project/PROJECT_NAME/service/SERVICE_NAME \
  --header 'Authorization: Bearer YOUR_BEARER_TOKEN'                      \
  --header 'content-type: application/json'                               \
  --data '{
      "user_config": {
          "backup_hour": BACKUP_HOUR,
          "backup_minute": BACKUP_MINUTE
      }
  }'

Replace the following:

• SERVICE_NAME: the name of your service.
• PROJECT_NAME: the name of your project.
• BACKUP_HOUR: the hour when the service backup starts. Accepted values are integers between 0 and 23.
• BACKUP_MINUTE: the minute when the service backup starts. Accepted values are integers between 0 and 59.

Aiven CLI

Run the avn service update command, and add the following properties to the user_config object:

avn service update SERVICE_NAME \
  --project PROJECT_NAME        \
  --user-config '{
      "backup_hour": BACKUP_HOUR,
      "backup_minute": BACKUP_MINUTE
  }'

Replace the following:

• SERVICE_NAME: the name of your service.
• PROJECT_NAME: the name of your project.
• BACKUP_HOUR: the hour when the service backup starts. Accepted values are integers between 0 and 23.
• BACKUP_MINUTE: the minute when the service backup starts. Accepted values are integers between 0 and 59.

Terraform

Use the backup_hour and backup_minute attributes in your service resource to set the start time for backups.

If a backup was recently made, it can take another backup cycle before the new backup time takes effect.

More resources

• Use incremental backups
• Our blog post: MyHoard, your solution to MySQL backups and restoration
• Read about Aiven cloud security and data encryption