Azure Database for MySQL and Replication

Azure Database for MySQL and Replication

Some time ago I was implementing Azure Database for MySQL for a project I was working on. When a co-worker of mine heard about it, he asked if had explored the replication capabilities of the service. For this particular project we were making use of Azure DB for MySQL on a pretty simple way, but still, he made me curious so I started researching.

By the time I started my research there was only one replication option available for MySQL in Azure, which was using it as a destination for a replica (as described here). Unfortunatelly I had to shift my attention to some other topics, but finally, around a week or so ago, I was able to get back to this, and to my surprise, I found that Microsoft had released around september another replication option, read only replicas using a MySQL PaaS as master (more details described here).

This puts two options on top of the table to use replication with MySQL PaaS services:

  1. To use MySQL PaaS service as master and deploy MySQL PaaS instances as replicas.
  2. To use a VM running MySQL, and deploy MySQL PaaS instances as replicas.

Why replication?

Before moving forward, let’s stop a minute here. Why replication? What’s the problematic that we are trying to address? In essence, replicating data across instances of a given service might help to address two different needs:

  1. Increased availability (HA and/or DR)
  2. Distributed load

When talking about the first, Microsoft sates on their MySQL PaaS service:

The Azure Database for MySQL service provides a guaranteed high level of availability. The financially backed service level agreement (SLA) is 99.99% upon general availability. There is virtually no application down time when using this service.

So this leaves us with using replication for distributing load, in fact Microsoft refers to it in this paragraph:

Applications and workloads that are read intensive can be served by the read-only replicas. Read replicas help increase the amount of read capacity available compared to if you were to just use a single server for both read and write. The read workloads can be isolated to the replicas, while write workloads can be directed to the master.

Addressing the issue

So now that we know what kind of issues or needs does Replication address, let’s see what does Azure provides us with. Based on what was described in the beginning of this article, there are two options available.

MySQL PaaS service as master with MySQL PaaS instances as replicas

Recently Microsoft enabled the possibility of deploying read-only replicas for an existing MySQL PaaS instance (more details described here). This sounds good but it does have some limitations and things you need to consider if you are going to deploy this:

  • You can deploy a maximum of 5 read-only replicas.
  • They must be all in the same location as the master.
  • When deploying the first slave, the master will be brought down and back up, this is because the binary log is not enabled by default, and when you deploy the first replica, the Azure back-end is enabling the binary log for the master instance.

If you visit my Github repo MySQL_Replication in there you’ll find the paas.azuredeploy.json and paas.azuredeploy.parameters.json files which will deploy a solution looking like this:

img01.png

The repo is fully documented and all the param in the main ARM template are documented.

VM runnign MySQL, and MySQL PaaS instances as replicas

But what if we want to run our replicas in a different location other than the same location than the master’s?

For such, under the same repo mentioned before (MySQL_Replication) I have published another ARM tempalte (iaas.azuredeploy.json). This ARM template will deploy a Vnet + Subnet, attach a NSG to the subnet, a server running Ubuntu with a Public IP, and finally, install MySQL on the Linux VM and configure replication against the MySQL PaaS instance.

As stated before, the repo is fully documented and all the parameters in the main ARM template contain proper descriptions.

The result will look like this:

img02.png

In the case that you’d like to use this ARM template but require to deploy multiple PaaS instances as Slaves, most likely you’ll have to provide a list of the PaaS instances rather than just the one as it is today to the install_mysql.sh script, and you’ll need to iterate over these lines with for each of the instances provided:

Reading List

Before you leave, these are some of the links or articles mentioned through the article, I recommend you taking a look at these for further information on any of the topics covered here:

 

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.