Master-Slave and Master-Master Replication with MariaDB/MySQL Auto-Сlustering

19

Today we continue the topic of database auto-clustering and will cover in more details two most common and frequently used data replication types for MariaDB and MySQL: Master-Slave and Master-Master replication. Let’s see how each of them lets easily solve performance problems, configure backups and eliminate system failures being packaged in a turnkey clustered solution that is installed in a few clicks.

Master-Slave Replication

Master-slave replication type is suitable for scale-out solutions that have a high number of reads and a low number of writes, as well as require distribution of read load across multiple database servers. For such needs, Jelastic packaged the following topology with pre-configured replication.

2897-1-master-slave-replication

This replication setup can be easily installed right from the Jelastic PaaS topology wizard using embedded Auto-Clustering feature that provides a set of benefits, i.e., new nodes auto-discovery, horizontal scaling with no downtime, intuitive management GUI, etc.

2897-1-master-slave-auto-clustering

Herewith, all of the required configurations are applied automatically, namely:

  • The asynchronous master-slave replication is implemented across all database instances with the very initially created MariaDB or MySQL container being assigned as master, whilst all the rest of nodes (including any subsequently added ones) will serve as slaves;
  • Environment topology is complemented with dedicated ProxySQL load balancers (added to the extra layer), that are intended to process incoming load and to distribute it across database nodes. By default, each balancer is assigned 8 cloudlets (which equals to 1 GB of RAM and 3.2 GHz of CPU) for automatic vertical scaling.

After creation, the cluster topology looks as follows.

2897-1-master-slave-cluster-topology

Note: If a master node goes down, the slave node should be manually promoted to master role. If a slave node fails, ProxySQL routes the read requests to the master node and the cluster keeps working.

You can access the Orchestrator panel (using the credentials from the received email) in order to check the replication state, to observe cluster topology connections and to monitor state-of-health of all nodes.

2897-1-master-slave-orchestrator-panel

As you can see, there is a container assigned as master, and the rest of the nodes (either initially created or as a result of scaling-out) are assigned with slave roles, i.e., the master-slave replication was successfully configured by the Jelastic PaaS platform.

Master-Master Replication

The master-master replication provides benefits of balancing the writing load, and simplifies recovery upon one of master nodes failure ensuring higher availability. Jelastic PaaS implementation of such database clustering is configured bidirectionally with two database nodes set up as masters.

While scaling out, the cluster is extended with extra slaves. To ensure equal replication workload distribution and increase the reading performance of the cluster, the extra slaves are added, in turn, first to one master node, and then to another master node, equally allocated among them.

2897-1-master-master-replication

You can access the Orchestrator panel (using the credentials from the received email) in order to check the replication state, to observe cluster topology connections and to monitor state-of-health of all nodes.

2897-1-master-master-orchestrator-panel

As you can see, two containers are assigned with master roles and replication is successfully configured.

The cluster can be scaled out with additional slave database servers via topology wizard.

Please note that, for production environments, it is better to perform scaling manually during a maintenance period or while having low load. If you prefer to activate automatic horizontal scaling for your database cluster, set up higher trigger intervals considering the amount of available data in order to avoid a loop of scale-out actions.

2897-1-master-master-cluster-topology

That’s it! Enhance your project by integrating an automatically configured MariaDB/MySQL database cluster with the most popular replication type already set up for you. Try it right now absolutely for free at our Jelastic PaaS platform and stay tuned for the next article of the series dedicated to the MariaDB/MySQL group replication.

SOURCEJelastic, Inc.