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.
Step 4. If you want more databases, you can add them by clicking “+” in the “Horizontal Scaling” section.
After a few minutes, your chosen setup will be ready with all the 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.
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.
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
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/.
Restart the application server to apply the mydb.cfg changes by clicking “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.
Step 5. To make sure the connection is working and a new table was created, go back to the 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.
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.