Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

The Best Way to Host MySQL on Azure Cloud

DZone 's Guide to

The Best Way to Host MySQL on Azure Cloud

See one person's opinion on the best way to host MySQL on Azure Cloud.

· Database Zone ·
Free Resource

Are you looking to get started with the world’s most popular open-source database and wondering how you should set up 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.

You may also like: MySQL Tutorial: A Beginners Guide to Learn MySQL

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, de-provisioning, 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 a 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.

MySQL Hosting Deployment - Slow Query Analyzer - ScaleGrid DBaaS

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.

High Availability Deployment

If you’re deploying in production, you should always set up 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 set up 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 semi-synchronous 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 that a datacenter goes down, your MySQL master can failover to a slave without any data loss. You can do this with either asynchronous or semi-synchronous replication.

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 set up 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 set up 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.

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 type, 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 that 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 that Azure offers, where you have to set up 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 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. 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 also helps ensure that 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.

Topics:
mysql ,sql ,azure ,database ,cloud computing ,developer ,dba ,backup ,high availability ,database management

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}