Table of contents

Introduction

MySQL^™ is one of the most used open-source relational databases in the market, and organizations can easily pair it with Microsoft Azure technologies to gain the flexibility and reliability benefits of the cloud. With its beginnings in the 1990s,¹ MySQL has many features for handling structured and unstructured data, complex queries, and transactional or reporting applications. By choosing to run MySQL on Azure, customers can enjoy a myriad of VM choices, storage configurations, monitoring tools, security, and high availability (HA) options.

This document addresses best practices for deploying Azure Database for MySQL – Flexible Server and MySQL on Azure infrastructure as a service (IaaS). It covers VM selection, deployment procedures, monitoring and maintenance, storage considerations, database configuration considerations, and backup and restore.

PaaS and IaaS: What to know for MySQL deployments

As organizations continue to shift their MySQL databases to the cloud, selecting the right deployment model becomes a critical decision that can impact flexibility, scalability, and operational efficiency. Two predominant cloud service models—Platform as a Service (PaaS) and Infrastructure as a Service (IaaS)—offer distinct advantages depending on the needs of an organization. PaaS streamlines application deployment by providing a ready-to-use platform that abstracts much of the underlying infrastructure complexity. IaaS provides a highly customizable cloud environment where businesses can have complete control over the operating system, storage, and networking.

By offering PaaS and IaaS solutions for MySQL, Azure allows organizations to select the model that best suits their needs while continuing to use familiar Microsoft and Azure tools. Choosing between the Azure PaaS and IaaS offerings comes down to the balance among organizations' needs for control, convenience, and available resources.

PaaS refers to cloud computing services that provide a managed platform for customers to develop, host, and manage applications without manually building, maintaining, and securing the underlying infrastructure. PaaS platforms deploy and configure infrastructure and automatically handle all maintenance, patching, and updates. With Azure Database for MySQL – Flexible Server, this means that customers can focus on business-critical activities, including application development, instead of devoting resources to database management tasks, such as HA, disaster recovery, backups, and geographic data replication.

PaaS offers these specific advantages:

• Less coding time: Pre-coded application components, such as workflows, directory services, security features, and search, can boost app creation.
• Faster time to market: Access to cloud capabilities, including development and devops tools, can help streamline the app dev process.
• Easy scaling: Scale up compute and storage to meet workload demands with the click of a button and near zero downtime while only paying for the resources you use.
• Automated management and maintenance: Azure handles HA, backups, patching, and provides intelligent tuning recommendations enabling organizations to reduce costs associated with administrative tasks while ensuring peak performance.

PaaS provides features that could bring additional advantages for staff and operations, including pre-coded application components, such as workflows, directory services, security features, and search.

In contrast to the benefits of PaaS, IaaS demands more technical resources and effort for deployment and configuration but provides complete control over the OS and database. This level of control is crucial in scenarios such as hosting legacy applications, meeting stringent regulatory and compliance requirements, or scenarios where organizations need higher levels of customization.

IaaS offers the following specific benefits:

• Full control over the OS and database configuration: For compliance and regulatory contexts (e.g., healthcare and finance), IaaS solutions might be valuable. In these use cases and others that require higher customization, admins will likely need more granular control over the database or data and security.
• Flexibility to customize your configuration to meet your specifications: Running MySQL on Azure IaaS allows customers to use whatever versions of MySQL they need while being able to configure all MySQL parameters. In contrast, PaaS allow customers to edit many, but not all, MySQL parameters. Additionally, legacy applications might depend on configurations or software versions that modern PaaS solutions do not support.

IaaS provides flexibility and a greater degree of control over your infrastructure. Manual deployment, management, and maintenance could require higher operational overhead and technical knowledge. For organizations that require this level of control and customization or that have in-house technical expertise, IaaS could be a good choice.

To make the right choice between PaaS and IaaS for your organization, you need to consider your specific needs and workloads. In this report, we explain how to plan and deploy either an Azure Database for MySQL – Flexible Server solution or MySQL on Azure IaaS.

PaaS deployments: Azure Database for MySQL – Flexible Server

Best practices for deploying Azure Database for MySQL – Flexible Server

The section covers deployment procedures and considerations for Azure Database for MySQL – Flexible Server, including compute selection, storage options, deployment methods, monitoring and maintenance considerations, failover and HA configurations, and backup and restore options.

Understanding Azure Database for MySQL – Flexible Server

Azure Database for MySQL – Flexible Server is a fully managed, fully functional MySQL database PaaS platform. Azure automates database deployment, management, maintenance, hosting, and HA tasks for the virtual infrastructure hosting the database. After deployment, users and applications can access the database service via a client of their choosing. To manage shifting needs, admins can adjust database parameters to match workload requirements. They can also use the auto scale IOPS capability to cater to specific workload requirements and scale compute and storage resources dynamically in times of unexpectedly high demand.

Azure also offers robust networking, security, provisioning, and redundancy capabilities that can be costly and complex to maintain in a self-hosted environment.

Azure Database for MySQL – Flexible Server supports MySQL versions 5.7 and 8.0.² Azure automates regular patches and updates within the supported MySQL versions on either a customer-defined (where customers choose the day and time for Azure to perform maintenance) or system-managed maintenance schedule.³ Azure Database for MySQL – Flexible Server is available in 55 Azure regions.⁴

Benefits of deploying Azure Database for MySQL – Flexible Server

Deploying Azure Database for MySQL – Flexible Server gives you all the benefits that PaaS solutions in the Azure ecosystem offer—ease of deployment and configuration, on-demand scalability, simplified management and automated maintenance, resiliency and availability, and security. Azure Database for MySQL – Flexible Server also integrates with Azure App Service, Azure Kubernetes Service, and Azure Cognitive Services, which makes deploying and managing databases for cloud-native applications and workloads simple and convenient for developers working in the Azure ecosystem. Using a managed database service allows organizations to focus on development without devoting time to database management and maintenance, backups, manually scaling, or database security. To gain better scalability and performance, GeekWire decided to migrate its WordPress site to the Microsoft Azure platform. By taking advantage of fully managed services like Azure Database for MySQL, the company can scale on-demand while cutting costs 45 percent.⁵

Similarly, we previously conducted hands-on testing to demonstrate the ease and benefits of migrating an existing MySQL database to Azure Database for MySQL – Flexible Server on an AMD EPYC^™ processor-based VM. Read the report at https://facts.pt/WA4Av1Z.

(Note: Azure Database for MySQL – Flexible Server is “a fully managed production-ready database service designed for more granular control and flexibility over database management functions and configuration settings.”⁶ The offering has supplanted Azure Database for MySQL – Single Server, which Microsoft retired on September 16, 2024. Azure discontinued support for deploying Single Server instances from the Azure portal on January 16, 2023, and Azure CLI on March 19, 2024.⁷)

Planning for your deployment on Azure PaaS

Service tiers and Azure VM series overview

You can create an Azure Database for MySQL – Flexible Server instance in one of three service tiers, differentiated by the underlying Azure compute:

• B-series Burstable VMs
• D-series General Purpose VMs
• E-series Business Critical VMs

Within these service tiers, Azure offers multiple VM options.

Azure VMs come in predefined groupings, or series of configurations. Within each series type, there are generations (e.g., v5, v6), and each VM size within the series has a certain number of virtual CPUs (vCPUs) and amount of virtual RAM. Azure documentation recommends certain families of VMs for specific application use cases. For example, Microsoft recommends E-series and D-series VMs for relational database applications.⁸ To explore the different VM series types and their common use cases, see https://learn.microsoft.com/azure/virtual-machines/sizes.

When deploying MySQL on Azure, you will determine the appropriate VM series and type for your specific use case. For production workloads, you will most likely select VMs from either the general purpose or memory optimized categories because MySQL will heavily use CPU, RAM, or both. You are unlikely to need high performance computing or GPU instances in a typical MySQL use case.

Azure VM service tiers and workload considerations

Table 1 provides detailed information on the three service tiers.

B-series burstable VMs can be valuable for test or development workloads or use cases where baseline CPU utilization is low overall but may require periods of higher performance based on intermittent demand. In addition, burstable VMs do not offer HA.¹⁰ Customers might also use B-series VMs for low-traffic web servers or applications, microservices, or other less critical workloads.

D-series general purpose compute VMs are the standard tier of Azure VMs. Customers might choose them for general production use cases and workloads. Microsoft recommends these VMs for enterprise customers with general purpose workloads that do not require a compute-to-memory ratio higher than 1:4 (for each vCore there is 4GiB memory). Examples of these workloads include e-commerce systems, production websites, hosted services, virtual desktop infrastructure hosting, and small to medium-sized databases.¹¹

E-series business critical VMs “are optimized for in-memory applications”¹² and feature a higher than typical ratio of memory to compute power (up to 8GB of memory per vCore, compared to 4GB per vCore in the General Purpose VM service tier).¹³ The Business Critical tier also offers accelerated logs, where logs reside on higher IOPS disks, at no additional cost.¹⁴ Microsoft recommends these VMs for workloads that require in-memory processing such as analytics, high-performance transactional workloads, exceptionally large databases, or business intelligence applications.

AMD processor-based VM series to consider

The two AMD EPYC^™ processor-based VM series available for Azure Database for MySQL – Flexible Servers are the Dadsv5 General Purpose tier VMs and the Eadsv5 memory-optimized Business Critical tier VMs. The Dadsv5 series provides a balanced ratio of compute to memory resources with 4 GiB of memory per virtual core, while the Eadsv5 series VMs feature 8 GiB of memory per vCore in most configurations.¹⁵

The Dadsv5 series features scalability up to 96 vCPUs and 384 GiBs of RAM, and use AMD EPYC 7763v processors that "can achieve a boosted maximum frequency of 3.5GHz.”¹⁶ For more information on the Dadsv5 VMs, see https://learn.microsoft.com/azure/virtual-machines/dasv5-dadsv5-series. Dadsv6 VMs based on 4^th Gen AMD EPYC processors are currently in preview.¹⁷

Eadsv5-series VMs feature memory-heavy 8:1 RAM-to-vCPU ratios in most configurations,¹⁸ with the smallest Eadsv5 VM having 16 GiBs of RAM with two vCores, and the largest VM configuration having 672 GiBs of RAM with 112 vCores. For more information on the Eadsv5 series, see https://learn.microsoft.com/azure/virtual-machines/easv5-eadsv5-series.

Configurable options and limitations

Azure Database for MySQL – Flexible Server instances have several provisioning and configuration options to consider prior to deployment, including service region, availability zone, authentication method, access scope, and HA. In addition to choosing an Azure region for the server, administrators can specify a particular availability zone within that region to collocate the database service with other Azure services or hosted applications that will use it. Customers can provision HA in either zone-redundant or same-zone configurations during the initial server deployment or enabled after the fact in the Azure portal. See the section HA best practices for details.

Azure Database for MySQL – Flexible Server instances support both traditional MySQL authentication via a user-defined administrator username and password and Microsoft Entra authentication for accessing and administering the server. Customers can enable either or both depending on application and security requirements. Administrators can also control network access to the server in many granular ways. They can allow inbound connections from public IP addresses or whitelist specific IP addresses or ranges in the Azure firewall. They can also permit other Azure services to access the resource, or they can restrict access sources to specific private endpoints or Azure virtual networks. All Azure Database for MySQL – Flexible Server deployments support encrypted TLS/SSL connections.

Due to its nature as a PaaS offering, Azure Database for MySQL – Flexible Server does not offer the same level of operating system (OS) control as a self-managed IaaS deployment of MySQL. If the configurable options detailed in this deployment guide are not sufficient for your workload or use case infrastructure requirements, you might find that the higher level of OS control offered by an IaaS deployment works better for your needs, but at the cost of higher manual effort for backup, maintenance, security, scalability, and replication.

Storage considerations

Azure Database for MySQL – Flexible Server sets maximum IOPS for each instance type, and the maximums differ for each instance size (visit this page for detailed memory and sizing options):¹⁹

• Burstable: 5,000 IOPS
• General purpose: 20,000 IOPS
• Business critical: 80,000 IOPS

Administrators can set a pre-provisioned IOPS level during deployment or opt to let Azure auto-scale IOPS as needed. Administrators specify the initial storage size in GiB during deployment and can enable Azure to scale storage dynamically as needed to prevent interruption of service due to storage capacity. Administrators can also manually scale up the storage capacity at any time but cannot scale storage down after initially provisioning the server.

Backup considerations

Administrators choose backup retention periods of 1 to 35 days for daily automated backups and can specify whether backups are locally redundant, zone-redundant (for zone-redundant HA deployments), or geographically redundant in paired Azure regions. Administrators can enable geo-redundancy at any time for locally redundant backups. In addition to daily automated server backup snapshots, Azure backs up transaction logs every five minutes.

Azure Database for MySQL – Flexible Server also allows administrators to initiate on-demand backups manually through the Azure portal. Azure can store up to 50 on-demand backups.²⁰

Deploying Azure Database for MySQL – Flexible Server

This section details the process and configuration options for deploying Azure Database for MySQL – Flexible Server via the Azure portal or the Azure CLI and discusses basic methods to connect to the MySQL resource once deployed. Our examples detail a specific configuration we chose for our sample deployment, but we discuss all relevant configuration options in the step-by-step instructions below.

Creating an Azure Database for MySQL – Flexible Server via the Azure portal

1. Navigate to the Azure portal at https://portal.azure.com/ and log in using your Azure credentials.

2. Using the search bar, navigate to the Azure Database for MySQL – Flexible Servers service or select it from the Azure services menu (see Figure 1).

3. Click Create. Figure 2 shows the two locations of the Create button in the upper left and bottom center.

4. Click the Advanced Create button under the Flexible Server deployment option as shown in Figure 3.

5. Under Project Details, select the Azure subscription and resource group to which you want to deploy the resource. To create a resource group, click Create new under the dropdown menu or navigate to Resource groups via the search bar and click Create.

6. Under Server details:

a. Enter or select the following details for the server:

• Server name – a unique server name from 3 to 63 characters that may only contain lowercase letters, numbers, and hyphens; it cannot start or end with a hyphen
• Region – any of the Azure regions (for our sample deployment, we chose East US)
• MySQL version – either MySQL 5.7 (retired) or MySQL 8.0 (for our sample deployment, we chose MySQL 8.0)
• Workload type – This will determine which VM series and compute/storage resources the creation wizard recommends. Each of the three workload options corresponds to the three available Azure Database for MySQL – Flexible Server service tiers (General Purpose, Business Critical, and Burstable). For our sample deployment, we chose “For small or medium size databases.”
  • “For small or medium size databases” corresponds to the General Purpose compute tier and might be appropriate for most production and business workloads.
  • “Tier 1 Business Critical Workloads” corresponds to the Business Critical tier and might be appropriate for high-performance and real-time data workloads.
  • “For development or hobby projects” corresponds to the Burstable tier and might be appropriate for development or intermittent purposes where the server does not require full CPU usage all the time.

7. Specify the server compute and storage details:

a. Click Configure server to select compute and storage options. These options can give you more granular control over your VM series and compute tier. Figure 4 shows the Configure server page with our sample deployment values.

b. Specify the following options according to your workload needs:

• Compute tier – Choose among Burstable, General Purpose, or Business Critical.
  • For our sample deployment, we chose General Purpose.
• Compute processor – Choose the instance that is appropriate for your workload.
  • For our sample deployment, we chose an AMD EPYC^™ instance. Note that only the General Purpose and Business Critical compute tiers offer AMD processors.
• Compute size – Choose one of the available VM series based on vCore, memory, and IOPS requirements.
  • For our sample deployment, we chose the Standard_D16ads_v5 VM series with 16 vCores, 64 GiB memory, and 20,000 max IOPS.
• Storage size – Specify the size of available storage capacity to make available to the server in gibibytes (GiB). This value can be anywhere between 20 GiB and 16 tebibytes (TiB), and the solution uses the capacity for database files and logs. Customers can add storage after creating the server, but they cannot scale down this value creation.
  • For our sample deployment, we chose 64 GiB.
• IOPS – Select whether to auto-scale or pre-provision IOPS, which could generate more operating expenses.
  • For our sample deployment, we chose to auto-scale IOPS.
• Accelerated logs (Business Critical tier only) – Choose to enable Accelerated logs. Accelerated logs could enhance storage performance by optimizing log-related operations to improve throughput and cost efficiency. To learn more about accelerated logs, visit https://learn.microsoft.com/azure/mysql/flexible-server/concepts-accelerated-logs.
  • For our sample deployment, we enabled accelerated logs.
• Storage auto-growth – Choose to enable storage auto-growth, which could increase your billable storage capacity if the server runs out of storage capacity and prevents the server from becoming read-only and interrupting workloads.
  • For our sample deployment, we enabled storage auto-growth.
• High availability – Choose to enable HA. If customers enable HA, they can choose between same zone (zonal) HA or zone-redundant HA.
  • For our sample deployment, we enabled zone-redundant HA.
• Backup options – Select a backup retention period in days (1 to 35) and enable geo-redundancy. Note that users cannot enable geo-redundancy after server creation when using zone-redundant HA mode.
  • For our sample deployment, we specified a 14-day backup retention period and enabled geo-redundancy.

c. After configuring your compute and storage options, click Save.

d. If desired, select a specific Availability zone for deployment. This might be useful if you want your database to exist in a specific availability zone that is co-located with an application or workload that uses it.

i. For our sample deployment, we chose no preference.

e. Choose Authentication Method: MySQL authentication, Microsoft Entra authentication, or both.

i. For our sample deployment, we chose MySQL authentication.

f. Enter an Admin username and password for the server. If you are using Microsoft Entra authentication, select a user-assigned managed identity and a Microsoft Entra admin.

8. Click Next → Networking.

a. Select connectivity method: public or private access. Public access enables you to allow access to the server from all public IP addresses, to only other Azure services, to only a specific range of public IPs, through a private endpoint via an Azure virtual network, or any combination of the four. Private access limits access through using a private endpoint via Azure virtual network integration.

i. For our sample deployment, we chose public access.

b. If using public access:

i. Choose to allow public access to this resource through the internet from a public IP address. For our sample deployment, we enabled this option.

ii. Choose to allow public access from any Azure service within Azure to this server. For our sample deployment, we enabled this option.

iii. Enter a firewall rule name and start and end IP addresses to give a range of public IP addresses access to the server if applicable. For our sample deployment, we added a rule to allow access to only our office public IP address.

iv. To allow private access, create a private endpoint by clicking Add private endpoint and specifying a virtual network, subnet, and private DNS zone.

c. If using private access (VNet integration):

i. Select the Azure subscription, virtual network, and subnet to use for private access. Azure will create a virtual network and subnet in the same resource group by default if unspecified.

ii. Select a private DNS zone or leave blank to automatically create one.

9. Click Next → Security.

a. Azure encrypts the storage by default using service-managed keys. If you would like to use a customer-managed key instead, select the user-assigned managed identity and key here.

10. Click Next → Tags.

a. Enter any Azure tags based on organizational requirements that you wish to associate with the Azure Database for MySQL – Flexible Server.

11. Click Next → Review and create. This screen validates the server configuration and estimates the server creation time in minutes. Click Create to confirm and initiate server deployment. Figure 5 shows a successful Azure Database for MySQL - Flexible Server deployment using the Azure portal.

Creating an Azure Database for MySQL – Flexible Server via Azure CLI

Administrators can also deploy Azure Database for MySQL – Flexible Server directly from the command line using Azure Cloud Shell or Azure CLI. We used Azure Cloud Shell for our sample deployment, but the following steps should work with an authenticated user on Azure CLI version 2.0 or later as well.

1. Open Azure Cloud Shell by logging into the Azure portal and navigating to https://portal.azure.com/#cloudshell/. Select Bash when prompted.

2. Select your active Azure subscription if prompted, and click Apply.

3. If desired, create a new resource group for deployment:

az group create --name sampleresourcegroup --location eastus

4. Create a new Azure Database for MySQL server by running the following command and modifying the parameters and values as needed. You can find a list of all parameters for this command at https://learn.microsoft.com/cli/azure/mysql/flexible-server?view=azure-cli-latest#az-mysql-flexible-server-create. Any parameters without values will revert to Azure CLI defaults.

az mysql flexible-server create --name samplemysqlserver --resource-group sampleresourcegroup

5. Respond yes or no to any prompts that appear during the deployment process. Azure will restrict the server to private access by default, which will prompt you to allow your client IP address to connect. We enabled this in our sample deployment.

6. After the cloud shell console indicates that you have successfully deployed and configured the server, it will display a short JSON object output that shows the server connection string, associated identifiers and resources, the VM series SKU, and the generated admin username and password if you did not specify them in step 4. The server is now ready to use.

7. To output the server details and connection information in JSON format and save the output for reference, run the following command:

az mysql flexible-server show --resource-group sampleresourcegroup --name samplemysqlserver

8. Run the following command to test your connection to the database and confirm your client connects to the database server as expected, replacing <username> and <password> with the values from the JSON output in the previous step:

az mysql flexible-server connect -n samplemysqlserver -u <username> -p <password>

Overview of using Azure Resource Manager templates

Azure Database for MySQL – Flexible Server supports provisioning via Azure Resource Manager (ARM) templates. An ARM template is a JSON file that defines the infrastructure and configuration for a deployment, which could consist of multiple databases on a single server or across multiple servers. ARM templates enable customers to automate provisioning and deployment of Azure Database for MySQL – Flexible Server instances with the same configuration options that users can specify when manually deploying instances via the Azure portal.

See the appendix for an example ARM template that you can use to deploy an Azure Database for MySQL – Flexible Server instance based on our sample configuration.

Managing Azure Database for MySQL – Flexible Server

Scaling up an existing deployment

Users can scale up or down Azure Database for MySQL – Flexible Server compute resources by adjusting the VM series type in the Azure portal. Users can also scale up storage, but not scale it down. Changing the VM type will require a server restart. You can follow these steps to scale up an existing Azure Database for MySQL – Flexible Server in the Azure portal:

1. Log into the Azure portal and navigate to the Azure Database for MySQL service using the services list or the search bar.

2. Click the name of the Azure Database for MySQL – Flexible Server instance to view its Overview page.

3. Click Settings in the left navigation pane to expand the settings, and click Compute + storage.

4. Adjust the compute tier, compute size (VM series) and storage size, IOPS, and auto-growth setting as needed. You can also enable post-deployment HA here or modify the backup retention period and geo-redundancy. The estimated costs panel at the bottom of the page reflects the new estimated monthly server cost based on configuration changes.

5. Click Save, and then when prompted to restart the server, click Continue.

Managing Azure Database for MySQL – Flexible Server using the Azure portal

Users can manage Azure Database for MySQL – Flexible Server primarily using the Azure portal. To manage your server, select your Azure Database for MySQL – Flexible Server instance in the Azure portal and open the Overview panel by clicking the Overview menu. Then if you click Databases, you can click Add to create a new database on the server, which allows you to specify the database name, collation, and character set. You can also click Delete to remove a database from the server.

Administrators can also modify MySQL server parameters by clicking Server parameters in the left-side menu. There you can view all server parameters and modify them where possible. Modifying static parameters will prompt you to restart the server either immediately or later. This management option also allows you to reset all server parameters to default if they are misconfigured. For more detailed information about modifying MySQL server parameters, see https://learn.microsoft.com/azure/mysql/flexible-server/how-to-configure-server-parameters-portal.

The settings section of the menu also allows administrators to configure or enable replication, HA, backup settings, security settings, alerts and monitors, and other basic configuration options after server deployment.

Connecting to Azure Database for MySQL – Flexible Server with Azure CLI and creating a database

1. Open the Azure Cloud Shell or Azure CLI, and connect to the server:

az mysql flexible-server connect -n samplemysqlserver -u <username> -p <password>

2. Create a new database, and specify the database name, the server name, and the resource group. If successful, you will see confirmation output consisting of database details in JSON format.

az mysql flexible-server db create -d sampledbname --server-name samplemysqlserver --resource-group sampleresourcegroup

3. Confirm deployment by connecting to the newly created database:

az mysql flexible-server connect -n samplemysqlserver -u <username> -p <password> -d sampledbname

4. Run the following command to test with a database query if desired:

az mysql flexible-server connect -n <server-name> -u <username> -p "<password>" -d <database-name> --querytext "<query text>"

For more information about the connect command, see the Azure CLI documentation: https://learn.microsoft.com/azure/mysql/flexible-server/connect-azure-cli.

Connecting to Azure Database for MySQL – Flexible Server with MySQL Workbench

This section describes how to connect to a newly created Azure Database for MySQL – Flexible Server instance from the official MySQL workbench tool. This example assumes that you have allowed public access. If you have limited access to a private endpoint, then install MySQL workbench on another VM with access to the private virtual network instead of a client device.

1. Download and install the MySQL workbench application from the MySQL website https://dev.mysql.com/downloads/workbench/.

2. Log into the Azure portal and navigate to your Azure Database for MySQL resource. Click the resource to open the Overview panel.

3. Note the server hostname, admin username, and the password.

4. Launch MySQL Workbench.

5. Click the + to add a new database connection.

6. Enter a name for the connection, and input the server hostname, username, and password from step 3.

7. To verify the connection and credentials, click Test Connection, and click OK (see Figure 6).

8. Click the tile corresponding to the connection you just configured. After the client connects to the server, a new window will open to allow SQL query input.

For more information about using MySQL Workbench with Azure Database for MySQL – Flexible Server, visit https://learn.microsoft.com/azure/mysql/flexible-server/connect-workbench.

HA best practices

Azure Database for MySQL – Flexible Server provisions physically separated primary and standby replicas in the same availability zone (zonal) or across availability zones (zone-redundant). Both configurations allow automatic failover with zero data loss.

Availability zones are separate data centers within the same region. Each data center has independent power, cooling, and networking. Azure services move to the nearest working availability zone in the event of a failure.

Azure always deploys standby replicas in the same VM configuration, and customers can remove them by disabling HA. Azure performs stop, start, and restart operations on both primary and standby servers at the same time.

Azure applies maintenance activities, such as updates, to the standby server first, and then promotes the standby server to primary while Azure updates the original primary server. This provides continuous uptime.

Zonal (same zone) HA

Zonal redundancy (primary and standby replicas in the same availability zone) provides redundancy with lower latency than the zone-redundant configuration. Zonal redundancy could protect from node-level failures while reducing downtime during planned and unplanned events.

Azure backs up data and logs onto local redundant storage. Admins can choose the region and availability zone for deployment. Zonal redundancy offers an uptime SLA of 99.95 percent.²¹

Zone-redundant HA

Zone redundancy (primary and standby replicas in different zones) provides the highest level of availability at the cost of some latency. The latency will show at the application level but is necessary to keep replicas up to date.²² According to Azure, the latency will not affect read-only queries, but customers can expect a 5 to 10 percent impact for writes and commits.²³

Zone redundancy provides complete physical separation between the primary and standby stacks. Azure mirrors data and logs three times onto zone-redundant storage for additional data protection.

Admins can choose the region and availability zones for both primary and standby locations. Zone-redundancy offers an uptime SLA of 99.99 percent.²⁴

Note: The Burstable compute tier does not support HA.

Security best practices

Data encryption

Azure encrypts server, database, and backup data with a service-managed key by default, but customers can choose a user-managed key for encryption instead. Azure Database for MySQL – Flexible Server integrates with Microsoft Entra and Azure Key Vault to create an Azure-wide authentication method. Role-based access and user-managed identities give admins flexibility in managing data access. Customers can use customer-managed or service-managed keys to encrypt their Azure Database for MySQL – Flexible Server instances and backups. Azure stores the customer-managed key in an Azure Key Vault instance, which is highly available and scalable. Customers also have the option to have FIPS 140-validated hardware security modules back Azure Key Vaults. The Azure Key Vault and Azure Database for MySQL service must belong to the same Microsoft Entra tenant.²⁵

Regular updates/patching

Azure Database for MySQL – Flexible Server undergoes regular maintenance to ensure the managed database remains secure, stable, and current. During these maintenance periods, the server receives new features, updates, and patches. The standard maintenance cycle occurs at least every 30 days. Admins have the option to schedule maintenance on a specific day and time window of their choice, or they can allow the system to automatically select a day and time window for you. If you do not specify a preference, the system will choose a time between 11 PM and 7 AM in the server’s regional time zone.²⁶

User management and access controls

MySQL authentication is the default method of connecting to Azure Database for MySQL – Flexible Server. However, admins can select Microsoft Entra as an additional or the sole method of connection.²⁷

Admins can restrict network access via allow list (public IPs) or use Azure tools to create a private network for access control.²⁸

Threat detection, prevention, and mitigation

At an additional cost, admins can deploy Microsoft Defender for open-source relational databases to help monitor, investigate, and mitigate attacks. Microsoft Defender for open-source relational databases integrates with Microsoft Sentinel. After deploying Defender, the following activities will trigger alerts:

• Anomalous database access and query patterns
• Suspicious database activities
• Brute-force attacks

See the full list of alerts at https://learn.microsoft.com/azure/defender-for-cloud/alerts-open-source-relational-databases.

Backup and disaster recovery best practices

Automated and on-demand backups

Administrators choose backup retention periods of 1 to 35 days for daily automated backups and can specify whether backups are locally redundant, zone-redundant (for zone-redundant HA deployments), or geographically redundant in paired Azure regions. Users can enable geo-redundancy at any time for locally redundant backups. In addition to daily automated server backup snapshots, Azure takes transaction log backups every five minutes. If a daily backup fails for any reason, Azure retries the backup every 20 minutes until successful.

Azure Database for MySQL – Flexible Server also allows administrators to initiate on-demand backups manually through the Azure portal. Users can request and store up to 50 on-demand backups.²⁹

Monitoring performance and health

Azure Monitor and alerts

Azure Monitor monitors Azure Database for MySQL – Flexible Server. All Azure metrics have a one-minute frequency with 30 days of history. Monitor pulls metrics, such as CPU and storage performance data, from Azure and the database. Admins can place metrics on a dashboard with various charts and alerts.³⁰ See the full list of metrics at https://learn.microsoft.com/azure/mysql/flexible-server/concepts-monitoring.

Some Azure Database for MySQL – Flexible Server metrics:

• Performance data
  • CPU, memory, and storage utilization percentages
  • Uptime
  • Credit usage (for burstable instance types)
• Storage breakdown
  • Capacity and limits
• Replication
  • Replication lag and status
• DML statistics
  • Select, update, insert, and delete statement counts
• DDL statistics
  • Create, drop, and alter db/table statement counts
• Innodb metrics
  • Lock and wait times
  • Buffer statistics

Admins can pull server logs using the Azure portal and CLI. Azure disables this feature by default, but admins can easily enable it in the Azure portal. Monitor retains logs for up to seven days or up to a maximum size of 7 GiB. The 7 GiB storage is free and non-extendable. Azure hashes out personally identifying information from logs, such as hostnames, IP addresses, and usernames. Admins can see this information by enabling audit logs.³¹

Because Azure Database for MySQL – Flexible Server integrates with Azure Monitor, admins can use Azure workbooks to parse and display metrics. Azure Database for MySQL – Flexible Server comes with three workbook templates: overview, auditing, and query performance. Admins can edit and customize templates as needed.

Query performance insights

Admins can enable slow query logging (which Azure disables by default) to help troubleshoot slow queries. Admins can access the log via the Azure portal and CLI. Admins have access to several parameters to help filter the slow query logs:

• long_query_time
  • Determines the minimum length of logged slow queries
  • Default is 10 seconds
  • Does not affect active connections
  • Azure recommends restarting the database service after changing this parameter
• log_slow_admin_statements
  • Determines if administrative statements are logged
• log_queries_not_using_indexes
  • Determines if queries that do not use indexes are logged
• log_throttle_queries_not_using_indexes
  • Throttles the number of non-indexed queries that are logged

Troubleshooting and Support

Azure support plans

Azure offers four tiers of support plans. All customers have the basic Request Support tier, which includes access to Azure documentation and community resources, the ability to submit support tickets, access to the Azure Advisor service, and Azure health status notifications.

Azure offers the Developer support tier at $29 per month. Azure intends for the tier to support test and development environments. The tier includes all of the resources in the basic tier with the addition of “third-party software support with interoperability and configuration guidance and troubleshooting” and offers full access to Azure support by email during business hours after customers initiate a support request.³² Azure limits developer support tickets to severity level C (minimal business impact) and have a guaranteed response time of 8 business hours.

Next is the Standard support tier at $100 per month. Azure intends for the tier to support production workloads and applications. It includes all resources and features of the basic and developer tiers with the addition of 24/7 access to technical support by phone or email. The tier also includes ticket creation with severity level B (moderate business impact) and level A (critical business impact) with response times of within 4 hours and 1 hour, respectively.³³

Azure offers the Professional Direct support tier for $1,000 per month. Azure intends for the tier to support business-critical workloads and applications. This tier includes all the features of the other tiers but with shorter response times: 4 business hours for severity level C, 2 hours (regardless of business hours) for severity level B, and 1 hour for severity level A. The tier also provides access to a pool of ProDirect delivery managers for architectural and operational support and service review and advisory consultation and guidance.³⁴

For more detailed information about the Azure support plans available, visit https://azure.microsoft.com/support/plans/.

Community resources

In addition to Azure support, there are several community resources available to supplement the official documentation and support resources and connect users with other Azure administrators and Microsoft engineers:

• Azure community support – Customers can find the landing page for the following resources at https://azure.microsoft.com/support/community/.
• Azure on Q&A (Microsoft community support forum) – Users can create a thread to ask questions about specific Azure services and get feedback or support from other community members and Microsoft experts at https://learn.microsoft.com/answers/tags/133/azure.
• Azure on Stack Overflow – Stack Overflow, a well-known third-party community resource for technical support, has an officially supported community channel at https://stackoverflow.com/collectives/azure.
• Azure Support on Twitter – Users can also log onto the social media site X (formerly known as Twitter) to engage with Azure experts at https://x.com/AzureSupport.

Documentation and knowledge base

Azure Database for MySQL – Flexible Server has extensive official documentation, tutorials, and quick start guides for various common tasks and use cases:

• The official comprehensive documentation for Azure Database for MySQL – Flexible Server is available at https://learn.microsoft.com/azure/mysql/.
• Microsoft Learn includes a series of training tutorials for common use cases with Azure Database for MySQL – Flexible Server that users can access at https://learn.microsoft.com/training/paths/introduction-to-azure-database-for-mysql/.
• Azure offers a library of how-to technical training videos produced by Microsoft experts that cover various aspects of using Azure services and the Azure portal at https://azure.microsoft.com/resources/videos.

IaaS deployments: MySQL on Azure IaaS

Planning for your MySQL deployment on Azure IaaS

Whether on site or in the cloud, a MySQL deployment requires careful consideration of several factors, which we list below. Each environment and application is different, so this list is not exhaustive; you should consider your environment’s specifics in your planning.

Storage: Azure offers several varieties of storage, each with a range of capabilities, that administrators can attach to virtual machines. Workloads that create a small-block random input/output (IO) pattern, such as most traditional online transaction processing (OLTP) workloads, may require virtual disks backed by high-speed SSDs that have higher input/output operations per second (IOPS) ratings. On the other hand, if your MySQL data is mostly at rest or does not need significant bursts of random IO, it may be best to consider more cost-effective, lower-IOPS options.

VM selection and configuration: Azure also provides many VM series with a range of specifications and costs. Azure has optimized each series for different use cases and workloads, so you can balance cost and performance according to your needs. You should carefully configure the VM you choose from the perspective of virtual storage and operating system software. Your choice of VM type will dictate most options, such as core count and virtual RAM, but you might consider configuring multiple volumes if you require a high degree of customization. For operating system choices, nearly any Linux distribution will work with MySQL. However, you’ll likely want to use your organization’s standard OS for compliance reasons, and you should check the MySQL release version’s compatibility with the Linux^® distribution version you choose at https://www.mysql.com/support/supportedplatforms/database.html.

Database and VM sizing: To select the appropriate Azure VM and disks, you will need some knowledge of your databases’ sizes. You should consider the current sizing of all elements of the MySQL database application, such as tables, indexes, and log files. You should also consider their projected growth over time. We discuss this more in depth later in this document.

MySQL configuration: MySQL is an advanced database management system (DBMS), with many configuration options available that you can modify to affect your application performance, such as cache settings, IO concurrency, logging settings, checkpoint behaviors, and more.

Networking: Azure offers robust networking options. You’ll want to be sure to explore the necessary networking options to set up the virtual networks you need, while controlling access to your MySQL database.

Monitoring and maintenance: While the PaaS offering from Microsoft includes patching and updates, the IaaS MySQL implementations are customer-supported and customer-patched. You should consider the maintenance needs of your MySQL implementation up front. In addition, you must monitor your database application, and Azure offers myriad options to do so. Combining these Azure Monitor tools with traditional MySQL monitoring approaches yields many options for a stronger implementation.

Security: Security is critical in any implementation, but especially in the cloud. In this section, we will review various security best practices for both Azure and MySQL.

Backup and restore: Any database implementation needs a carefully planned backup and restore framework. Later in this document, we discuss how to approach this critical piece of the puzzle in the context of running MySQL on Azure in an IaaS implementation.

Azure VM series operating system choices

AMD processor-based VM series to consider

There are three recommended AMD processor-based VM types: the Easv6 and Eadsv6 memory-optimized VMs and the Famsv6 compute-optimized VMs. All feature sizeable RAM-to-vCPU ratios, making them excellent candidates for database workloads where you need much of your working dataset cached in database buffers.

The Easv6 series and Eadsv6 series both feature scalability up to 96 vCPUs and 672 GiBs of RAM, and use AMD EPYC^™ 9004 processors that "can achieve a boosted maximum frequency of 3.7GHz.”³⁵ The Eadsv6 series VMs also include a local NVMe temp disk for application and system short-term storage, which is helpful for page files, swap files, and SQL tempdb files, among other things. For more information on the Easv6 VMs, see https://learn.microsoft.com/azure/virtual-machines/easv6-eadsv6-series. For more information on the Easdv6 VMs, see https://learn.microsoft.com/en-us/azure/virtual-machines/sizes/memory-optimized/eadsv6-series.

Famsv6 VMs feature full cores (no simultaneous multithreading) and memory-heavy 8:1 RAM-to-vCPU ratios,³⁶ with the smallest Famsv6 VM having 16 GiBs of RAM, and the largest VM having 512 GiBs of RAM. For more information on the Famsv6 series, see https://learn.microsoft.com/azure/virtual-machines/fasv6-falsv6-series.

In addition to Famsv6 VMs, we also recommend Basv2, Falsv6 VMs, Fasv6, and Da-series v6 VMs for very small or non-production databases.

OS compatibility

MySQL is compatible with many commonly available operating systems and is likely to run on all the operating systems the Azure catalog offers. MySQL documentation states that MySQL supports operating systems including Oracle Linux, Red Hat^® Enterprise Linux^®, CentOS, Ubuntu^®, SUSE Enterprise Linux, Debian, Microsoft Windows and MacOS^®.³⁷ For this guide, we chose Ubuntu as the underlying operating system.

Azure Disk Storage overview

Because persistent and consistent data handling is a cornerstone of a reliable database application, storage design plays a key role in any database deployment. When designing in the context of Azure, you must consider Azure Disk Storage offerings and how to present those to the guest operating system, and thus, the database. In this section, we explore the Azure Disk Storage types and their features.

Azure Disk Storage types

Azure Disk Storage is a service for managing Azure VMs’ virtualized disk storage. Azure manages the underlying storage hardware, eliminating the need for users to maintain infrastructure. Users can simply choose a size and disk type, provision the disk, and then attach the disk to a VM.

Azure offers five disk storage types: Ultra Disk, Premium SSD v2, Premium SSD, Standard SSD, and Standard HDD. Table 1 shows the specifications for each as well as examples of workloads for which each disk type is suited. Note that while these limits apply specifically to Azure Disk Storage, Azure VMs also have their own unique storage performance limits depending on the VM series, as we discuss below.

Azure Disk Storage features

These disks come equipped with several benefits and features.

Storage availability and durability: Azure has designed managed disks for at least 5 nines (99.99999 percent) of availability via high levels of durability and redundancy.³⁸ Azure achieves this redundancy by creating three replicas of your data, meaning that even in the event of one or two replicas failing, the remaining replicas can protect against data loss. In addition, locally redundant storage (LRS) disks provide at least 11 nines (99.99999999999 percent) of durability each year, and zone redundant storage (ZRS) disks provide at least 12 nines (99.999999999999 percent) of durability each year. Azure Disk Storage is integrated with both availability sets and availability zone features provided by Azure, providing further protection for user data. Availability set integration ensures that disks associated with VMs in an availability set are isolated from each other by placing the disks in different storage scale units known as stamps, thereby avoiding a single point of failure. Similarly, availability zone integration protects against a single point of failure by giving users the ability to place disks in data centers in separate physical locations.³⁹

Storage scalability and using multiple volumes: Users can create up to 50,000 VM disks of a particular type per region in a subscription. Disks can range in capacity from 4 GiB to 64 TB, providing performance specs of anywhere from 120 to 400,000 IOPS and 25 MB/s to 10,000 MB/s of throughput, allowing you to customize the performance profile of your storage based on your needs.

For the data drive, consider using Premium SSD v2 disks which provide additional features, high IOPS, and high throughput for a competitive price. For the log drive, plan for capacity projections. On all disk types, you should thoroughly test performance and evaluate costs. In general, the Premium SSD v2 disks or the Premium P30-P80 disks are good choices for high IOPS. If you require extremely low latency, also consider evaluating Azure Ultra Disks. Note that Azure offers Premium SSD v2 disks in certain regions only.

Backup support for disks: Managed disks include support for Azure Backup, a service that can create a backup job with scheduled backups and customizable retention policies. Additionally, Azure Backup includes Azure Disk Backup, a native solution that automates periodic creation of point-in-time snapshots for managed disks and retention of said backups based on user-defined policy. It supports backups for both OS and data disks, regardless of whether they are currently attached to a running Azure VM.⁴⁰ It is an agentless solution, meaning that the snapshot process does not affect application performance.

Snapshots and images: You can take a snapshot of your managed disks, creating a read-only full copy of the disk that exists independently of the source disk. You can use these to create new disks and to take a backup of a disk at any time. Azure takes snapshots from a single disk and stores them as standard managed disks by default. Azure incremental disk snapshots are a cost-effective way to create point-in-time backups of your Azure managed disks. Unlike full disk snapshots, which capture full disk data, incremental snapshots capture only the changes to disks since the last snapshot. This results in lower storage costs and faster snapshot creation times.⁴¹

Azure Disk Storage also supports the creation of custom images from either a user-managed VHD in a storage account or directly from a generalized VM. The capture process includes all managed disks attached to a VM, creating a single image. You can use these images to clone VMs that you have configured beyond the standard OS images provided by Azure.

Azure Elastic SAN

Azure Elastic SAN is a service Azure provides that allows you to create and provision a cloud-based SAN interoperable with Azure VMs, as well as other Azure offerings such as Azure Kubernetes Service and Azure VMware Solutions.⁴² Azure Elastic SAN is not limited to working with one particular type of storage-consuming host or service. Additionally, you can provision volumes from a single SAN to use across multiple different Azure offerings. Azure simplifies the process of creating the SAN by handling the deployment, management, and initial configuration.

Elastic SANs can provide up to millions of IOPS with double-digit GiB/s of throughput and single-digit-millisecond latencies.⁴³ Its benefit in a MySQL use case would be if you had to store and centrally manage many terabytes of database data across multiple MySQL instances. However, storage latencies on Elastic SAN would likely be higher than those of Premium SSD v2 instances or Azure Ultra Disks.

The individual volumes that you provision from the SAN share the SAN’s performance values, up to a limit of 80,000 IOPS per volume. Individual volumes can have high IOPS and throughput, but the total IOPS and throughput of all your volumes can’t exceed the total IOPS and throughput your SAN has. In addition to these performance benefits, Azure Elastic SAN brings a centralized approach to management and volume creation and maintenance, like in more traditional on-premises storage array approaches. You should consider Azure Elastic SAN as your storage solution when you need highly scalable storage with centralized management for large-scale applications or when your workloads require low latency, high IOPS, and high throughput across multiple volumes.⁴⁴

Because you can connect Azure Elastic SAN with your Azure Virtual Machines, you could choose to back your MySQL databases with Azure Elastic SAN storage if your organization requires that approach.

Deploying MySQL on Azure IaaS

Creating an Ubuntu VM

Deploying MySQL on an Azure VM involves creating the necessary VMs and configuring Azure specifics—such as selecting subscriptions, regions, and disks—and then accessing the VM’s operating system and performing the necessary MySQL installation steps.

To demonstrate this, we used Ubuntu version 24.04 and a standard E2ads_v6 VM. We created an Ubuntu virtual machine by logging into the Azure Portal, navigating to the Virtual Machines service, and clicking Add. We set the Azure options and chose our OS image, Ubuntu 24.04 LTS. Next, we configured access essentials such as generating a new key pair and opening port 22. Finally, we configured disk and networking options, reviewed our VM, and clicked Create.

For step-by-step instructions, see below.

1. Log into the Azure Portal, and navigate to the Virtual Machines service.
2. To open the Create a Virtual Machine wizard, click Create → Azure Virtual Machine.
3. On the Basics tab, set the following:
1. Choose your Subscription from the drop-down menu.
2. Choose your Resource group from the drop-down menu.
3. Name the Virtual Machine.
4. Choose your Region from the drop-down menu. (We used East US.)
5. Under Availability options, choose Availability zone.
6. Choose your Availability zone. (We used Zone 3.)
7. From the Image drop-down menu, choose Ubuntu 24.04 LTS.
8. Under VM architecture, select x64.
9. Leave Azure Spot VM set to No.
10. Select the VM size you wish to use. (We used Standard E2ads_v6.)
11. Keep the Authentication type set to SSH public key.
12. Choose a Username and Key pair name, and select Generate a new key pair.
13. Leave Public inbound ports set to Allow selected ports.
14. For Select inbound ports, choose SSH (22).
4. Click Next: Disks.
5. On the Disks tab, set the following:
1. For the OS disk size, select 30GB.
2. For the OS disk type, choose Premium SSD from the drop-down menu.
3. Leave the default Encryption type.
6. Click Next: Networking.
7. On the Networking tab, leave all settings as default, and ensure the checkbox for accelerated networking is enabled.
8. Click Next: Management. On the Management tab, leave all defaults.
9. On the Advanced tab, leave all defaults. On the Tags tab, add any tags you wish to use.
10. On the Review + create tab, review your settings, and click Create.

After Azure has successfully deployed the VM, you can check its status in the Azure portal (see Figure 7).

Installing MySQL 8 on the Ubuntu VM

In this section, we present the steps to install MySQL on the VM. Note: Consider using Ubuntu Pro (https://ubuntu.com/pro), which includes Expanded Security Maintenance (ESM), security updates, and other tools.

1. Use SSH to connect to the Ubuntu VM.
2. Update the repository:

sudo apt update

3. Install and start MySQL 8:

sudo apt install mysql-server-8.0sudo systemctl start mysql.service

4. By default, the root account is not configured with a password. Set the root password:

sudo mysql mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘<password>’;mysql> exit

5. After installation, run MySQL security script to change default settings and enhance overall security:

sudo mysql_secure_installation

6. Follow the prompts to set a password, remove anonymous users and test database, etc.:
   1. Enter y to set up a valid password.
   2. Enter y to remove anonymous users.
   3. Enter y to disallow root login remotely.
   4. Enter y to remove test database and access to it.
   5. Enter y to reload the privilege tables.
7. Once MySQL secure installation is complete, connect to the database:

   sudo -u root -p

Compute considerations

VM sizing

VM sizing for MySQL involves optimizing Azure resources such as vCPUs, memory, and storage to improve database performance, scalability, and reliability. Below, we look at some key components in a VM and how they could impact performance.

CPU

MySQL can benefit from multi-vCPU VMs on Azure for parallel query processing. You need to monitor CPU usage closely to make sure your MySQL server works efficiently for complex operations such as joins, sorting, aggregation, etc. You can use the built-in tools such as PERFORMANCE_SCHEMA to monitor CPU usage and troubleshoot time-consuming queries. Additionally, you can use native Linux tools such as top, iostat, and nmon to monitor your server’s CPU usage as well.

Memory

Memory is crucial for databases to function properly. During a typical database query, if the data is not already in memory, the database retrieves it from disk and then temporarily stores it in memory. Then, the database takes further query actions, such as any aggregation, grouping, and sorting. This makes it important that your VM has enough memory.

MySQL implements various cache mechanisms such as the query cache,⁴⁵ InnoDB buffer pool, and key cache⁴⁶ to store frequently accessed data in memory and minimize disk I/O latency. Additionally, MySQL benefits from Linux OS filesystem caching, even though it does not directly manage these caches. It is important to tune both MySQL and the Linux OS caches to maximize the advantages of caching and optimize the use of system resources.

Storage considerations

Choose storage that meets the performance requirements of your application. Organizations should first measure the application demands, then use those IOPS measurements and projections to size and select Azure disks. Based on those projections, customers can choose both the disk layout (i.e., using multiple disks), and the disk model. A good starting point can be to use Premium SSD v2 due to its flexibility with adjusting IO performance. If you do select Premium SSD (non v2), then you could consider striping multiple less expensive disks if you require more IOPS than a single larger more expensive disk could deliver.

When choosing Azure Disk Storage for your high-performance applications, it is important to consider the disk’s IOPS, throughput, and latency characteristics to ensure they meet your application’s needs. OLTP applications need to process many concurrent user requests immediately. These requests are typically small and random IO operations that require very high disk IOPS. On the other hand, OLAP or data warehouse applications need to scan and read large volumes of data. These operations are generally large and sequential IO tasks that require high disk throughput.⁴⁷

You can also consider configuring multiple disks into a RAID group to combine their IOPS, throughput, and storage capacity to deliver even higher performance for your applications. On Linux, you can use the MDADM utility to stripe disks together and create a software RAID.⁴⁸

Choose an appropriate file system type (e.g., ext4 or XFS) and mount option (e.g., noatime, nobarrier, or nodiratime) for MySQL data directories to optimize disk IO performance and reduce file system overhead. Consider using separate file systems or mount points for MySQL data, binary logs, redo logs and other general logs⁴⁹ to minimize contention and improve IO throughput (see the “Configuring multiple volumes” section below).

Configuring multiple volumes

Historically, in specific cases, database architects and storage architects have recommended configuring multiple volumes with multiple underlying disks for database applications. This is not always necessary, and you should evaluate your situation as to whether this approach would provide performance or reliability advantages.

Examples of when to do this may include:

• Improved performance by separating data and logs: The IO profile that data volumes ingest (small block, random, read/write) is likely different from the IO profile of a log volume (potentially larger block, sequential, write only).
• Better scalability: To increase the throughput available to you, you could stripe virtual disks together using the lvcreate tool to increase the IOPS capacities that are available to you in Azure on a single volume. Note that you should keep your organization’s data redundancy policies in mind.
• Streamlined backup and restore: MySQL DBMS might benefit from separating data and log volumes when it comes to backup and restore speeds.
• Different disk types for data and logs: Because Azure offers a variety of disk choices, you can select the disk type that suits your performance needs specific to both the data and log storage of your MySQL database.

OS considerations

OS options

Adjust kernel parameters (sysctl settings) related to memory management, networking, disk IO, and process scheduling to optimize system performance for MySQL. Common kernel parameters to tune include vm.swappiness, vm.dirty_ratio, vm.dirty_background_ratio, fs.file-max, net.core.somaxconn, and kernel.shmmax. You can also consider various Linux filesystems, I/O schedulers, and NUMA settings. For a detailed explanation, please refer to https://www.percona.com/blog/linux-os-tuning-for-mysql-database-performance/.

Database considerations

MySQL parameters

MySQL has many parameters to optimize its performance. For an exhaustive list of all server parameters please visit https://dev.mysql.com/doc/refman/8.0/en/optimization.html. We discuss some important ones in this section.

Connection configuration parameters

Download the PDF version of this report

Legal disclaimer

Principled Technologies disclaimer

Principled Technologies is a registered trademark of Principled Technologies, Inc.
All other product names are the trademarks of their respective owners.

DISCLAIMER OF WARRANTIES; LIMITATION OF LIABILITY:
Principled Technologies, Inc. has made reasonable efforts to ensure the accuracy and validity of its testing, however, Principled Technologies, Inc. specifically disclaims any warranty, expressed or implied, relating to the test results and analysis, their accuracy, completeness or quality, including any implied warranty of fitness for any particular purpose. All persons or entities relying on the results of any testing do so at their own risk, and agree that Principled Technologies, Inc., its employees and its subcontractors shall have no liability whatsoever from any claim of loss or damage on account of any alleged error or defect in any testing procedure or result.

In no event shall Principled Technologies, Inc. be liable for indirect, special, incidental, or consequential damages in connection with its testing, even if advised of the possibility of such damages. In no event shall Principled Technologies, Inc.’s liability, including for direct damages, exceed the amounts paid in connection with Principled Technologies, Inc.’s testing. Customer’s sole and exclusive remedies are as set forth herein.