MySQL Auto-Clustering: High Availability, Embedded Load Balancing and Choice of Replication Types

By: Segiy Sergienko, 25 Jan 2019
4 minutes

Reading Time: 4 minutes

Jelastic.Cloud is a cloud platform with extensive autoscaling capacities provided in PaaS form, created especially to strip an applications’ deployment process of the necessity to make complex server environment configurations. The solution allows optimizing resources at the points of most intense server loads and minimizing this load during user inflows. Website migration to Jelastic Cloud can be conducted in literally a few clicks, while the CMS deployment in the cloud takes no more than a couple of minutes. Additionally, with Jelastic, you can rapidly create a multi-node MySQL cluster equipped with dedicated tools for load balancing and DBMS duplication (the only significant action required from an administrator is to define the number of nodes).

In the following article, we will review all the nuances related to a highly-scalable MySQL cluster, which can help you keep the server load-balanced and provide the maximum possible level of accessibility from to the replication of cluster nodes.

Why Jelastic?

Without further ado, let’s take a brief look at the general advantages of Jelastic Cloud from the perspective of database replication based on automatic MySQL clustering:

  • increased performance. Jelastic Cloud distributes the load across several database copies at once, thus increasing cluster performance speed. Record updating is conducted through the master server, while a random number of slaves are responsible for processing reading operations. Such an approach separates the master and slave responsibilities, boosting cluster performance efficiency.
  • maximum accessibility. A database interacts with devices through the ‘master–slave’ principle, so any device can be disconnected from the cluster at any moment without affecting the overall performance.
  • online monitoring. The master server is stripped of the excessive load even in cases where administrators need constant app or website performance monitoring. In particular, the monitoring can easily take place on the slave device.
  • remote resource support. Due to an ability to create local copies of remote sites, Jelastic does not require a continuous connection to the master server.
  • self-management. Jelastic Cloud also minimizes the admin workload: as requests come in, the tool automatically defines a number of nodes and offers topologies or data replication types most appropriate to a particular case.

In order to appreciate the power and significant advantages of Jelastic Cloud in practice, experts recommend employing at least two servers that feature dedicated databases.

Automatic MySQL Clustering Via Jelastic Cloud: Benefits

Jelastic MySQL auto-clustering is characterized by:

  • several data replication options. You can select one of the following replication variants: Master–Slave, Master–Master, or Single- or Multi-Primary Group Replication;
  • MySQL load distribution. Due to the fact that ProxySQL distributes reading and writing operations responsibilities via the PR algorithm, the load between nodes is distributed equally, which increases app or website performance;
  • extensive horizontal scaling capabilities. When a necessity to add new nodes to the cluster arises, configurations stored in nodes that are already in the cluster are automatically duplicated. This makes horizontal scaling rapid and eliminates the need for any significant participation by the network operator;
  • downtime minimization. By forming a fail-proof topology, any node or even a number of them in the cluster can be reconfigured without affecting application/site work. Moreover, if some node (or nodes) goes out of order, this is automatically excluded from the cluster without stopping the operation of the rest of the nodes (later on, when they are restored, they are brought back into the cluster automatically as well);
  • comfortable interaction. The cluster is managed via an intuitive GUI, providing extensive opportunities for manual network configuration and reconfiguration.

Replication Types

Replication is a necessary condition in adjusting a fail-proof, reliable cluster. In the case of MySQL Auto-Clustering from Jelastic Cloud, several types of replication can be employed.

Simple Replication

This kind of replication is further subdivided into the following types:

  • ‘master–slave’ – there is one node that has the responsibility of adding new nodes and passing new configurations to newly-connected nodes. It should be noted that this node is incapable of auto-switching to another node in the case of failure;
  • ‘master–master’– here, two managing nodes operate simultaneously; the rest of the nodes operate in ‘slave’ mode. As opposed to the previous option, the load here is distributed equally between the two nodes, which makes the chances of downtime minimal. On the other hand, with a much more complicated structure, this topology risks causing delays in cluster performance and affecting node synchronization (thus, there is a risk that both managing nodes can start recording one and the same database).

Group Replication

MySQL group replication provides an even more fail-proof and high-performance topology operation at a lower cost level (compared to any other simple replication sub-type). Group replication has the following sub-types:

  • one primary group – a group of replicated nodes where the cluster autonomously selects the ‘master’ node (i.e., the node that will accept entry-making responsibilities);
  • multi-primary group – any node can update data in the DBMS (i.e., each has permission to both read and write records).

Note that any sub-type of group replication can include up to nine nodes.

MySQL Auto-Cluster Configuration

And now for a brief step-by-step tutorial on configuring MySQL Auto-Cluster:

  • Launch Jelastic Marketplace;
  • Set up MySQL Auto-Cluster, choosing the required replication and cluster type;
  • Define the internal name of the environment, additional nickname, and environment region;
  • Select the ‘install’ option;
  • Voila! Your new cluster with the topology you chose is up and ready!

In order to make sure you successfully created a cluster, check that a load balancer has been created and that MySQL pseudonyms have been renamed according to the selected data replication type.

Once all the above-mentioned manipulations have taken place, you will receive several emails from the MySQL Auto-Cluster service. One of them will include the data for accessing the PHPMyAdmin administrator panel (you will need this during DBMS management), login data for accessing the Orchestrator panel (required for cluster configuration), and info on the cluster for attaching it directly to an app or website.  Do you have an idea for the project? Contact us today to discuss all the features, fine details, and possible ways to implement your ideas.