MariaDB Galera Cluster Replication

Extending the topic around database auto-clustering, we’d like to cover MariaDB Galera Cluster, high availability synchronous replication solution, that provides:

• True multi-master topology;
• Automatic new node provisioning;
• No data loss when nodes crash;
• Data replicas remain consistent;
• Automatic membership control;
• No complex and time-consuming fail-overs;
• Parallel transaction execution on all cluster nodes;
• No slave lag;
• No lost transactions;
• Reads/writes scalability;
• Smaller client latencies;
• Support of multi-cloud and multi-region deployments.

According to official documentation, Galera implements so-called certification–based replication. The basic idea is that the transaction to be replicated – the write set – not only contains the database rows to replicate, it also includes information about all the locks that were held by the database (i.e., InnoDB) during the transaction. Each node then certifies the replicated write set against other write sets in the applier queue, and if there are no conflicting locks, we know that the write set can be applied. At this point, the transaction is considered committed, after which each node continues to apply it to the InnoDB table-space.

This approach is also called virtually synchronous replication since it is logically synchronous, but actual writing (and committing) to the InnoDB table-space happens independently (and thus, strictly speaking, asynchronously) on each node.

In Jelastic PaaS, Galera Cluster can be automatically activated while creating the environment. The default topology consists of 2 ProxySQL Load Balancers and 3 MariaDB instances.

MariaDB Galera Cluster Installation

Navigate to the Jelastic PaaS dashboard, click Create Environment and select MariaDB server within the topology wizard. Then activate Auto-Clustering and choose Galera scheme. You can increase the default number of database nodes by pressing “+” in the Horizontal Scaling block.

In a few minutes, the environment will be created with the chosen topology and pre-configured interconnections.

You can perform the state-of-health monitoring of the cluster nodes via the Orchestrator admin panel that can be accessed with the credentials from the email related to ProxySQL Load Balancer deployment. The cluster members are shown at the panel as separated clusters with one instance inside.

Application Connection to MariaDB Galera Cluster

Let’s establish a connection to our MariaDB Galera Cluster from a Java application using ProxySQL Load Balancer as an entry-point. Follow the linked guide to find out more about the connection to other types of applications.

The creation of each master node within MariaDB cluster is accompanied by an email with phpMyAdmin credentials. Accessing databases via phpMyAdmin panel is useful for debugging or performing some manual operations on databases.

1. Log in to phpMyAdmin using Admin Panel URL, Username and Password (received in the email). Choose the existing database test (or create whatever you want) at the left pane. After that in the right pane you will see there are no tables in the database test.

2. Get back to the Jelastic PaaS dashboard. We use a separate environment with a Tomcat 9 application server for this example. Now, we have to create a database config file for our test application. To do this, click on the Config icon next to your compute node, then navigate to /opt/tomcat/temp directory and create a mydb.cfg file using the platform built-in file-manager.

3. Put the following lines into the mydb.cfg file and fill all the fields with the entry-point credentials (as shown on the picture above).

host=jdbc:mariadb://{connect_URL}/{db_name}?usePipelineAuth=false
username={user}
password={password}
driver=org.mariadb.jdbc.Driver

Where:

• {connect_URL} – link to your database cluster load balancer (i.e., ProxySQL node);
• {db_name} – name of the database (we choose test in the first step);
• usePipelineAuth – if activated different queries are executed using pipeline (all queries are sent, only then all results are read, permitting faster connection creation), this value should be set to false, as such implementation doesn’t work with ProxySQL Load Balancer in front of the cluster;
• {user} and {password} – database credentials as received in the email.

4. Download the test application using the link stated below and deploy it to the Tomcat application server.

https://download.jelastic.com/public.php?service=files&t=b2c6e4e01d487dfd5af953ba31dac848&download

5. Once deployment is finished, click Open in Browser in a popup window or next to your application server. Click on Create test table in your database button in the application window.

6. In order to ensure the connection was established and a new table was created, return to the MySQL admin panel.

You should see the table with the name {date-time of creation}. To make sure the replication works properly, go through all of the database phpMyAdmin panels in the cluster to check the data availability using the same credentials.

Tip: In Jelastic PaaS, all MariaDB nodes are equipped with the phpMyAdmin panel. To access it, just press Open in Browser button in the row of each database node.
 

Done! In just a few simple steps, you’ve established access to your database cluster from the your application and performed a simple management operation via a single entry-point.

Now you have a highly available and reliable MariaDB Galera Cluster automatically installed in matter of minutes, and provided with out-of-the-box intuitive management tools. Get started and benefit from embedded database auto-clustering at our Jelastic PaaS platform.