How to Setup MySQL (MariaDB) with Master-Master Replication on a Windows Azure availability set of VMs

Installing MySQL (actually MariaDB)

If you wonder why we care about availability sets you better read here

By just having two instances of a database on two different VMs, doesn’t offer much even if the VMs are members of an availability zone. In order to make something out of the configuration of our machines we need the data that our application generates to be replicated between the two databases.

Replication is a quite rich and compilcated subject, the most common replication pattern is the simple master-slave replication in which one “master” node takes care of all the application writes while a number of “slave’ nodes are only reading the data, on top of that basic concept it is also possible to have failover configurations and other desirable characteristics on the DB cluster. The issue in this approach is what will happen if the “master” node fails, and although there might be solutions to that, e.g. having more than one “master” node, here we are having a situation with limited resources and thus we cannot really afford to have sets of master and slave nodes. In our case we would like to follow a master-master configuration where each node is able to accept writes and distribute them throughout the cluster. In this way we can redirect all traffic on one of the VMs we have on Azure, the writes will be replicated also on the second VM and in the case where something happens to the first node, automatically the second one will continue serving the application.

So, in order to have Master-Slave replication we’ll use MariaDB together with a set of patches known as “Galera” which implements synchronous master-master replication. MariaDB is an enhanced, drop-in replacement for MySQL which means that in most cases we can use the same drivers as MySQL to access it.

We’ll follow the standard procedure for installing mariadb-galera as it can be found here and here.

First we have to prepare our VM  by adding all the appropriate packages and configurations to add the repository from which to fetch the MariaDB Galera packages. To find the correct repository we can also use the official repository configurator.

So first we have to add the repository by doing the following:

and then install the appropriate packages

Using the above command, mariadb with the galera patches will be installed without setting any root password, if you want to also be asked for a root password remove the DEBIAN_FRONTEND=noninteractive from the beginning of the command. If everything went ok you should now have a running mariadb instance on your server but it is not yet running in a cluster mode, in order to do that we’ll need to add some configuration to our mariadb installation.

the above conclude the installation, but we need to configure the nodes to be members of a cluster, as they are right now they are running in stand-alone mode, we use the following configuration:

This configuration has to be copied in all of our cluster members . Just create a new cnf file, e.g.  myCluster.cnf under /etc/mysql/conf.d/ and save the above config parameters. Just make sure to update the wsrep_cluster_address parameter with the IPs of your nodes.

Another notable parameter in our config file is the wsrep_sst_method which is used to define which method will be used by wsrep to perform the replication, in our case we’ll be using rsync, if you remember we also install it at the previous step. There are also other agents available for state snaphost transfer which is the process performed to replicate the data between the nodes, possible alternatives are mysqldump and xtrabackup with each one having its own pros and cons, for a simple case like the one we are discussing right now though rsync should do the job.

Then we can just start our cluster by invoking the following command on one of our nodes.

the –wsrep-new-cluster parameter is needed only for the first member of our cluster, which will also have to create to cluster, the rest of the nodes do not have to use this parameter.

If everything went well, you should now have a cluster of MariaDB instances operating in a master-master replication node, don’t forget to have a look at the syslog file, you can see some interesting output of your cluster there and get some hints of what the nodes are doing.

Now you can continue in the final step of setting up ElasticSearch here.

Links

[1] https://www.digitalocean.com/community/articles/how-to-configure-a-galera-cluster-with-mariadb-on-ubuntu-12-04-servers excellent tutorial on how to setup mariaDB with the Galera cluster patches from digital ocean.

[2] https://blog.mariadb.org/installing-mariadb-galera-cluster-on-debian-ubuntu/ another tutorial for mariadb + galera but this time from the mariadb official blog.

 

Advertisements