Azure Database for MySQL Flexible Server Administrative Tasks

By: Rajendra Gupta | Updated: 2024-11-04 | Comments | Related: > Azure

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.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-11-04