How to Setup Mariadb Clustering on Ubuntu 20.04

What Is MariaDB Galera Cluster?

Galera Cluster is a contemporary multi-master database cluster that is designed keeping in mind the contemporary replication, MySQL (or MariaDB), and InnoDB. It is used to direct read and write databases to any node. An individual node can be lost if the operation is uninterrupted and when the complex failover procedures are not being used.
How Does MariaDB Galera Cluster Work?

 

Galera Cluster is only available on Linux and supports the InnoDB storage engine. It consists of the database server that uses the Galera Replication Plugins to direct replication efficiently. MariaDB replication plugin API is extended to deliver all the information and secure necessary for true multi-master, synchronous replication. This extended API is known as Write-Set Replication API (or wsrep).

 

This API enables MariaDB Galera Cluster to deliver certification-based replication. It consists of database rows to replicate and the information related to the entire locks held by the database during the transaction. Then, in the applier queue, each node certifies the replicated write-set against another write-set. This write-set is applied to the conflicting locks that are not required. Now, the transaction is considered committed. Later, each of these nodes continues to apply it to the tablespace.

setup mariadb clustering on ubuntu server 20.04

Features Of MariaDB Galera Cluster

  • It is virtually synchronous replication.
  • It can read and write to any cluster node.
  • It consists of active multi-primary topology.
  • It controls the membership automatically and drops the failed nodes from the cluster.
  • It joins the node automatically.
  • It connects to the client directly by providing the look and feel of native MariaDB.
  • It delivers true parallel replication on row level.
  • It provides better performance for all the workload.
  • Users need not require VIP or master-slave operation if they use MariaDB.
  • No downtime is available in terms of failure or intentionally taking down of nodes due to less availability of failover.
  • Users can avoid database backup manually.

MariaDB Clustering Setup

mariadb galera setup

In this guide, we will show you how to set up MariaDB Clustering on Ubuntu 20.04.

Installing MariaDB on All Nodes

Prior to MariaDB 10.1, the Galera cluster feature is bundled into MariaDB. So you will need to install the MariaDB server package on all three nodes. You can install it using the following command:

				
					apt-get install mariadb-server -y
				
			

Once the MariaDB has been installed, start the MariaDB service on all nodes:

				
					systemctl start mariadb
				
			

You can now check the status of the MariaDB with the following command:

				
					systemctl status mariadb
				
			

You should see the following output:

				
					● mariadb.service - MariaDB 10.3.31 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Sat 2021-10-02 03:37:11 UTC; 20s ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
   Main PID: 1803 (mysqld)
     Status: "Taking your SQL requests now..."
      Tasks: 31 (limit: 2353)
     Memory: 66.0M
     CGroup: /system.slice/mariadb.service
             └─1803 /usr/sbin/mysqld

				
			

Next, you will need to secure the MariaDB installation and set a MariaDB root password on all nodes. You can do it by running the following script:

				
					mysql_secure_installation
				
			

Answer all the questions as shown below:

				
					Enter current password for root (enter for none): 
Switch to unix_socket authentication [Y/n] n
Change the root password? [Y/n] Y
New password:
Re-enter new password:
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

				
			

Configuring Galera Cluster

Next, you will need to create a MariaDB Galera configuration file on each node. You can create a file in the /etc/mysql/conf.d directory.

Configuring the First Node

Login to the first node and create a MariaDB Galera configuration file:

				
					nano /etc/mysql/conf.d/galera.cnf
				
			

Add the following lines:

				
					[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://45.58.47.29,45.58.35.200,45.58.44.157"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="45.58.47.29"
wsrep_node_name="mariadb01"

				
			

Save and close the file when you are finished.


Note: replaced each IP address as per your server’s IP.

Configuring the Second Node

Next, log in to the second node and create a MariaDB Galera configuration file:

				
					nano /etc/mysql/conf.d/galera.cnf
				
			

Add the following lines:

				
					[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://45.58.47.29,45.58.35.200,45.58.44.157"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="45.58.35.200"
wsrep_node_name="mariadb02"

				
			

Save and close the file when you are finished.

Configuring the Third Node

Next, log in to the third node and create a MariaDB Galera configuration file:

				
					nano /etc/mysql/conf.d/galera.cnf
				
			

Add the following lines:

				
					[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://45.58.47.29,45.58.35.200,45.58.44.157"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="45.58.44.157"
wsrep_node_name="mariadb03"

				
			

Save and close the file when you are finished.

Starting the Galera Cluster

At this point, all nodes are configured. You are now ready to bring up the cluster.

Stop MariaDB on All Nodes

First, run the following command on all nodes to stop the MariaDB service:

				
					systemctl stop mariadb
				
			

Next, make sure MariaDB service is stopped:

				
					systemctl status mariadb
				
			

Sample output:

				
					● mariadb.service - MariaDB 10.3.31 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Sat 2021-10-02 03:41:05 UTC; 22s ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 1803 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=0/SUCCESS)
   Main PID: 1803 (code=exited, status=0/SUCCESS)
     Status: "MariaDB server is down"
				
			

Start the First Node

Now, go to the first node and use the galera_new_cluster script to start the first node.

				
					galera_new_cluster
				
			

You should not get any output if the script is executed successfully.

 

Next, verify the cluster status using the following command:

				
					mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
				
			

You should get the following output:

				
					+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+
				
			

Start the Second Node

Now, go to the second node and bring up the MariaDB service with the following command:

				
					systemctl start mariadb
				
			

Next, verify the cluster status using the following command:

				
					mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
				
			

The following output indicates that the second node has joined the cluster :

				
					+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

				
			

Start the Third Node

Finally, bring up the third node by running the following command:

				
					systemctl start mariadb
				
			

Next, run the following command to check the cluster status:

				
					mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
				
			

You should get the following output:

				
					+--------------------+-------+
| Variable_name      | Value 
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
				
			

Verify Replication

At this point, your MariaDB cluster is up and running. Now, you will need to test whether the replication is working or not.

Create a Database and Table on the First Node

First, go to the first node and connect to the MariaDB using the following command:

				
					mysql -u root -p
				
			

Once you are connected, create a database named classdb:

				
					MariaDB [(none)]> CREATE DATABASE classdb;
				
			

Next, switch the database to classdb and create a table named students:

				
					MariaDB [(none)]> USE classdb;
MariaDB [classdb]> CREATE TABLE students (id int, name varchar(20), surname varchar(20));
				
			

Next, insert some data into students table:

				
					MariaDB [classdb]> INSERT INTO students VALUES (1,"vyom","patel");
MariaDB [classdb]> INSERT INTO students VALUES (2,"raj","shah");
				
			

Now, verify the inserted data with the following command:

				
					MariaDB [schooldb]> SELECT * FROM students;
				
			

You should get the following output:

				
					+------+------+---------+
| id   | name | surname |
+------+------+---------+
|    1 | vyom | patel   |
|    2 | raj  | shah    |
+------+------+---------+
				
			

Verify Replication on the Second and Third Node

Now, it’s time to verify whether the classdb database is replicated or not.

First, go to the second node and connect to the MariaDB with the following command:

				
					mysql -u root -p
				
			

Once you are connected, verify all the databases:

				
					MariaDB [(none)]> SHOW DATABASES;
				
			

You should see that your classdb database is replicated to the second node:

				
					+--------------------+
| Database           |
+--------------------+
| classdb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

				
			

Now, switch the database to classdb and verify your students table:

				
					MariaDB [(none)]> USE classdb;
MariaDB [classdb]> SELECT * FROM students;
				
			

You should see the following output:

				
					+------+------+---------+
| id   | name | surname |
+------+------+---------+
|    1 | vyom | patel   |
|    2 | raj  | shah    |
+------+------+---------+

				
			

Now, add some information in the third row:

				
					MariaDB [classdb]> INSERT INTO students VALUES (3,"nisha","sharma");
				
			

Next, go to the third node and connect to the MariaDB with the following command:

				
					mysql -u root -p
				
			

Next, switch the database to classdb database and verify your tables:

				
					MariaDB [(none)]> USE classdb;
MariaDB [classdb]> SHOW TABLES;
				
			

You should see the following output:

				
					+-------------------+
| Tables_in_classdb |
+-------------------+
| students          |
+-------------------+

				
			

Now, verify your table records using the following command:

				
					MariaDB [classdb]> SELECT * FROM students;
				
			

You should get the following output:

				
					+------+-------+---------+
| id   | name  | surname |
+------+-------+---------+
|    1 | vyom  | patel   |
|    2 | raj   | shah    |
|    3 | nisha | sharma  |
+------+-------+---------+
				
			

Conclusion

In the above guide, we explained how to set up three nodes MariaDB cluster on Ubuntu 20.04. Now, data modifications on any node are replicated to all other nodes. You can now add more MariaDB nodes to increase the size of the cluster.

Avatar for Hitesh Jethva
Hitesh Jethva

I am a fan of open source technology and have more than 10 years of experience working with Linux and Open Source technologies. I am one of the Linux technical writers for Cloud Infrastructure Services.

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x