The Best Way to Host MySQL on Azure Cloud

Are you looking to get started with the world’s most popular open-source database, and wondering how you should setup your MySQL hosting? So many default to Amazon RDS, when MySQL performs exceptionally well on Azure Cloud. While Microsoft Azure does offer a managed solution, Azure Database, the solution has some major limitations you should know about before migrating your MySQL deployments. In this post, we outline the best way to host MySQL on Azure, including managed solutions, instance types, high availability replication, backup, and disk types to use to optimize your cloud database performance.

MySQL DBaaS vs. Self-Managed MySQL

The first thing to consider when weighing between self-management and a MySQL Database-as-a-Service (DBaaS) solution is what internal resources you have available. If you’re reading this, you likely already know the magnitude of operational tasks associated with maintaining a production deployment, but for a quick recap, there’s provisioning, deprovisioning, master-slave configurations, backups, scaling, upgrades, log rotations, OS patching, and monitoring to name a few.

An internal MySQL expert, or a team of DBA’s depending on your application size, can certainly handle these with your organization for you, but the question becomes where you want your team’s efforts focused. Many decide to move to a MySQL DBaaS to automate these time-consuming tasks so they can focus more on the development and optimization of their applications databases. A good example would be slow query analysis. While almost every DBaaS offers a MySQL Slow Query Analyzer tool to help identify trouble queries, this task still requires human skill and intuition to determine how to optimize those queries impacting their application performance.

Whether you’re a startup company or a fortune 500 business, you’ll find many organizations choose to leverage a DBaaS to optimize their DBA’s time, while the same business types and sizes also choose to stick with internal self-management. For many enterprise businesses, the decision largely comes down to customization and control. This is why we caution against defaulting to Azure Database, or it’s AWS competitor, Amazon RDS, as they do not allow you to keep MySQL superuser access or even SSH access to your machines. Additionally, the ability to customize your deployment setup is highly limited, such as the instance types, RAM, disk size, or IOPS you can use. You’ll learn more about the best instance types and disks to use below, and you can check out this MySQL Provider Comparison to see the advantages and limitations of the top four managed MySQL solutions, ScaleGrid, Compose, Azure Database, and Amazon RDS.

High Availability Deployment

If you’re deploying in production, you should always setup MySQL as a master-slave deployment. Standalone deployments are a single node without any replication, and should really only be used for development or testing environments. With master-slave deployments, you’re able to configure high availability so if one of your nodes goes down, you can failover to a slave with zero downtime. This is typically setup either as a 3-node master-slave-slave, or a 2+1 node master-slave-quorum. The advantage of using a quorum is that it’s a lower cost alternative, but the downside is that you only have 2 data-bearing nodes as the other acts as a quorum node to determine the best failover course. If your application is able to read from the slave, then you need to do read scaling so they return the same data from the cluster volume with minimal lag.

When using a MySQL master-slave configuration, we recommend setting up semisynchronous replication to improve your data integrity with data redundancy. This ensures that when a commit returns successfully, the data exists both in the master and the slave, so in the event a datacenter goes down, your MySQL master can failover to a slave without any data loss. You can do this with either asynchronous or semisynchronous replication, and learn more about it in our MySQL High Availability Explained – Part II blog post.

So, how do we configure high availability for MySQL on Azure? We need to distribute our slave instances across different Azure availability zones (AZ). So, we want to make sure that we choose an Azure region that has at least 3 AZ’s, putting each instance in a different AZ. We do this because the availability guarantees are across AZ’s, so if 1 zone goes down, your application database is still able to stay online through the other 2 AZ’s. Availability zones are fairly new to Azure, so if you’re working in a region that doesn’t offer AZ’s, you have the option to use availability sets. These are slightly weaker than AZ’s, but ensure that you’re deployed across different domains and racks to protect you against a potential outage. There’s also the option to deploy across regions, but this is a more complicated setup so we recommend reaching out to discuss before implementing.

Azure Virtual Networks

The best way to protect your database from the internet is by deploying it in a private subnet to ensure it is not exposed. Azure makes this easy to setup through the use of a Virtual Network (VNET) which can be configured for your MySQL servers. With an Azure VNET for MySQL, you’re able to setup secure communications between your servers, the internet, and even your on-premise private cloud network. These are typically configured to communicate across a single network, but if you need to connect more than one region, you can create multiple VNETs to communicate through Virtual Network Peering.

Additionally, you can manage your MySQL access control through Network Security Groups (NSG) rules without having to deal with IP whitelists. This is not available through Azure Database for MySQL, but both VNET and NSG can be configured through our MySQL Bring Your Own Cloud (BYOC) plans on Azure where you able to host your clusters through your own cloud account.

Azure Instance Types

Another important aspect to consider is the performance of your MySQL instances in the public cloud. Azure cloud offers multiple instance types that can be used for your MySQL hosting, including Es2 v3, Ds2, v2, and Ls4.

We recommend starting with a memory optimized instance types as databases require a lot of RAM and are looking for the fastest disk speed possible for the best performance. The Es2 series is typically a good starting point for most applications MySQL workloads. From there, you can do some performance testing to see if you require more CPU, in which case, balanced instance types or CPU-intensive instance types might better serve your MySQL needs, such as the Dv3 instance types. Your performance tests may also show that you need more I/O (input/output), you can move to a disk-intensive instance type.

If you plan to leverage Azure as your MySQL cloud provider for the next 1-3 years and maintain fairly consistent deployment configurations, you can also consider reserved instances. These are essentially prepaid instances which allow you to achieve considerable cost savings for your MySQL hosting. On average, you can save around 20% to 30% for one year reserved instances, and 40% to 50% on the 3 year reserved instances.

Azure Disk Types

The first determination you need to make when it comes to choosing an Azure disk type for your MySQL deployments is whether to go with a managed vs. unmanaged disk. The unmanaged disks are the legacy disks Azure offers where you have to setup the storage account, map your disk to the storage account, and monitor the IOPS use and limits for that storage account. We highly recommend using managed disks, and if you’re still deploying with unmanaged disks, you should consider moving to the managed.

MySQL Dev/Test Environments: Standard Disks

There are multiple managed disks types available through Azure, the default being the standard disks. Standard disks can support up to 500 IOPS (input/output operations per second) and are good for development and testing operations as they can be resized dynamically, but should not be used for MySQL production deployments.

MySQL Production Deployments: Premium Disks

For your MySQL production servers, we highly recommend leveraging Azure premium disks. There are a wide variety of premium disks you can pick from. For each premium disk, you can choose the best size, and each size comes with different Provisioned IOPS so you can select the one that best fits your application needs.

MySQL Production Deployments: Local SSD

Azure Local SSD’s are a high-performance alternative to premium disks, typically best suited for large clusters. The Local SSD’s provide a much higher I/O performance, and the best throughput in Azure. But, they do have a drawback in that they are ephemeral disks, not a permanent store, so if you stop the instance, the data goes away. We recommend the Ls v2 series which are very fast, but caution that the CPU is really weak which can cause machine bottlenecks.

MySQL Backups on Azure

The best way to backup your MySQL data on Azure is by using managed disk snapshots. A snapshot is a read-only point in time version of a disk. These backups can be read, copied, or deleted, but note that they cannot be modified. It’s a good idea to do full backups so all of your databases, users, and settings are backed up on the instance in case you ever need to recover a MySQL database. It’s also a good idea to encrypt your backup snapshots so that the backup can only be restored onto the machine in which the backup was taken.

Your MySQL backups will result in additional Azure data storage charges, unless you’re leveraging an all-inclusive MySQL on Azure solution like our Dedicated Hosting plans at ScaleGrid. In order to control costs, it’s a good idea to automate your backups through a customizable schedule that allows you to configure the frequency of your backups, the maximum number of backups to retain, and your backup target. This of course also helps you ensure your MySQL data is regularly backed up in case of any data loss in your production deployment so you can quickly recover with a recent backup.

If you have any questions on the best way to host MySQL on Azure, leave us a comment below or get in touch with us at [email protected]. You can also start a free 30-day trial to explore the advantages of leveraging a fully managed MySQL service to improve the performance of your deployments.