How to set up MariaDB Galera Cluster- Multi-primary Synchronous?

How to Set up MariaDB Galera Cluster- Multi-primary Synchronous?

Setting up a MariaDB Galera Cluster in a multi-primary synchronous configuration is a powerful way to achieve high availability and data redundancy. We will guide you through creating a robust and scalable database cluster that ensures your data remains highly available and consistent.

Galera works by copying actions in a certain way. It copies the information in the database and details about locks used when the action was done. Each node checks the copied actions against others, and the actions are applied if there are no conflicts. Then, the action is considered done, and each node keeps it in the database.

This method is also known as almost simultaneous copying because it seems things are happening together, but they happen separately on each node.

In AccuWeb.Cloud, the Galera Cluster can automatically turn on when making the environment. It usually has 2 ProxySQL load balancers and 3 MariaDB instances.

For Galera Cluster to work, your databases need to meet some rules. The important ones to remember when making tables are:

  • Use InnoDB Storage: Keep your data in InnoDB storager to ensure things work smoothly.
  • Primary Keys: Every table you want to copy must have a special main key. It can be a single column or a few columns together.

You can find the complete list of these rules in the official documents.

Setting Up MariaDB Galera Cluster

Step 1. Go to the AccuWeb.Cloud dashboard.

Step 2. Click “New Environment” & pick the MariaDB server from the options.

Let’s learn how to connect your

Step 3. Turn on “Auto-Clustering” and select the Galera option.

Auto-Clustering

Step 4. If you want more databases, you can add them by clicking “+” in the “Horizontal Scaling” section.

Note: If you want, leave out the ProxySQL layer from the cluster setup before installing by using the right switch. Remember, if you do this now, you can’t add the proxy layer later. The same goes the other way – if you skip it now, you can’t add it after.

Horizontal Scaling

After a few minutes, your chosen setup will be ready with all the connections.

Connections

You can keep an eye on the health of the cluster nodes using the Orchestrator admin panel. You can access it with the login details sent to you by email when you set up the ProxySQL Load Balancer. The cluster members will be shown as separate clusters on the panel, each with one instance inside.

Connecting Your Application to MariaDB Galera Cluster

Let’s learn how to connect your Java web application to the MariaDB Galera Cluster using the ProxySQL load balancer as the starting point. If you want to connect different kinds of applications, you can follow the linked guide.

When you create each main node in the MariaDB cluster, you’ll get an email with phpMyAdmin access details. This can be handy for debugging or doing manual tasks on the databases.

Step 1. First, log in to phpMyAdmin using the URL, username, and password given in the email. Pick the “test” database on the left side. You’ll notice no tables on the right side in the “test” database.

phpMyAdmin

Step 2. Go back to the AccWeb.Cloud dashboard. We’re using a separate environment with a Tomcat 9 application server for this example. We need to make a database configuration file for our test application. Click on the “Config” icon next to your compute node, then go to the /opt/tomcat/temp directory and make a file named “mydb.cfg” using the built-in file manager.

"mydb.cfg"

Step 3. Add the following lines to the “mydb.cfg” file and fill in the details with the entry point credentials like in the picture:


host=jdbc:mariadb://{connect_URL}/{db_name}?usePipelineAuth=false
username={user}
password={password}
driver=org.mariadb.jdbc.Driver
  • {connect_URL} is the link to your DB cluster load balancer (like the ProxySQL node).
  • {db_name} is the name of the database. We chose “test” before.
  • usePipelineAuth is for faster connection creation. It should be “false” for ProxySQL.
  • {user} and {password} are the database credentials from the email.

Step 1. Download the test application using one of the links below and put it on the Tomcat server Tomcat server

Remember

Step 2. For the best compatibility with the proxy layer, use the latest JDBC connector for MariaDB. Put the connectors in /opt/tomcat/webapps/ROOT/WEB-INF/lib/.

JDBC connector for MariaDB

Restart the application server to apply the mydb.cfg changes by clicking “Restart Nodes.

Restart Nodes

If you plan to make custom user accounts after cluster installation, add them to the MySQL_users table on each ProxySQL node. Otherwise, you won’t be able to connect via the proxy layer. Here’s how:


$ 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;"

Replace <username> and <password> with the new database account’s credentials.

Step 4. After the deployment, click “Open in Browser” next to your application server. Click “Create test table in your database” in the app window.

Create test table in your database

Step 5. To make sure the connection is working and a new table was created, go back to the MySQL admin panel.

MySQL admin panel

You’ll see the table named {date-time of creation}. To check if replication works, go through all the phpMyAdmin panels in the cluster to verify data availability using the same credentials.

Important Note: In AccuWeb.Cloud, all MariaDB nodes have phpMyAdmin panels. Just click “Open in Browser” to access it for each database node.

Open in Browser

Great job! With a few easy steps, you’ve connected your web application to the DB cluster and done a basic task using a single entry point. Now, you have a highly available and dependable MariaDB Galera Cluster set up automatically in minutes, along with user-friendly management tools.