How to automate Azure MySQL database backups

Case #

Azure database for MySQL is a fully managed cloud service which includes some basic backup operations. These include taking full backup of all databases and the MySQL instance itself so that it can be restored to any available point in time within a maximum 35-day retention window. When restoring the MySQL backups taken by the managed service, the full MySQL instance is restored into a new Azure database for MySQL resource, along with all its databases. Azure offers an additional MySQL storage (twice as much as the original storage utilized by the databases) for backup purposes. So if you pay for 100 GB of MySQL database data, you are entitled to another 100 GB for MySQL backups at no extra cost. Also Azure offer an automatic storage auto-increment feature, which increases the MySQL data quota if the initial quote is reached. Storage quota can only be increased and cannot be decreased by design. Details on the Azure database for MySQL restoration process can be found at: https://docs.microsoft.com/en-us/azure/mysql/howto-restore-server-portal.

So the Azure database for MySQL service comes with two limitations by design, which can be important in certain usage scenarios:

  1. No backup retention longer than 35 days can be provided.
  2. No granular backup plan of the individual databases can be configured.

In order to overcome the above limitations (by design) there are a handful of options which can be used to automate Azure MySQL database backups. The following sections provide an overview of these options.

Solution #

How to connect to Azure database for MySQL (single server) #

You should first review the following article on the available ways of connecting to the Azure database for MySQL resource (single server): https://docs.microsoft.com/en-us/azure/mysql/how-to-connect-overview-single-server.

How to automate Azure MySQL database backups #

Option 1: Via SQL script and corresponding Azure CLI/Powershell script #

In this option, you will be using a combination of a MySQL script with an Azure CLI/Powershell script.

Sample MySQL commands to generate and compress the database(s) #

Backup Azure Database for MySQL

--- backup single database:
mysqldump -Fc -v -h XXXXXXX.mysql.database.azure.com -u XXXXXXX -p -d databaseName1 > databaseName1_backup.sql

--- backup multiple databases:

mysqldump -Fc -v -h XXXXXXX.mysql.database.azure.com -u XXXXXXX -p  --databases db1 db2 db3  > databases-backup.sql

--- backup all databases

mysqldump -Fc -v -h XXXXXXX.mysql.database.azure.com -u XXXXXXX -p --all-databases > all_databases_backup.sql

If you see issue "mysqldump: 1044 Access denied when using LOCK TABLES" Or
"Access denied for use '@'%' to database 'mysql' when using LOCK TABLES", aA quick workaround is to pass the –-single-transaction option to mysqldump:

mysqldump -Fc -v -h XXXXXXX.mysql.database.azure.com -u XXXXXXX -p --single-transaction --all-databases > all_databases_backup.sql

Sample az storage blob commands for Azure CLI #

You can use Azure CLI commands to automate upload of your backups to an Azure blob storage account. Refer to the links below for basic upload/download commands, either per individual file or for bulk operations.

az storage blob command reference:

https://docs.microsoft.com/en-us/cli/azure/storage/blob?view=azure-cli-latest

blob upload

https://docs.microsoft.com/en-us/cli/azure/storage/blob?view=azure-cli-latest#az_storage_blob_upload

blob upload batch

https://docs.microsoft.com/en-us/cli/azure/storage/blob?view=azure-cli-latest#az_storage_blob_upload

blob download

https://docs.microsoft.com/en-us/cli/azure/storage/blob?view=azure-cli-latest#az_storage_blob_download

blob download batch

https://docs.microsoft.com/en-us/cli/azure/storage/blob?view=azure-cli-latest#az_storage_blob_download_batch

Option 2: Use rclone #

RClone is an open source and free application which can minimize the time needed for syncing MySQL backup files to an Azure storage account.

Follow the procedure below:

  1. Create a MySQL script in MySQL Workbench to periodically take mysqldump exports of all your Azure MySQL databases into a local folder. More details on this can be found in the previous Option 1 described above. Use the following commands for reference when taking mysqldump exports of your MySQL databases:

    mysqldump -Fc -v -h XXXXXXX.mysql.database.azure.com -u XXXXXXX -p -d databaseName1 > databaseName1_backup.sql
  2. Alternatively, use a MySQL Workbench backup schedule, as per https://dev.mysql.com/doc/workbench/en/wb-mysql-enterprise-backup-online-backup.html.
  3. Download and extract the rclone binaries. Then run following command as per https://rclone.org/azureblob/:
rclone config

Then run command

rclone sync [your local folder path here] [rclone remote name here]:[azure blob storage container name here]

Example

rclone sync C:\Data\MySQLBackups rcloneconfig1:MyAzureStorageContainerName

You can find the detailed rclone documentation at https://github.com/rclone/rclone/blob/master/docs/content/azureblob.md

You can further automate this by making use of cron in Linux or scheduled tasks in Windows for creating a scheduled task for MySQL backups and Azure blob storage uploads, while using Rclone for the syncing process.

The following articles provide step-by-step instructions for proper configuration of a Windows Scheduled task for rclone:

Option 3: Use a third-party backup tool #

You can always make use of a third party tool for automating everything you at the cost of purchasing the software license and installing it on-premises. There are a lot of cost effective software options in the market which include Azure Storage containers as a target when syncing MySQL backups. The following are only a few recommendations of software i have used. I do not endorse the purchase of any of these apps, nor do i have any affiliation with the respective software companies.

Option 4: Use a VM inside Azure or a VM on-premises as backup console #

You can make use of a VM on-premises but in this case you will have to enable private endpoints and an Azure VPN gateway for establishing secure connection to the MySQL database server and the Azure storage account directly from your VM. This requires additional configuration and will also increase the cost of your Azure implementation.

Instead of using an on-premise VM you could also create an Azure VM to be used for management and backup purposes, i.e. utilize the Azure management VM as your custom backup console among other things. A step-by-step guide on how to accomplish this is available at: https://techcommunity.microsoft.com/t5/azure-database-for-mysql/automate-backups-of-your-azure-database-for-mysql-server-to/ba-p/1791157

Option 5: Use Azure Data Factory #

To export data from the Azure MySQL database or an on-premises MySQL database to Azure Storage you can also make use of Azure Data Factory. This can be an ideal solution for large-scale implementations with high complexity as well as in scenarios of data analytics (see Azure Synapse Analytics). Bear in mind that Azure Data Factory can incur significant costs in your Azure bill. Here is some documentation on how to connect Data factory with Azure MySQL. https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-database-for-MySQL. You can run this on schedule or trigger the data migration by using a supported software development stack (.Net, Python, Azure REST API or Powershell, just to name a few).

Option 6: Use Azure Automation or Azure Functions #

This article provides step by step instructions on how to utilize Azure Automation or Azure functions to automate Azure SQL backups (SQL Server PaaS on Azure). You can adjust the procedure to fit the Azure database for MySQL scenario. Again should should expect additional costs from the use of Azure Automation or Azure Function resources alongside the software development implementation effort.

You can always make use of Powershell Gallery, Powershell module browser and Azure Code samples to discover any existing code/script which can be used as blueprint to carry this out, then finalize your backup script accordingly to run inside Azure Automation (Powershell) or as an Azure function.

Option 7: Create custom app and utilize Azure Storage REST API #

If you need to build something custom using the Azure REST API and the Azure SDK or integrate automated Azure MySQL backups in your existing software solution, you should utilize the following tools:

How to restore backups to Azure MySQL database  #

Follow the process below in Azure Cloud Shell:

  1. Navigate to your Azure Database for MySQL server on the portal and Run Azure Cloud Shell (Bash).  If you run this for the first time it will ask you to create a Storage container and and this will mount an Azure File in it.
  2. Type df in the cloud shell and collect the Azure File path.
  3. Change directory to the cloud drive using the cd command (cd /usr/[username]/clouddrive)
  4. Now that you are in that directory run mysql command, example: mysql -h mydemoserver.mysql.database.azure.com -u myadmin@mydemoserver -p testdb < testdb_backup.sql

Use the following article for reference on how to connect to Azure MySQL instance using the Azure Cloud Shell: https://docs.microsoft.com/en-us/azure/mysql/quickstart-create-mysql-server-database-using-azure-cli#connect-to-azure-database-for-mysql-server-using-mysql-command-line-client.

Alternatively, make use of MySQL Workbench and run commands to drop/create/import databases and individual tables.

Backup compression and encryption #

Last but not least, bear in mind that backups should be compressed and ideally encrypted as well. You should determine the criticality of your data and apply encryption accordingly. Security and encryption considerations for MySQL data at rest and in-transit are available in the below links:

Closing words #

In all above implementation options, you can either use Azure block blob storage or Azure Files. A comparison between Azure block/page blob storage Azure File storage can be found at: https://docs.microsoft.com/en-us/azure/storage/files/storage-files-faq. Also always remember to minimize Azure blob storage costs by following recommendations of Microsoft Azure storage lifecycle management.

Sources #

https://docs.microsoft.com/en-us/azure/mysql/

https://docs.microsoft.com/en-us/rest/api/storageservices/

https://docs.microsoft.com/en-us/rest/api/storageservices/blob-service-rest-api

https://severalnines.com/database-blog/mysql-cloud-backup-and-restore-scenarios-using-microsoft-azure

Powered by BetterDocs