Long-term retention (LTR) leverages full database backups to enable point in time restore (PITR). It can be beneficial for you to employ LTR for several reasons, such as auditing, compliance, or data archiving. Azure Database for MySQL (both Flexible Server and Single Server) offers a retention period up to 35 days, but there may be situations in which you want to retain the backups for longer periods. Azure offers a variety of alternatives for retaining backups for longer than 35 days. This blog post includes details for implementing one of these methods.
In this scenario, we’ll use a container-based solution to create an on demand/scheduled backup of Azure Database for MySQL to an Azure blob container. To accomplish this, we’re providing a deployment template (in the GitHub folder here). This template deploys the following objects in your Azure subscription:
- A resource group to hold the related resources for the solution.
- A storage account, which will create a file share to store the backups.
- A runbook, which is a PowerShell script that creates a container with the mysql client installed and schedules the backup of the selected PAAS database to run at the specified time.
- A managed identity to run the runbook
- An automation account to authenticate the runbook by managing resources in either Azure Resource Manager or the classic deployment model.
To enable the overall functionality, you need to manually perform the following steps:
- Provide the managed identify with Contributor access to the resource group containing the automation account and storage account.
- Specify the parameters (Managed Identity client id, RGname, hostname, username, password, storagename, and backupfileshare) in the runbook, and then run it.
After you run the deployment template, your resource group will contain the resources shown in the following image:
When you navigate to your runbook, you can either have it start on demand or link it to a schedule, as shown in the following image:
If your subscription is not registered to containerinstance namespace, you might get an error. To register your subscription to containerinstance namespace, run the following commands:
az account set --subscription "My Demos"
az provider register --namespace Microsoft.ContainerInstance
To avoid running the container continuously, schedule your runbook to run at a specific time. At the scheduled time, the container will spin up, take a backup of the specified Azure Database for MySQL databases, and then the container will stop.
Note that you’ll need to delete the backup files manually based on your retention policy or schedule a script to delete the files for you automatically.
With this information, you can configure LTR for your database backups for periods longer than the 35 days provided for in Azure Database for MySQL.
If you have any questions about the detail I’ve provided, please leave a comment below or email our team at MySQLMariaDBNinjas@service.microsoft.com.
Thank you!
