Problem
The tip, Azure Database for MySQL, explored various deployment models for Azure MySQL and their features. Further, we deployed an Azure MySQL flexible server using the Azure portal. This tip will explore the tasks and operation items required for a MySQL flexible server. Let’s check it out.
Solution
Let’s explore the various operations in Azure Database for MySQL flexible server.
Backups
Backups are crucial components for any organization’s business continuity planning. You can handle backups in Azure Database for MySQL flexible server using automated and on-demand processes.
Automated Backups
Azure automatically creates backups to recover at a point in time. These backups are stored securely in the local redundant storage of the same region where your Azure MySQL server exists. Specifics of automated backups include:
- Default retention: 7 days
- Configurable retention: 1 to 35 days
- Encryption: AES 256-bit
- How it works: Snapshots of the database file plus transaction logs.
- Frequency: Once a day with transaction log backups every 5 minutes.
The portal shows the backup type, retention period, and earliest restore point for my demo Azure MySQL instance.
You can change the default backup retention period using the Change button near the Backup section. The image below shows how to specify the value between 1 and 35 (short-term backup retention).
Click the Backup and restore tab to view the automated backup history and retention period. Click on the Fast restore button to restore it directly on Azure MySQL flexible server with few configurations.
On-Demand Backup
Automated backup runs on schedule time and frequency. However, if required, you can take a manual backup at any time with a backup retention period of up to 35 days.
Click on Backup Now, specify a backup set name, and click Trigger.
Once the on-demand backup is complete, you can view it along with the automated backup, as shown below.
Note: You can configure long-term backup retention up to 10 years (in preview).
Scheduled Maintenance
The Azure database uses the planned maintenance window for regular maintenance, such as patching and applying updates. By default, a maintenance schedule is system-managed and varies by region.
The system-managed maintenance window might not be suitable for your application requirements. Therefore, you can configure the custom schedule by choosing the day of the week and maintenance window.
Security
The Azure Database for MySQL flexible server provides various security mechanisms that can be implemented to safeguard against unauthorized access:
- Encrypt Data at Rest: To protect data, you can configure data encryption using the customer-managed key. Follow the link for configuration.
- Microsoft Defender for Cloud: The Microsoft Defender is a tool to detect anomalous\ suspicious database activity. It is an open-source platform that comes with a 30-day free trial.
- Network Connectivity Method: Azure provides two network connectivity methods for connecting to Azure MySQL flexible server.
- Public access (allowed IP addresses) and private endpoint.
- Private access (Vnet integration).
If you allow public access, the database is exposed to the Internet, and you can restrict public access according to the start and end IP address range. Without public access, the database is accessible using private endpoints. Additionally, you can configure a virtual network and subnet to allow connections from the virtual network. It restricts the database access from the Internet and safeguards the database within the virtual network boundary.
It is always recommended to allow connections in this manner:
- Microsoft Entra ID Authentications: Microsoft Entra authentication is recommended for centralized identity management, token-based authentication, multi-factor authentication, and conditional access policies. For more information, refer to the article on Microsoft Entra authentication in Azure MySQL flexible server.
Day-to-Day Management for Azure MySQL Flexible Server
Azure’s flexible server for MySQL manages the infrastructure and critical tasks, such as backup, patching, and upgrading. However, you can perform the several day-to-day management tasks.
Monitoring and Alerts
Azure MySQL’s flexible server provides various metrics for monitoring performance and utilization. A few useful metrics include:
- Host CPU percent
- CPU credit consumed
- Active connections
- Storage IO percent
- Memory percent
- Aborted connection
- Slow_queries
- Active transactions
- Data storage used
- Backup storage used
- Replication lag
- Replication IO status
- Replication SQL status
- MySQL lock timeouts
- MySQL lock deadlocks
Click the Metrics tab, choose the required metrics, and configure the chart as shown below.
Similarly, you can view the performance metrics using MySQL flexible server workbooks. Click on the Workbooks menu to get the following workbooks:
Click the Overview MySQL Flexible Server Onboard workbook to get various graphs for CPU, memory usage, storage used, storage limit, and backup storage.
Similarly, the Query Performance Insight workbook gives charts for query load, active connections, slow query trend, and details.
In the Enhanced Metrics workbook, you can view the Innodb buffer pool, DML & DDL, and replication-related metrics.
By default, it displays data for the last 30 minutes. You can change the time range to get data based on your time range.
Configure Alerts. Alerts help notify you if any monitoring event happens on the Azure MySQL flexible server. An example would be if CPU or memory utilization goes beyond a specified threshold. You can configure the email notifications to take the required actions. Refer to the article Set up alerts on metrics for Azure Database for MySQL to set up the alerts.
Scaling and Configuration
We can choose from Auto scale IOPS or Pre-provisioned IOPS during the MySQL flexible server deployment. You can monitor the maximum IOPS requirement in the metrics and provisioned IOPS or switch to Auto scale IOPS to configure MySQL for unpredictable spikes in the database traffic.
High Availability
Azure MySQL flexible server enables users to configure high availability and automatic failover to safeguard their database against unexpected failures. It gives two options:
- Zone Redundant High Availability: This solution protects flexible servers from availability zone failures. It provides complete isolation and infrastructure isolation. Therefore, you can use it for mission-critical applications that cannot afford the downtime.
- Same Zone High Availability: The primary and standby server exists in the same availability zone. You can get the highest level of availability within the single availability zone in Azure.
Monitor and Choose the Required Compute Tier
You must monitor the vCores requirement using the performance parameters and upgrade and modify the number of vCores to have a balanced configuration meeting application performance.
Note: You cannot use the high availability for the burstable compute tier.
You can click the High Availability tab and enable the zone to be redundant or the same zone to have high availability per your application requirements.
Next Steps
- Explore Microsoft docs on Azure Database for MySQL.
- Use the appropriate tool, such as MySQL workbench or Azure Database Studio, to connect and query the MySQL database in Azure.
- Always stop and start the instance appropriately to save the compute tier.
- You can review existing tips for Azure.
