Backup and Restore Azure Database for MySQL

This document outlines the backup and restore capabilities for Azure Database for MySQL, helping you protect your data and recover from accidental data loss or corruption.

Introduction

Azure Database for MySQL provides built-in automated backup and restore functionalities to ensure your data is safe and highly available. You can also perform manual backups for more granular control.

Automated Backups

Azure Database for MySQL automatically backs up your server on a regular basis. These backups are stored in geo-redundant storage to protect against regional outages.

Backup Retention

You can configure the backup retention period for your Azure Database for MySQL server. The retention period can range from 7 to 35 days. During this period, backups are retained and available for restore operations.

The default retention period is 7 days.

Backup Storage

Backup storage is provided free of charge up to 100% of your provisioned server storage. Any additional backup storage consumed beyond this will incur an additional charge.

For example, if you provisioned a server with 100 GB of storage, you get 100 GB of free backup storage. If your backups consume more than 100 GB, you will be charged for the excess.

Backup Frequency

Full backups are taken on a weekly basis. Differential backups are taken daily, and transaction logs are backed up every 5 to 10 minutes.

This comprehensive backup strategy allows for quick recovery to a specific point in time within your configured retention period.

Manual Backups

While automated backups are robust, you may require more control over your backups, such as for archiving or specific migration scenarios. Azure Database for MySQL allows you to create manual backups.

Creating Manual Backups

You can create a manual backup of your Azure Database for MySQL server using the Azure portal or Azure CLI.

Azure Portal: Navigate to your Azure Database for MySQL server, go to the 'Backup/Restore' section, and click 'Create backup'.

Azure CLI: Use the following command:

az mysql server backup create --resource-group <your-resource-group> --server-name <your-server-name> --name <backup-name>

Managing Manual Backups

Manual backups are stored in Azure Blob Storage. You can access and manage these backups through your Azure Storage account. They persist independently of the server and are not subject to the automated backup retention policies.

It is crucial to manage the lifecycle of your manual backups to avoid unnecessary storage costs.

Restore Operations

Azure Database for MySQL offers flexible restore options to recover your server to a previous state.

Point-in-Time Restore

This is the most common restore operation. You can restore your server to any specific point in time within your defined backup retention period. A new server is created with the data from the chosen point in time.

Steps:

1. Navigate to your Azure Database for MySQL server in the Azure portal.
2. Go to 'Backup/Restore'.
3. Select 'Restore'.
4. Choose the desired 'Restore point' (date and time).
5. Specify the new server name, region, and other configurations for the restored server.
6. Click 'Review + create' and then 'Create'.

Geo-Restore

If your primary region experiences a disaster, you can perform a geo-restore. This operation restores your server from a geo-redundant backup to a server in a different Azure region. This ensures business continuity even in the event of a regional outage.

Geo-restore is available if you have configured geo-redundant backups for your server.

Restoring from Manual Backup

You can restore your server from a previously created manual backup. This involves downloading the backup file from Azure Blob Storage and then importing it into a new or existing MySQL server, typically using tools like mysqlpump or mysqldump.

# Example using mysqlpump to restore a manual backup
mysqlpump --user=<user> --password=<password> --host=<host> --port=<port> <database_name> < <backup_file.sql>

Considerations

• Restore Time: The time taken for a restore operation depends on the size of the database and the chosen restore point.
• New Server Creation: Point-in-time and geo-restores always create a new server. You will need to reconfigure applications to point to the new server's connection string.
• Backup Storage Costs: Monitor your backup storage usage to stay within the free tier or to manage costs effectively.
• Security: Ensure appropriate access controls are in place for managing backups and restore operations.
• Testing: Regularly test your restore procedures to ensure they work as expected and meet your Recovery Time Objectives (RTO) and Recovery Point Objectives (RPO).