Deploying MariaDB Replication for High Availability

MariaDB Server offers asynchronous and synchronous replication. It can be set up to have a multi-source replication or with a multi-master setup. 

For a read and write intensive application, a master-slave setup is common, but can differ based on the underlying stack needed to build a highly available database environment. 

Having a master-slave replication setup might not satisfy your needs, especially in a production environment. A MariaDB Server alone (master-slave setup) is not enough to offer high availability as it still has a single point of failure (SPOF). 

MariaDB introduced an enterprise product (MariaDB Platform) to address this high availability issue. It includes various components: an enterprise version of MariaDB, MariaDB ColumnStore, MaxScale, and lightweight MariaDB Connectors. Compared to other vendors with the same enterprise solution offering, it could be a cost effective option, however not everyone needs this level of complexity.

In this blog, we’ll show you how to use MariaDB Server using replication on a highly available environment with the option to choose from using all free tools or our cost-efficient, management software to run and monitor your MariaDB Server infrastructure.

MariaDB High-Availability Topology Setup

A usual setup for a master-slave topology with MariaDB Server uses asynchronous or synchronous approach with just one master receiving writes, then replicates its changes to the slaves just like the diagram below:

But then again, this doesn’t serve any high availability and has a single point of failure. If the master dies, then your application client no longer functions. Now, we need to add in the stack to have an auto-failover mechanism to avoid SPOF and also offers load balancing for splitting read-writes and in a round-robin fashion. So for now, we’ll end up having the type of topology,

Now, this topology serves more safety in terms of SPOF. MaxScale will do the read and write splitting over the database nodes from your master against the slaves. MaxScale does a perfect approach when dealing with this type of setup. MaxScale also has auto-detection built-in. So whatever changes occur on the state of your database nodes, it will detect and act accordingly. MaxScale has the availability to proceed a failover or even a switchover. To know more about its failover mechanism, read our previous blog which tackles the mechanism of MariaDB MaxScale failover. 

Take note that MaxScale failover mechanism with MariaDB Monitor also has its limitations. It’s best applied only for a master-slave setup with no overcomplicated setup. This means that a master-master setup is not supported. However, MaxScale has more things to offer. It does not only do some load balancing as it performs read-write splits, it has built-in SmartRouter which sends the query to the most performant node. Although this doesn’t add the feature of being highly available but it helps the nodes from getting stuck in traffic and avoid certain database nodes from under-performing that can cause timeouts or to a totally unavailable server caused by high resource intensive activity on-going.

One thing as a caveat of using MaxScale, they are using BSL (Business Source LIcense). You might have to review the FAQ before adopting this software.

Another option to choose from is using a more convenient approach. It can be cost-efficient for you to choose using ClusterControl and have proxies in the middle using HaProxy, MaxScale, or ProxySQL, for which the latter can be configured to from light-weight to a more production-level configuration that does query routing, query filtering, firewall, or security. See the illustration below:

Now, sitting on top of them is the ClusterControl. ClusterControl is set up with a high availability i.e. CMON HA. Alternatively, the proxy layer can be chosen from either HaProxy–a very lightweight option to choose from, MaxScale, as mentioned previously, or ProxySQL which has a more refined set of parameters if you want more flexibility and configuration ideal for a high-scaled production setup. ClusterControl will handle the auto-detection in terms of the health status of the nodes, especially the master which is the main node to determine if it requires a failover or not. Now, this can be more self-sufficient yet it adds more cost due to a number of nodes required to implement this setup and also using ClusterControl auto-failover which applies on our advance and enterprise license. But on the other hand, it provides you all the safety, security, and observability towards your database infrastructure. It is actually more of a low-cost enterprise implementation compared to the available solutions in the global market.

Deploying Your MariaDB Master-Slave Replication for High Availability

Let’s assume that you have an existing master-slave setup of MariaDB. For this example, we’ll use ClusterControl using the free community edition which you can install and use free of charge. It just makes your work easy and quick to set up. To do this, you just have to import your existing MariaDB Replication cluster. Checkout our previous blog on how to manage MariaDB with ClusterControl. For this blog, I have the following setup initially as my MariaDB Replication cluster as seen below:

Now, let’s use MaxScale here as an alternative solution from MariaDB Platform which also offers high availability. To do that, it’s very easy to use with ClusterControl by just a few clicks, you are then able to set up your MaxScale that is running on-top of your existing MariaDB Replication cluster. To do that, just go to Manage → Load Balancer → MaxScale, and you’ll be able to setup and provide the appropriate values as seen below,

Then just enable or click the checkbox option to select which servers have to be added as part of your MaxScale monitoring. See below,

Assuming that you have more than one MaxScale node to add, just repeat the same steps.

Lastly, we’ll set up Keepalived to keep our MaxScale nodes always available whenever necessary. This is just  very quick with just simple steps using ClusterControl. Again, you have to go to Manage → Load Balancer but instead, select Keepalived,

As you’ve noticed, I’ve placed my Keepalived along with MaxScale on the same node of my slave (192.168.10.30). Whereas, on the other hand, the second (2nd) Keepalived is running on 192.168.10.40 along with Maxscale on the same host.

The result of the topology is production ready which can provide you query routing, high availability, and with auto-failover equipped with extensive monitoring and observability using ClusterControl. See below,

Conclusion

Using MariaDB Server replication alone does not offer you high availability. Extending and using third-party tools will equip you to have your database stack highly available by not only relying on MariaDB products or even using MariaDB Platform. 

There are ways to achieve this and manage it to be more cost-effective. Yet, there is a huge difference to availing to these solutions available in the market such as ClusterControl since it provides you speed, less hassle,, and of course the ultimate observability with real-time and up-to-date events not only the health but also the events occurring in your database cluster.