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 failovers
- 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 (ie. 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 tablespace.
This approach is also called virtually synchronous replication since it is logically synchronous, but actual writing (and committing) to the InnoDB tablespace happens independently (and thus, strictly speaking, asynchronously) on each node.
In Jelastic, Galera Cluster can be automatically activated while creating the environment. The default topology consists of 2 ProxySQL load balancers and 3 MariaDB instances.
Galera Cluster Requirements
In Galera, databases shall comply with some requirements. The key ones user should follow creating tables are:
- InnoDB Storage Engine. Data must be stored in the InnoDB transactional storage engine.
- Primary Keys. Every table that is to be replicated must have an explicit primary key, either a single or a multi-column index.
The full list of the well-known limitations can be found in the official documentation.
MariaDB Galera Cluster Installation
Navigate to Jelastic dashboard, click Create Environment and select MariaDB server within topology wizard. Then activate Auto-Clustering and choose Galera scheme. You can increase the default number of databases by pressing “+” in the Horizontal Scaling block.
If required you may exclude ProxySQL layer from the cluster topology before installation with the respective switch. Keep in mind that you won’t be able to do that later and vice versa if you switch it off before installation you will not be able to add the proxy layer afterward.
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 Java web-application, using ProxySQL load balancer as an entrypoint. Follow the linked guide to find out about the connection to other types of applications.
The creation of each master node within MariaDB cluster is accompanied by the email with phpMyAdmin credentials. Accessing the database via phpMyAdmin panel is useful for debugging or performing some manual operations on the 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 Jelastic dashboard. We use a separate environment with a Tomcat 9 application server for this example. Now, we have to create 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 mydb.cfg file using platform built-in file-manager.
3. Put the following lines into the mydb.cfg file and fill all the fields with entrypoint credentials like 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 DB cluster load balancer (i.e. ProxySQL node)
- {db_name} - name of the database. We chose 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 the ProxySQL in front of the cluster
- {user} and {password} - database credentials received in the email
Download test application using one of the links below and deploy to the Tomcat server.
For Apache Tomcat 9 and below:
https://download.jelastic.com/public.php?service=files&t=40b1bd6e376db56df1945fade1cbd851&download
For Apache Tomcat 10:
https://download.jelastic.com/public.php?service=files&t=0ce6e86db89238ddb43ba18d5a89bbea&download
- To get full compatibility with proxy layer use the latest JDBC connector for MariaDB. Put connectors to /opt/tomcat/webapps/ROOT/WEB-INF/lib/
- Don’t forget to restart your application server to apply mydb.cfg changes, by pressing Restart Nodes button.
- In case you are going to create and use the custom user accounts after cluster installation, you should add them to the mysql_users table on each ProxySQL node, otherwise you won’t be able to establish connection to the database via the proxy layer. To do this issue commands below:
$ MYSQL_PWD=admin mysql -h 127.0.0.1 -P6032 -uadmin -e "INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('<username>', '<password>', 2);"
$ MYSQL_PWD=admin mysql -h 127.0.0.1 -P6032 -uadmin -e "LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;"
Substitute <username> and <password> with credentials of a newly created database account.
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 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 database phpMyAdmin panels in the cluster to check up the data availability using the same credentials.
Great! In just a few simple steps, you’ve established access to your DB cluster from web-application and performed a simple management operation via a single entrypoint.
Now you have highly available and reliable MariaDB Galera Cluster automatically installed in a matter of minutes, and provided with out-of-box intuitive management tools. Benefit from embedded database auto-clustering with Jelastic PaaS.
