MariaDB/MySQL Auto-Сlustering

 Database clusterization is an obligatory requirement for highly loaded production applications to ensure data availability and high performance. However, the configuration of a reliable cluster is not a trivial task even for experienced developers and system administrators. For solving this problem, BitssCloud introduced out-of-the-box clustering for MariaDB and MySQL to make applications highly available by default.

The implemented solution provides a set of benefits:

• high availability with pre-configured replication options – Master-Slave, Master-Master, Galera, Single- and Multi-Primary Group Replication
• scalability and autodiscovery – new nodes, added during horizontal scaling, are connected to the cluster with all required adjustments being applied automatically
• efficient load balancing – each cluster is supplemented with two ProxySQL nodes for load balancing with the automatic splitting of reading/write requests
• automated failover – the database nodes that are temporarily unavailable or have high latency are automatically excluded from the cluster and re-added once the connection is restored.

All these benefits can be achieved just in a few clicks within a topology wizard. Explore the steps below to activate auto-clustering for your MariaDB and MySQL databases in BitssCloud PaaS.

Enable Automatic Clustering for Databases

Click on NEW ENVIRONMENT on the dashboard and choose MariaDB or MySQL database.

Activate the Auto-Clustering button. As a result, a dropdown with different replication schemes will appear:

• MariaDB – Master-Slave, Master-Master, and Galera
• MySQL – Master-Slave, Master-Master, Single- or Multi-Primary Group Replication.

Choose the replication type you prefer and click Create. That’s all! No configurations required, the cluster is ready to work with.
After successful installation, you’ll receive a number of emails with the cluster information:

• PHPMyAdmin at Master Node  web administration interface with credentials to access the database server for interactive management.
  
• Entry Point for Connections to MySQL Cluster – hostname and credentials for connecting an application to the database cluster. In BitssCloud, each MariaDB/MySQL auto-clustering solution has two ProxySQL nodes in front of the database cluster. These nodes form a proxy extra layer referred as the entry point for the database cluster with hostname as follows: proxy.${envName}.${platformDomain}.
  
  Tip: To override database credentials generated by BitssCloud with custom ones, do the following:

1. Click on the Variables button right after you’ve selected a replication Scheme.
   
2. Then press Add button to provide the custom user’s database credentials via two variables DB_USER and DB_PASS as follows:
   
3. Once custom credentials are specified they’ll be mailed to the user upon cluster successful installation as for access to the PHPMyAdmin at Master Node and as Entry Point credentials.

• MySQL Orchestrator Panel – credentials to access the Orchestrator panel, intended for convenient cluster management

Use the received credentials to access the admin panel of cluster Orchestrator installed on ProxySQL, which provides a possibility to review the cluster topology information: slick visualization of topologies, replication problems if there are any, read/write distribution, state of health check-ups and autodiscovery of newly added DB nodes, etc.

What Replication Type to Choose?

Let’s consider the details about each replication scheme available for databases within BitssCloud in order to understand which one is the most suitable for the specific needs and use cases.

Master-Slave MariaDB/MySQL Replication

Master-slave replication is the most commonly used topology, that provides a good consistency (i.e. exactly one node to modify data), but no automatic failover upon master failure. Write latency in asynchronous replication is low because the write is recorded locally by the master server before writing to the slave servers. It allows to scale out the reads, providing the highest performance,  since adding more replicas does not affect replication latency. Slaves can be read without impact on the master, providing such obvious advantages as:

• High performance for reading requests
• Database backup can be done with no impact on the master instance
• Analytical requests can load the slave instance only without affecting the master
• Slave instance can be taken out of the cluster and got back with subsequent master instance data catch-up with no database downtime
  

Master-Master MariaDB/MySQL Replication

Master-master asynchronous replication operates with two master nodes simultaneously, Compared to the default master-slave solution, it benefits on the balancing of writing load and simpler recovery upon one master node failure.

In contrast to the default settings, in BitssCloud master-master cluster scaling leads to slaves addition to the cluster. Upon creation, the slave instances are equally distributed between master nodes allowing smoothly distribute of the replication workload and increasing the reads capacity of the cluster.

MySQL Group Replication (MGR)

MySQL Group Replication mechanism is implemented by a MySQL Server plugin that provides distributed state machine replication with strong coordination between servers. Group Replication allows creating the fault-tolerant systems with redundancy by replicating the system state throughout a set of servers. Consequently, even if some of the servers fail (as long as it is not a majority), the cluster will still be available. Thus servers coordinate themselves automatically when they are part of the same group. When the new server, that (re)joins the group, will be automatically synchronized with the other group members.

According to MySQL official documentation, if one server node fails and is removed from the cluster, it gets back manually, but in BitssCloud, it will be automatically returned to the cluster upon server node availability.

Also, you should consider that MySQL group replication supports up to 9 members, while any subsequent join request will be automatically refused.

Single-Primary Group Replication

The group can operate in a single-primary mode, where one group member is issued with read-write permissions (i.e. is configured as primary), while the remaining members are set to read-only acting as the hot standby. Herewith, in case of the primary failure, an automatic election will be performed to assign a new one.

Multi-Primary Group Replication

Alternatively, the group can be deployed in a multi-primary mode that shares all of the MGR specifics described in the single-primary mode, but all of the cluster members are provided with read-write permissions and can accept updates, even if they are issued concurrently. Such implementation provides benefits of simultaneous writing into different MySQL servers and does not require time to elect and prepare a new primary (upon failure of a previous one).

MariaDB Galera

Galera cluster is a type of multi-master synchronous replication which is performed at a transaction commit time, by broadcasting the transaction write set to all cluster nodes for applying and making sure the write that was sent to all nodes in the cluster before this write will be actually committed.

A user application can send reads and writes to any node in the cluster, which provides an ability to scale-out read and write transactions. Adding nodes to a cluster is fully automated. Excluding nodes from the cluster is just a matter of removing the unneeded or failed ones. There is no need anymore to implement the bulky logic for the separation of reads and writes, the scaling potential can be immediately implemented with no need to change the application logic. Galera offers one of the best protection against data loss and inconsistent databases since there is no delay in replicating data. If one of the cluster nodes fails the user application won’t see it and will continue to serve users using the other nodes that may also be located in other data centers.