Implement Azure Database for MySQL Step-by-Step

By: Rajendra Gupta   |   Updated: 2024-09-13   |   Comments   |   Related: > Azure

Problem

MySQL is a relational database (OLTP) widely used in organizations for web and enterprise applications with high performance, security, scalability, flexibility, and open-source database supported on cross-platform OS. It can integrate with programming languages PHP, Python, and Java and supports ACID-compliant transactions.

You might be familiar with MySQL's on-premises deployment. With organizations moving to the cloud, knowing the deployment options for MySQL in Azure is necessary. Let's check out the tip for implementing MySQL in Azure.

Solution

Microsoft Azure for MySQL comes in two different flavors:

1. Azure Database for MySQL Flexible Server: It's a Database as a Service (DBaaS) or Platform as a Service (PaaS). MySQL deployment gives the following features:
   • Fully-managed MySQL, Community Edition.
   • Automatic OS patching, MySQL minor version upgrades, high availability, automatic failover, read replicas, automated backups, Azure Monitor integration and alerting, monitoring database operations, and query performance insights.
   • Automatic maintenance of the underlying OS, hardware, and database engine, and the flexibility to choose the scheduled maintenance window.
   • Based on Linux OS and provides 99.99% service-level agreement (SLA).
   • Supports features such as online storage scaling, auto storage scaling, and IOPS scaling.
   • Advanced security features include database encryption, private connectivity with Azure Private Link, and Microsoft Defender for Cloud support.
   • Supports automation for large-scale deployments.
   • Optimize the cost by stopping and starting Azure servers on demand.
2. MySQL on Azure Virtual Machine (VM): Infrastructure as a Service (IaaS) provides MySQL on Azure-managed VMs. It does not cover the benefits of DBaaS. It gives you full control over the MySQL environment, similar to the on-premise setup.

You can refer to the table Compare the MySQL deployment options in Azure to compare the MySQL flexible server and MySQL on Azure VM.

Deployment

Let's explore the Azure Database for MySQL flexible server deployment. Before we proceed further, there is good news for the readers. You can get a MySQL flexible server without any cost for 12 months under the free-tier account with the following limitations:

• 750 hrs. of burstable (1-2 vCores) instance.
• 32 GB data storage.
• 32 GB backup storage.

To deploy the Azure MySQL flexible server using the free deployment, search for free services in the Azure portal. Look for the Azure Database for MySQL in the results, as shown in the following image. Click Create.

Choose Flexible server on the page, then select Azure Database for MySQL as the deployment option.

Subscription

Select the subscription (if not using the free tier) and resource group (existing) in the flexible server configuration or create a new resource group.

Server Details. The Azure portal asks for the flexible server name, region, MySQL version (latest 8.0), and workload type. The workload type helps to suggest the suitable compute and storage configuration. For example, here I have chosen the For development or hobby projects workload type, and it suggests Burstable, B1ms configuration with the following configuration:

• 1 vCore
• 2 GiB RAM
• 20 GIB storage
• Auto scale IOPS.

You can click on the configure server button and change the configuration. You get the following options to choose from:

• Burstable (1-20vCores): Suitable for development\learning purposes. It provides intermittent performance demands.
• General Purpose (2-96 vCores): Suitable for production workload with balanced compute and memory requirements.
• Business Critical (2-96 vCores): Suitable for critical applications that require high computing power and resilience.

You also have the option to choose the storage size (GiB), IPOS (Auto Scale or Pre-provisioned IOPS), and high Availability and configure the automatic backup retention (default seven days).

Authentication Method

You need to choose the authentication method that is suitable for your requirements. The available authentication methods are:

• MySQL authentication only.
• Microsoft Entra authentication only.
• MySQL and Microsoft Entra authentication.

For MySQL authentication, specify the admin credentials as shown below.

Network Connectivity

You can use public or private access to connect with Azure Database for MySQL flexible server. You can configure (allow) the limited IP addresses to connect in a public access. However, if you choose private access, it uses the VNet integration, and the virtual network's connection to MySQL is limited. Let's go with public access for this tip.

You can add the current client IP address or use the IP range (start and end IP address) to allow connections for public access.

Note: Be careful when adding the IP range 0.0.0.0 – 255.255.255.255. The Azure MySQL server allows connections from any IP address. You must not use this rule for any organization configuration. Use it only for learning purposes.

Note: Azure does not allow changing the connectivity method after configuring the Azure MySQL flexible server. For example, you cannot switch from public to private VNet.

Review the configuration and estimated total for your deployment on the next page.

Note: The estimated price for a free-tier account MySQL flexible server deployment will not be zero. Azure will not charge you when you are within the free-tier limitations.

Click Create, and deployment will start, as shown below.

When deployment is complete, the following notification will appear.

Click Deployment details to see the Azure Database for MySQL flexible sever.

Click the flexible server name to see its properties configuration.

You can scroll down and get the various connection strings to connect MySQL with .Net, PSQL, Python, JDBC, PHP, Node.js, Ruby, C++, and Azure CLI.

You can also click Connect to launch Cloud Shell and connect the MySQL flexible server.

Stop and Start the Flexible Server

It is advisable to stop the flexible server immediately after use. Once stopped, the billing also stops for the compute tier. You only pay for the storage while the server is in stopped status. You can have significant savings for development and test MySQL resources costs. If you revisit the estimate of deployment cost, you can see the compute tier is most of the deployment cost. Therefore, you can save running and consuming those compute resources with the stopped instance.

Note: If you do not start the flexible server manually for 30 days, it will be started automatically.

Click Stop and provide acknowledgment.

The notification toolbar shows the status message.

Click Start to bring the MySQL flexible server online for use again.

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